Loading…
Loading…
SQL tutorial · Advanced
⏱️ 22 mins read
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.
EXPLAIN SELECT ...
EXPLAIN ANALYZE SELECT ...
-- MySQL: look at 'type', 'key', 'rows'
-- PostgreSQL: look at Seq Scan vs Index Scan-- 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);| 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 |
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 rulesThe 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;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'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.
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.
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?
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 →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.
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.