analyze_query_performance
Analyze SQL query performance to identify bottlenecks and receive optimization suggestions for improved database efficiency.
Instructions
Analyze query performance and provide optimization suggestions
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| database | No | Database name (optional) | |
| query | Yes | SQL query to analyze for performance optimization |
Implementation Reference
- index.js:658-673 (handler)Main handler function for the 'analyze_query_performance' tool. Delegates analysis to QueryOptimizer and formats the JSON response.async analyzeQueryPerformance(query, database) { const analysis = await this.queryOptimizer.analyzeQuery(query, database); return [ { type: 'text', text: JSON.stringify( { success: true, data: analysis }, null, 2 ) } ]; }
- index.js:333-336 (registration)Dispatch case in the tool request handler that routes 'analyze_query_performance' calls to the handler method.case 'analyze_query_performance': return { content: await this.analyzeQueryPerformance(args.query, args.database) };
- lib/tools/tool-registry.js:162-172 (schema)Tool schema definition with input schema for validation, used by getAllTools() for MCP tool listing.name: 'analyze_query_performance', description: 'Analyze query performance and provide optimization suggestions', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL query to analyze for performance optimization' }, database: { type: 'string', description: 'Database name (optional)' } }, required: ['query'] } }
- Core helper method in QueryOptimizer class that performs static query analysis, identifies bottlenecks, generates warnings and optimization suggestions.analyzeQuery(query, executionStats = {}, planData = {}) { // Input validation to prevent null pointer exceptions if (!query || typeof query !== 'string') { throw new Error('Query must be a non-empty string'); } try { const analysis = { query: query.trim(), query_type: this.determineQueryType(query), complexity_score: this.calculateComplexityScore(query), estimated_cost: planData.TotalSubtreeCost || 0, table_access_methods: this.extractTableAccessMethods(planData), join_algorithms: this.extractJoinAlgorithms(planData), operators: this.extractOperators(planData), bottlenecks: this.identifyBottlenecks(query, executionStats, planData), performance_warnings: this.generatePerformanceWarnings(query, executionStats), optimization_suggestions: this.generateOptimizationSuggestions( query, executionStats, planData ), is_modification_query: this.isModificationQuery(query) }; return analysis; } catch (error) { // Log the error and return a safe fallback analysis console.error('Error during query analysis:', error.message); return { query: query.trim(), query_type: 'UNKNOWN', complexity_score: 0, estimated_cost: 0, table_access_methods: [], join_algorithms: [], operators: [], bottlenecks: [], performance_warnings: ['Query analysis failed due to parsing error'], optimization_suggestions: [], is_modification_query: false, error: 'Query analysis failed' }; } }
- index.js:241-243 (registration)Registers the tool list handler which includes 'analyze_query_performance' via getAllTools() from tool-registry.this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: getAllTools() }));