SQL Query Optimizer MCP Server

A powerful Model Context Protocol (MCP) server that analyzes, optimizes, and suggests indexes for SQL queries across multiple dialects (PostgreSQL, MySQL, Oracle, SQL Server). Built with Python and sqlglot.

Features

Advanced Query Analysis

• Complexity Scoring: Calculates a heuristic complexity score (1-10) based on joins, subqueries, and set operations.

• Detailed Breakdown: Provides a granular breakdown of what contributes to the complexity.

• Anti-Pattern Detection: Identifies performance killers like:

  • SELECT * usage

  • Implicit type casts (e.g., id = '123')

  • Potential N+1 queries (LIMIT without ORDER BY)

  • NULL pitfalls in NOT IN subqueries

  • Join explosions (> 3 joins)

Query Optimization

• Automated Rewriting: Uses sqlglot to apply optimization rules like predicate pushdown and simplification.

• Alternative Suggestions: Generates alternative query forms (e.g., formatted only, CTE refactoring) alongside the main optimization.

• Cost Estimation: Estimates the structural complexity reduction (e.g., "~30%").

• DDL Generation: Generates CREATE INDEX statements for suggested indexes.

Explain Plan Visualization

• ASCII Tree View: Visualizes EXPLAIN output as an easy-to-read ASCII tree.

• Plan Parsing: Extracts scans, costs, and rows from Postgres and MySQL plans.

Index Suggestions

• Composite Indexes: Suggests multi-column indexes for AND conditions.

• Covering Indexes: Recommends extending indexes to include selected columns (Index-Only Scans).

• Smart Prioritization: Ranks suggestions by impact (Critical, High, Medium, Low).

Installation

1. Clone the repository:

   git clone https://github.com/yourusername/mcp-sql-optimizer.git cd mcp-sql-optimizer

2. Create a virtual environment:

   python -m venv venv source venv/bin/activate # On Windows: venv\Scripts\activate

3. Install dependencies:

   pip install -r requirements.txt

Configuration

Add the server to your MCP client configuration (e.g., claude_desktop_config.json):

Note: On Windows, use double backslashes

🐳 Docker (Recommended)

Run the server in a container to avoid environment issues.

1. Build the image:

   docker build -t mcp-sql-optimizer .

2. Configure Claude Desktop:

   { "mcpServers": { "sql-optimizer": { "command": "docker", "args": [ "run", "-i", "--rm", "mcp-sql-optimizer" ] } } }

Usage

The server exposes the following MCP tools:

analyze_query

Analyzes a SQL query for performance issues, complexity, and anti-patterns. Optionally accepts an explain_plan string to visualize the execution plan.

Input:

optimize_query

Rewrites the query to be more performant and provides alternative suggestions.

Input:

suggest_indexes

Suggests indexes to improve query performance, including DDL statements.

Input:

Project Structure

Development

Run the demo client to test features without an MCP client:

Run unit tests:

License

MIT