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 INsubqueries - Join explosions (> 3 joins)
Query Optimization
- Automated Rewriting: Uses
sqlglotto 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 INDEXstatements for suggested indexes.
Explain Plan Visualization
- ASCII Tree View: Visualizes
EXPLAINoutput 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
ANDconditions. - Covering Indexes: Recommends extending indexes to include selected columns (Index-Only Scans).
- Smart Prioritization: Ranks suggestions by impact (Critical, High, Medium, Low).
Installation
-
Clone the repository:
git clone https://github.com/yourusername/mcp-sql-optimizer.git cd mcp-sql-optimizer -
Create a virtual environment:
python -m venv venv source venv/bin/activate # On Windows: venv\Scripts\activate -
Install dependencies:
pip install -r requirements.txt
Configuration
Add the server to your MCP client configuration (e.g., claude_desktop_config.json):
{
"mcpServers": {
"sql-optimizer": {
"command": "C:\\path\\to\\venv\\Scripts\\python.exe",
"args": [
"C:\\path\\to\\mcp-sql-optimizer\\server.py"
],
"env": {
"PYTHONPATH": "C:\\path\\to\\mcp-sql-optimizer"
}
}
}
}
Note: On Windows, use double backslashes \\ in paths. The PYTHONPATH is crucial for the server to find its internal modules.
🐳 Docker (Recommended)
Run the server in a container to avoid environment issues.
-
Build the image:
docker build -t mcp-sql-optimizer . -
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:
{
"sql": "SELECT * FROM orders WHERE user_id = '123'",
"dialect": "postgres"
}
optimize_query
Rewrites the query to be more performant and provides alternative suggestions.
Input:
{
"sql": "SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)",
"dialect": "postgres"
}
suggest_indexes
Suggests indexes to improve query performance, including DDL statements.
Input:
{
"sql": "SELECT * FROM users WHERE region_id = 5 AND status = 'active'",
"dialect": "postgres"
}
Project Structure
mcp-sql-optimizer/
├── server.py # Main MCP server entry point
├── core/
│ ├── analyzer.py # Performance & complexity analysis
│ ├── rewriter.py # Query optimization & alternatives
│ ├── indexer.py # Index suggestion logic
│ ├── explain_parser.py # Explain plan parsing & visualization
│ ├── parser.py # SQL parsing wrapper
│ └── dialect_detector.py# Dialect inference
├── utils/ # Helper utilities
└── tests/ # Unit tests
Development
Run the demo client to test features without an MCP client:
python demo_client.py
Run unit tests:
python -m unittest discover tests
License
MIT