SQL Query Optimizer

Overview

This skill helps developers analyze and optimize SQL queries for relational databases, including OceanBase and MySQL-compatible systems.

When to Use

Use this skill when a user:

Reports a slow SQL query.
Provides an SQL execution plan for analysis.
Wants recommendations for indexing.
Needs help rewriting a query for better performance.

Workflow

Step 1: Collect Context

Request the SQL statement, database version, relevant table schemas, existing indexes, and execution plan when available.

Do not assume missing schema or statistics.

Step 2: Analyze the Query

Identify potential performance bottlenecks:

Full table scans on large tables.
Expensive joins or subqueries.
Missing or ineffective indexes.
Unnecessary sorting or aggregation.
Large intermediate results.
Inaccurate cardinality estimates.

Distinguish observations supported by the execution plan from hypotheses requiring verification.

Step 3: Recommend Optimizations

Suggest improvements in priority order:

SQL rewriting without changing query semantics.
Appropriate indexes based on filters and joins.
Reducing unnecessary intermediate data.
Checking database statistics and execution plans.

Explain trade-offs, including index storage and write overhead.

Step 4: Validate

Recommend comparing before-and-after execution plans, latency, and resource usage.

Do not claim a performance improvement without benchmark evidence.

Output Format

Provide:

Bottleneck analysis.
Recommended SQL rewrite, if applicable.
Index recommendations.
Expected benefits and trade-offs.
Steps to validate performance.

Safety

Never execute destructive SQL statements or change production schemas without explicit user approval. Preserve the original query semantics. Do not invent benchmark measurements.

Example

Input: SELECT * FROM orders WHERE customer_id = 100;

Possible recommendation: Check whether an index on orders(customer_id) exists and whether the execution plan uses it. Evaluate its benefit against the table size and query selectivity.