MySQL Optimizer MCP Server

A Model Context Protocol (MCP) server that provides MySQL database analysis and query optimization tools with read-only access.

Features

• Table Analysis: Get detailed information about tables including columns, indexes, primary keys, and foreign keys
• Query Optimization: Analyze SELECT queries and get optimization suggestions
• Index Suggestions: Get recommendations for creating indexes based on query patterns
• Data Retrieval: Safely retrieve sample or latest data from tables
• Security: Read-only access with strict validation against dangerous operations

Installation

From npm (Recommended)

npm install -g mysql-optimizer-mcp-server

From source

git clone https://github.com/yourusername/mysql-optimizer-mcp-server.git
cd mysql-optimizer-mcp-server
npm install

Environment Variables

Set these environment variables for database connection:

DB_HOST=localhost          # Database host (default: localhost)
DB_PORT=3306              # Database port (default: 3306)
DB_USER=your_username     # Database username
DB_PASSWORD=your_password # Database password
DB_NAME=your_database     # Database name

Usage

Running the Server

npm start

Available Tools

1. show_table_details

Get comprehensive information about a table.

Parameters:

• table_name (string): Name of the table to analyze

Returns:

• Table structure with columns, data types, constraints
• All indexes with their columns and properties
• Primary keys and foreign key relationships

2. analyze_query

Intelligently analyze SQL queries with three-tier safety handling.

Parameters:

• query (string): SQL query to analyze
• limit (number): Maximum rows to return for executed queries (default: 100, max: 1000)

Three-Tier Safety Model:

• Safe queries (SELECT, SHOW, DESCRIBE, EXPLAIN): ✅ Executed with results and optimization suggestions
• Analyzable queries (INSERT, UPDATE): ⚠️ Analyzed for structure and optimization but NOT executed
• Blocked queries (DROP, DELETE, TRUNCATE, ALTER, etc.): ❌ Completely rejected

Returns:

• For safe queries: Execution results, optimization suggestions, execution plan
• For analyzable queries: Structure analysis, optimization suggestions, safe alternatives
• For blocked queries: Rejection message with security explanation

3. suggest_indexes

Get index suggestions for a table based on query patterns.

Parameters:

• table_name (string): Name of the table
• where_columns (array): Columns frequently used in WHERE conditions

Returns:

• Existing indexes on the table
• Suggested single-column indexes
• Suggested composite indexes
• SQL statements to create the indexes

4. get_table_data

Retrieve sample or latest data from a table.

Parameters:

• table_name (string): Name of the table
• limit (number): Number of rows to return (default: 10, max: 100)
• order_by (string): Column to order by
• order_direction (string): ASC or DESC (default: DESC)

Returns:

• Sample data from the table
• Total row count
• Query parameters used

5. list_tables

List all tables in the database with metadata.

Returns:

• All tables with row counts, sizes, and timestamps

Security Features

• Three-Tier Security Model: Smart categorization of queries based on risk level
• Safe Execution: SELECT, SHOW, DESCRIBE, EXPLAIN queries are executed with optimization
• Analysis-Only Mode: INSERT, UPDATE queries are analyzed for structure but never executed
• Complete Blocking: DROP, DELETE, TRUNCATE, ALTER operations are completely rejected
• Comprehensive Validation: Detects dangerous keywords, SQL injection patterns, and nested unsafe operations
• Flexible Limits: Configurable row limits (max 1000 for safe queries, max 100 for table data)
• Read-Only Guarantee: No data modifications can occur - only safe reads and analysis

Example Usage in MCP Client

// Get table details
await callTool('show_table_details', { table_name: 'users' });

// Analyze any query - automatically handles safety
await callTool('analyze_query', { 
  query: 'SELECT * FROM users WHERE email = ? AND status = ?',
  limit: 50
});

// Safe queries are executed with optimization suggestions
await callTool('analyze_query', { 
  query: 'SHOW TABLES' 
});

await callTool('analyze_query', { 
  query: 'DESCRIBE users' 
});

await callTool('analyze_query', { 
  query: 'EXPLAIN SELECT * FROM users WHERE email = "[email protected]"' 
});

// Analyzable queries (INSERT/UPDATE) - analyzed but not executed
await callTool('analyze_query', { 
  query: 'INSERT INTO users (name, email) VALUES ("John", "[email protected]")' 
});
// Returns: Structure analysis, optimization suggestions, safe alternatives

await callTool('analyze_query', { 
  query: 'UPDATE products SET price = price * 1.1 WHERE category = "electronics"' 
});
// Returns: Structure analysis, affected columns, optimization suggestions

// Dangerous queries are completely rejected
await callTool('analyze_query', { 
  query: 'DELETE FROM users WHERE last_login < "2023-01-01"' 
});
// Returns: {"status": "REJECTED", "reason": "Dangerous operations are completely blocked"}

// Get index suggestions
await callTool('suggest_indexes', { 
  table_name: 'users', 
  where_columns: ['email', 'status'] 
});

// Get latest reports
await callTool('get_table_data', { 
  table_name: 'reports', 
  limit: 20, 
  order_by: 'created_at',
  order_direction: 'DESC'
});

Security Examples

The server uses a three-tier security model with different handling for each category:

Safe Query Execution (SELECT, SHOW, DESCRIBE, EXPLAIN)

// Input: SELECT COUNT(*) FROM users WHERE status = 'inactive'
// Output:
{
  "query": "SELECT COUNT(*) FROM users WHERE status = 'inactive'",
  "safety_status": "SAFE - EXECUTED",
  "query_type": "SELECT",
  "result": [{"COUNT(*)": 42}],
  "row_count": 1,
  "optimization_suggestions": [...]
}

Analyzable Query Analysis (INSERT, UPDATE)

// Input: UPDATE products SET price = price * 1.1 WHERE category = 'electronics'
// Output:
{
  "query": "UPDATE products SET price = price * 1.1 WHERE category = 'electronics'",
  "safety_status": "ANALYZABLE - NOT EXECUTED",
  "structure_analysis": {
    "type": "UPDATE",
    "tables": ["products"],
    "columns": ["price"],
    "conditions": ["category = 'electronics'"]
  },
  "analysis_recommendations": [
    {
      "type": "analysis",
      "message": "UPDATE operations modify existing data",
      "alternatives": [
        "To preview affected rows: SELECT * FROM products WHERE category = 'electronics' LIMIT 10",
        "To count affected rows: SELECT COUNT(*) FROM products WHERE category = 'electronics'"
      ]
    }
  ],
  "note": "This query was analyzed for structure and optimization but was NOT executed."
}

Blocked Query Rejection (DROP, DELETE, TRUNCATE, etc.)

// Input: DELETE FROM users WHERE status = 'inactive'
// Output: 
{
  "query": "DELETE FROM users WHERE status = 'inactive'",
  "status": "REJECTED",
  "reason": "Dangerous operations are completely blocked",
  "violations": ["DELETE: DELETE operations remove data from tables"],
  "message": "Operations like DROP, DELETE, TRUNCATE, ALTER, CREATE, etc. are completely blocked for security.",
  "allowed_operations": ["SELECT", "SHOW", "DESCRIBE", "DESC", "EXPLAIN", "HELP"],
  "analyzable_operations": ["INSERT", "UPDATE"]
}

MCP Configuration

Using npm package (Recommended)

Add to your MCP client configuration:

{
  "mcpServers": {
    "mysql-optimizer": {
      "command": "npx",
      "args": ["mysql-optimizer-mcp-server"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

Using uvx (Alternative)

If you have uv installed, you can also use:

{
  "mcpServers": {
    "mysql-optimizer": {
      "command": "uvx",
      "args": ["mysql-optimizer-mcp-server"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

Using local installation

For local development or custom installations:

{
  "mcpServers": {
    "mysql-optimizer": {
      "command": "node",
      "args": ["/path/to/mysql-optimizer-mcp-server/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_NAME": "your_database"
      }
    }
  }
}

Query Optimization Tips

The server provides suggestions for:

1. Missing Indexes: When full table scans are detected
2. Single Column Indexes: For frequently filtered columns
3. Composite Indexes: For multi-column WHERE conditions
4. Query Structure: Based on execution plan analysis

All suggestions are provided as SQL statements that you can review and execute manually.# mcp-db

mcp-db

mcp-db