Query Optimization — SQL Tutorial with Examples

What You'll Learn

Query optimization ensures SQL runs efficiently at scale. Use EXPLAIN/EXPLAIN ANALYZE to see the execution plan. Look for: full table scans (type=ALL), missing indexes, large estimated row counts, and nested loops on big tables. Common fixes: add indexes, rewrite functions in WHERE, avoid SELECT *, use EXISTS instead of IN.

Syntax

EXPLAIN SELECT ...
EXPLAIN ANALYZE SELECT ...
-- MySQL: look at 'type', 'key', 'rows'
-- PostgreSQL: look at Seq Scan vs Index Scan

Example

-- BAD: function on indexed column breaks index
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- GOOD: range condition uses the index
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- BAD: SELECT * is wasteful
SELECT * FROM large_table WHERE status = 'active';

-- GOOD: only needed columns
SELECT id, name, status FROM large_table
WHERE status = 'active';

-- BAD: NOT IN with possible NULLs
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM a WHERE NOT EXISTS
  (SELECT 1 FROM b WHERE b.id = a.id);

Beyond the Basics

Data in play — reference tables for this topic

-- orders (from Topic 7) — the table being optimized
-- id | customer_id | status    | total  | items | order_date | shipped_at
-- 1  | 1           | shipped   | 240.00 | 3     | 2024-01-15 | 2024-01-18
-- 2  | 2           | shipped   | 89.90  | 1     | 2024-02-03 | 2024-02-07
-- 3  | 1           | pending   | 430.00 | 2     | 2024-03-11 | NULL
-- 4  | 3           | shipped   | 59.50  | 1     | 2024-04-02 | 2024-04-05
-- 5  | 4           | cancelled | 120.00 | 0     | 2024-05-20 | NULL

EXPLAIN first, intuition second

Everything else in this topic is a hypothesis until EXPLAIN confirms it. The plan tells you the join order, whether an index was used, and — critically — the estimated row counts. When a query is slow, the estimate is usually lying: 5 estimated rows that turn out to be 500,000 mean stale statistics or a non-sargable predicate confusing the planner.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
-- With idx_cust_date (Topic 21): type=ref, key=idx_cust_date
-- Without it:                   type=ALL, rows=5 (full scan)
-- On 5 rows the scan is FASTER — context matters more than rules

Read the plan, not the folklore: on tiny tables full scans win; index rules only pay off at scale.

N+1: the anti-pattern that doesn't look like SQL

The application fetches 100 orders, then loops and runs one query per order to fetch its customer. Each query is fast (indexed!), the code looks clean — and the page makes 101 round trips. This is the single most common 'the database is slow' diagnosis that turns out to be application design.

-- The loop, written out (what the app actually does):
SELECT * FROM orders LIMIT 100;               -- 1 query
SELECT name FROM customers WHERE id = 1;      -- ×100 more

-- The fix — one query:
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Count the queries a page makes before blaming the engine — batch loops into JOINs or an IN list, and measure the round trips, not the per-query milliseconds.

Every warning so far, cashed in

Look at the example's BAD column and check the trail: functions on columns kill indexes (Topics 3, 12, 14 — sargability), NOT IN with NULLs returns wrong results (Topics 7 and 16), SELECT * drags unneeded columns (Topic 2), and the covering-index idea explains why idx_cust_date served both filters (Topic 21). Optimization is mostly paying attention to habits you already formed.

-- BAD, then GOOD — four habits in one query:
-- ✗ WHERE YEAR(order_date) = 2024        → range condition
-- ✗ SELECT *                              → name the columns
-- ✗ status = 'completed'                  → ShopCo has no such status;
--                                           filtering on a value that
--                                           returns nothing still scans
-- ✓ WHERE order_date >= '2024-01-01'
--     AND order_date <  '2025-01-01'
--     AND status = 'shipped'

Most 'optimization' is subtractive: remove the function, remove the columns, remove the wrong rows earlier. The plan confirms what discipline already predicted.

Common Mistakes

Common performance killers: functions on WHERE columns, OR conditions (use UNION), SELECT *, NOT IN with NULLs, implicit type conversions (WHERE int_col = '5'), missing JOIN indexes, fetching all rows to filter in application code.

Interview Tips

In interviews, always mention EXPLAIN as your first step. Discuss the difference between Seq Scan and Index Scan. Mention query cost, cardinality estimates, and that covering indexes (all queried columns in the index) are fastest.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. What is the first step when a query is slow?

2. The N+1 problem is…?

3. Which pairing is the classic BAD → GOOD rewrite?

4. EXPLAIN vs EXPLAIN ANALYZE — what's the catch with the latter?

5. On ShopCo's 5-row orders table, is a full scan bad?

6. What makes 'avoid SELECT *' a performance habit, not just style?

Practice

Return each employee's name and their department's name using a single JOIN — the rewrite that fixes the N+1 query problem.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

EXPLAIN vs EXPLAIN ANALYZE — which should I run?

EXPLAIN shows the plan without executing (safe for writes, but estimates only). EXPLAIN ANALYZE actually runs it and reports real timings and row counts — far more truthful, but it executes the statement, so wrap UPDATEs/DELETEs in a transaction you roll back.

Isn't optimizing before measuring premature?

Yes — Knuth's 'premature optimization' warning applies. Write the clear query first, then measure with real data volumes. The exceptions are free habits (sargable predicates, named columns, sane indexes) that cost nothing to write correctly the first time.