analyze-query

Analyze Query

Analyze SQL query execution plan and suggest optimizations.

Your Task

Analyze the provided SQL query and provide optimization recommendations.

Arguments

• $1: SQL query (inline) or file path containing query

Steps

1. Parse the query:

   • Identify SELECT, FROM, WHERE, JOIN, ORDER BY, GROUP BY clauses
   • Note any subqueries or CTEs
   • Check for functions on columns

2. Analyze query structure:

   Check for common issues:

   • SELECT * (fetching unnecessary columns)
   • Missing WHERE clause (full table scan)
   • Functions on indexed columns
   • Correlated subqueries
   • Large OFFSET values
   • No LIMIT clause

3. Provide EXPLAIN command:

   EXPLAIN ANALYZE
   [user's query];
   

   Instruct user to run this and share results.

4. Analyze execution plan (if provided):

   Look for:

   • Seq Scan (sequential scan - bad for large tables)
   • Index Scan (good)
   • Nested Loop (can be slow for large datasets)
   • Hash Join (good for large datasets)
   • Sort operations (expensive)
   • High cost values

5. Recommend indexes:

   Based on WHERE, JOIN, ORDER BY clauses:

   -- For WHERE user_id = ?
   CREATE INDEX idx_table_user_id ON table_name(user_id);
   
   -- For WHERE user_id = ? AND created_at > ?
   CREATE INDEX idx_table_user_created ON table_name(user_id, created_at);
   
   -- For JOIN
   CREATE INDEX idx_table_foreign_key ON table_name(foreign_key_column);
   

6. Suggest query rewrites:

   *Replace SELECT :

   -- Instead of
   SELECT * FROM users;
   
   -- Use
   SELECT id, name, email FROM users;
   

   Add LIMIT:

   -- Instead of
   SELECT * FROM posts ORDER BY created_at DESC;
   
   -- Use
   SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
   

   Optimize subqueries:

   -- Instead of
   SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);
   
   -- Use
   SELECT DISTINCT u.* FROM users u
   JOIN posts p ON u.id = p.user_id;