Senior LevelPerformance3 Interview QuestionsFREE

SQL Query Optimization Interview Questions

What Interviewers Are Testing

Query optimization interview questions assess your ability to diagnose slow queries, interpret execution plans, and apply techniques to improve database performance at scale.

Interview Questions & Model Answers

Q1

How would you approach a slow SQL query in production?

A

Step 1: Reproduce the issue — get the exact slow query from slow query log or APM. Step 2: Run EXPLAIN/EXPLAIN ANALYZE to get the execution plan. Step 3: Identify the bottleneck — full table scans, missing indexes, bad join order, high row estimates. Step 4: Add or modify indexes to address the bottleneck. Step 5: Rewrite the query if necessary (EXISTS vs COUNT, WHERE vs HAVING, CTEs vs subqueries). Step 6: Verify the fix with EXPLAIN ANALYZE and measure actual runtime.

Potential Follow-up Questions:

  • How do you read an EXPLAIN plan?
  • What does 'Using filesort' in MySQL EXPLAIN mean?
Q2

What is the N+1 query problem and how do you fix it?

A

The N+1 problem: 1 query fetches N records, then N additional queries fetch related data for each record. Total: N+1 queries. Example: fetch 100 orders (1 query), then for each order fetch the customer (100 queries) = 101 total queries. Fix: use a single JOIN to fetch all needed data in one query, or use batch loading (fetch all customer IDs, then fetch all customers IN those IDs in one query).

Example
-- N+1 Problem (bad)
SELECT * FROM orders; -- 1 query
-- For each order, separately:
SELECT * FROM customers WHERE id = order.customer_id; -- N queries

-- Fix: single JOIN
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id;
Try in Playground

Potential Follow-up Questions:

  • How do ORMs cause N+1 problems?
Q3

What does EXPLAIN output tell you in SQL?

A

EXPLAIN shows the query execution plan — how the database engine plans to retrieve the data. Key fields: type/access_type: ALL (full scan — bad), range, ref, eq_ref, const (best for single lookup). key: which index is being used. rows: estimated row count at each step (higher = more work). Extra: 'Using index' (covering index — great), 'Using filesort' (sorting not by index — can be slow), 'Using temporary' (temp table — can be slow). EXPLAIN ANALYZE actually runs the query and shows real vs estimated row counts.

Potential Follow-up Questions:

  • What is the difference between EXPLAIN and EXPLAIN ANALYZE?

Frequently Asked Questions

How do you optimize a slow SQL query?
1. Get the slow query (slow query log, query store). 2. Run EXPLAIN ANALYZE. 3. Look for full table scans — add an index on the WHERE/JOIN column. 4. Check if existing indexes are being used — avoid functions on indexed columns in WHERE. 5. Consider composite or covering indexes. 6. Rewrite correlated subqueries as JOINs. 7. Use EXISTS instead of COUNT for existence checks. 8. Move WHERE filters before GROUP BY (HAVING should only filter aggregates). 9. Use LIMIT when only top N results are needed.
What causes a SQL query to be slow?
Common causes of slow queries: full table scans (no index on WHERE/JOIN column), wrong index column order in composite index, large result sets without LIMIT, N+1 query pattern, functions on indexed columns preventing index usage (WHERE UPPER(name) = 'JOHN'), implicit type conversions in joins, stale table statistics causing poor query plans, missing indexes on foreign key columns, SELECT * fetching unnecessary columns, and large OFFSET in pagination.

Related Interview Topics

IndexingSql JoinsWindow FunctionsTransactions

Practice Answering Live

Use our Interview Arena to practice SQL challenges under real interview conditions. Free.

Open Interview Arena SQL Playground