SingleStore MCP Server

Instructions

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'
        });
      }
    }
  }