Query Optimization: Reading EXPLAIN
Use EXPLAIN to see how a database runs a query. On 200,000 orders, one composite index cut a join from 14.7 ms to 0.27 ms; covering indexes, functions on columns and statistics also change the plan.
SQL query optimization starts with EXPLAIN, which shows the plan the optimizer chose from cost estimates based on statistics: which tables are scanned, which indexes are searched and in what join order. Interviewers expect composite index column order: put equality columns before the range column, and avoid wrapping an indexed column in a function, which stops the index being used.
The query planner picks how to run your SQL: which indexes, which join order. EXPLAIN shows the choice. On a 200,000-row orders table, a filter that scanned everything in 14.7 ms took 0.27 ms once a matching composite index existed. Learning to read the plan tells you why a query is slow.
- The query optimizer generates candidate plans, estimates each one's cost from statistics about the data, and runs the cheapest plan.
- On a 200,000-row orders table in SQLite, adding a composite index on (status, amount) cut a join query from 14.7 ms to 0.27 ms, about 54 times faster.
- A composite index is sorted by its first column and then the next, so equality columns should come before the range column, as in (status, amount).
- A covering index holds every column a query needs, so select sum(amount) used a covering index on (customer_id, amount) while select * still had to read the table.
- With an index on created, the filter created = 500 searched the index while the equivalent created + 0 = 500 scanned the whole table.
- An index helps most for selective filters: status = 'refunded' matched 5% of orders, while status = 'paid' matched 95%, where a full scan can be cheaper.
01Declarative SQL and the planner
SQL describes the result, not the steps. For one query the database could scan a table or use an index, join table A to B or B to A, and use a hash join or a nested loop. The query optimizer enumerates plans, estimates a cost for each from statistics about the data, and runs the cheapest.
So performance work usually means checking what plan it chose and giving it better options (indexes) or better information (statistics), or rewriting a query so the cheap plan becomes possible. The tool for looking is EXPLAIN. The examples below use SQLite's EXPLAIN QUERY PLAN. PostgreSQL's EXPLAIN ANALYZE and MySQL's EXPLAIN show similar information in different formats.
02The test data
create table customers(id integer primary key, country text, name text);
create table orders(id integer primary key, customer_id int,
amount real, status text, created int);I filled orders with random amounts from 0 to 500 and a status that was paid 95% of the time and refunded 5%. The query joins orders to customers and looks for large refunds:
select c.name, o.amount
from orders o join customers c on c.id = o.customer_id
where o.status = 'refunded' and o.amount > 490;The result has 172 rows out of 200,000 orders.
03Reading a plan: scan versus search
Before any index, the plan was:
SCAN o
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)This means: read every order (SCAN), and for each one look up its customer by primary key (SEARCH). The best of three runs took 14.7 ms. Then I added an index on the two columns the WHERE clause uses:
create index idx_o_status_amt on orders(status, amount);The plan became SEARCH o USING INDEX idx_o_status_amt (status=? AND amount>?), and the time dropped to 0.27 ms, about 54 times faster. The index sorts by status and then amount, so the database jumps to refunded orders over 490 and reads only matching entries. Column order matters: equality columns first, then the range column. An index on (amount, status) would serve this query much worse.
04Covering indexes
With an index on orders(customer_id, amount), the query select sum(amount) from orders where customer_id = 777 showed SEARCH ... USING COVERING INDEX. Every value needed (the filter and the amount) lives in the index, so the table itself is never read. The query select * from orders where customer_id = 777 showed only USING INDEX, because it needs the other columns and must fetch each row from the table afterward.
A covering index can make a hot query much cheaper, at the price of a bigger index. Selecting only the columns you need, rather than *, is what makes covering possible.
05Rewrites that unblock the index
With an index on created, the query where created = 500 gave SEARCH orders USING INDEX idx_created. The logically equivalent where created + 0 = 500 gave SCAN orders. Applying a function or arithmetic to the indexed column means the database cannot use the sorted order. The same applies to lower(email) = ..., date(ts) = ... and col LIKE '%x'.
Move the computation to the other side: created = 500, a range such as ts >= '2026-01-01' and ts < '2026-01-02', or build an expression index on lower(email) if you need it. Implicit type conversions can also disable an index, for example comparing a text column to a number.
06Statistics and selectivity
An index helps most when the filter is selective, meaning it matches a small fraction of rows. status = 'refunded' matches 5% of orders here. status = 'paid' matches 95%, and reading nearly the whole table through an index is slower than scanning it, so a good planner may ignore the index for that value.
The planner knows this from statistics. Running ANALYZE in SQLite stores them in sqlite_stat1. For idx_o_status_amt it recorded 200000 100000 N: 200,000 rows, about 100,000 per status value (two values), and N rows per (status, amount) combination, where N depends on how many distinct amounts exist. Note these are averages: sqlite_stat1 cannot see that paid is 95% of the rows, so SQLite may still pick the index for status = 'paid' unless sqlite_stat4 histograms are collected. PostgreSQL and MySQL gather similar numbers (ANALYZE, autovacuum). Stale statistics after a big data load are a common cause of a sudden bad plan.
07A routine for slow queries
- Run EXPLAIN on the slow query and find the SCANs on large tables.
- Check that the filter and join columns have indexes, and that column order fits the query.
- Look for expressions or functions on indexed columns and for leading wildcards.
- Select fewer columns, and consider a covering index for hot queries.
- Update statistics if the data changed a lot.
- Measure again. Keep the index only if it helps, since each one slows writes and takes space.
On PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) runs the query and shows real times and row counts next to the estimates. A big gap between estimated and actual rows points to bad statistics.
08Common interview questions
Q: What does the query optimizer do?
A: It generates candidate plans, estimates their cost from statistics, and runs the cheapest.
Q: How do you see how a query runs?
A: With EXPLAIN (and EXPLAIN ANALYZE where available to include real timings).
Q: Why might the database ignore an index?
A: The filter matches too many rows, the column is wrapped in a function, there is a leading wildcard, or statistics are out of date.
Q: What is a covering index?
A: An index containing all columns the query needs, so the table is not read.
Q: Does column order in a composite index matter?
A: Yes. It is sorted by the first column, then the second, so put equality columns before the range column.
Q: What is the cost of an index?
A: Extra storage and slower inserts, updates and deletes.