SingleStore MCP Server

Instructions

Input Schema

Input Schema (JSON Schema)

Implementation Reference

• src/index.ts:1810-1852 (handler)

  The primary handler for the 'optimize_sql' tool. Validates input query, executes PROFILE on SingleStore, retrieves JSON profile data, analyzes it via analyzeProfileData helper, and returns structured optimization recommendations including summary, suggestions, and optionally an optimized query.
  case 'optimize_sql': { if (!request.params.arguments || typeof request.params.arguments.query !== 'string') { throw new McpError( ErrorCode.InvalidParams, 'Query parameter must be a string' ); } const query = request.params.arguments.query.trim(); try { // Step 1: Run PROFILE on the query await conn.query('SET profile_for_debug = ON'); await conn.query(`PROFILE ${query}`); // Step 2: Get the profile data in JSON format const [profileResult] = await conn.query('SHOW PROFILE JSON') as [mysql.RowDataPacket[], mysql.FieldPacket[]]; // Step 3: Analyze the profile data and generate recommendations const recommendations = await this.analyzeProfileData(profileResult[0], query); // Step 4: Return the analysis and recommendations return { content: [ { type: 'text', text: JSON.stringify({ original_query: query, profile_summary: recommendations.summary, recommendations: recommendations.suggestions, optimized_query: recommendations.optimizedQuery || query }, null, 2) } ] }; } catch (error: unknown) { const err = error as Error; throw new McpError( ErrorCode.InternalError, `Query optimization error: ${err.message}` ); } }
• src/index.ts:1365-1377 (registration)

  Registration of the 'optimize_sql' tool in the MCP server's listTools response, defining its name, description, and input schema (requires 'query' string).
  name: 'optimize_sql', description: 'Analyze a SQL query using PROFILE and provide optimization recommendations', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL query to analyze and optimize' } }, required: ['query'] } }
• src/index.ts:78-91 (schema)

  TypeScript interface defining the structure of optimization recommendations returned by the tool, including performance summary, suggestion list with impact levels, and optional optimized query.
  interface OptimizationRecommendation { summary: { total_runtime_ms: string; compile_time_ms: string; execution_time_ms: string; bottlenecks: string[]; }; suggestions: Array<{ issue: string; recommendation: string; impact: 'high' | 'medium' | 'low'; }>; optimizedQuery?: string; }
• src/index.ts:357-415 (helper)

  Primary helper function that processes SingleStore PROFILE JSON data, extracts timings, orchestrates specialized analysis (execution plan, memory, network, etc.), identifies bottlenecks, and builds the OptimizationRecommendation object.
  private async analyzeProfileData(profileData: any, originalQuery: string): Promise<OptimizationRecommendation> { const result: OptimizationRecommendation = { summary: { total_runtime_ms: '0', compile_time_ms: '0', execution_time_ms: '0', bottlenecks: [] }, suggestions: [] }; try { // Parse the JSON string if it's not already an object const profile = typeof profileData === 'string' ? JSON.parse(profileData) : profileData; // Extract query_info const queryInfo = profile.query_info || {}; // Set basic summary information result.summary.total_runtime_ms = queryInfo.total_runtime_ms || '0'; // Extract compile time from compile_time_stats if available if (queryInfo.compile_time_stats && queryInfo.compile_time_stats.total) { result.summary.compile_time_ms = queryInfo.compile_time_stats.total; // Calculate execution time (total - compile) const totalTime = parseInt(result.summary.total_runtime_ms, 10); const compileTime = parseInt(result.summary.compile_time_ms, 10); result.summary.execution_time_ms = (totalTime - compileTime).toString(); } // Analyze execution plan and operators this.analyzeExecutionPlan(profile, result); // Analyze table statistics and memory usage this.analyzeMemoryAndStats(profile, result); // Analyze network traffic and data movement this.analyzeNetworkTraffic(profile, result); // Analyze compilation time this.analyzeCompilationTime(profile, result); // Analyze partition skew this.analyzePartitionSkew(profile, result); // Identify bottlenecks this.identifyBottlenecks(profile, result); } catch (error) { result.suggestions.push({ issue: 'Error analyzing profile data', recommendation: 'The profile data could not be properly analyzed. Please check the query syntax.', impact: 'high' }); } return result; }
• src/index.ts:417-441 (helper)

  Helper method for analyzing execution plan from profile text: detects full table scans without indexes and large hash joins, adding high/medium impact suggestions.
  private analyzeExecutionPlan(profile: any, result: OptimizationRecommendation): void { const textProfile = profile.query_info?.text_profile || ''; const lines = textProfile.split('\n'); // Look for full table scans if (textProfile.includes('TableScan') && !textProfile.includes('IndexScan')) { result.suggestions.push({ issue: 'Full table scan detected', recommendation: 'Consider adding an index to the columns used in WHERE clauses to avoid scanning the entire table.', impact: 'high' }); } // Check for hash joins with large tables if (textProfile.includes('HashJoin')) { const rowsMatch = textProfile.match(/actual_rows: (\d+)/); if (rowsMatch && parseInt(rowsMatch[1], 10) > 10000) { result.suggestions.push({ issue: 'Large hash join operation', recommendation: 'For large tables, consider using appropriate indexes on join columns or partitioning data to reduce the size of hash tables.', impact: 'medium' }); } } }