EXPLAIN: the query plan
In short: EXPLAIN shows the query plan, how the database will run a query: whether it reads the whole table or searches through an index (in SQLite, EXPLAIN QUERY PLAN says SCAN or SEARCH).
Problem
“The report is slow”
A colleague says the report on one customer’s visits is slow. You could guess: maybe the table is big, maybe the condition is complicated, maybe an index is missing. Or you could ask the database how exactly it plans to run it.
Every database first makes a query plan: which table to start from, whether to read everything or search an index, whether to sort. Only then does it run it. The plan can be seen.
The idea
EXPLAIN QUERY PLAN
In SQLite you write EXPLAIN QUERY PLAN in front of the query. The query does not run. Instead of a result you get the plan:
The column that matters is detail. id and parent arrange the steps into a tree when there are several, and notused is not used. Here you can ignore them. SCAN visits means: read the whole table row by row.
In this field the index on customer_id has already been created:
SEARCH means a search through an index. The brackets say what it looks for: customer_id=?, that is, one value of customer_id. SCAN or SEARCH is the first thing you look for in a plan.
Try it
“My orders”
The “My orders” page looks up a customer’s orders by customer_id. Look at the plan in the field, then create an index idx_orders_customer so that the plan of the same query becomes SEARCH.
Solution
CREATE INDEX idx_orders_customer ON orders(customer_id);CREATE INDEX idx_orders_customer ON orders(customer_id); The check runs the plan of SELECT * FROM orders WHERE customer_id = 5 after your answer and compares it with the plan after the solution, so the index name has to be the same. Notice that customer_id is a foreign key, and there was no index on it. SQLite and PostgreSQL do not create such an index on their own. MySQL (InnoDB) does.
The idea
Covering index
Compare the plan of SELECT * above with the plan of COUNT(*) with the same condition:
Here it says USING COVERING INDEX. The index stores the value of customer_id and the row number. For COUNT(*) that is enough: the database counts the entries in the index and does not open the table. This is called a covering index.
SELECT * also needs product_id and seen_at. They are not in the index, so for every entry found the database goes to the table as well. The row number (id) is in every index entry, so SELECT id with the same condition is covered:
See it
The hidden sort
The page shows the customer’s visits starting from the newest. Run the plan:
The second line is USE TEMP B-TREE FOR ORDER BY. The index helped to find the rows, but they are not sorted by seen_at. So the database sorts them itself, in a temporary structure. For 60 rows that is nothing. For 60,000 you start to notice it. In the next lesson we will make an index that gives the rows already sorted.
A search by primary key has its own entry in the plan:
The idea
How to find out why it is slow
Run the plan of the slow query and look for:
- SCAN of a big table while the condition picks few rows. An index is missing, or the condition cannot use it (that is the last lesson of the module).
- USE TEMP B-TREE. The database sorts or groups by itself. An index in the right order can save that.
- SEARCH, but still slow. The condition picks many rows, or the index does not cover the query and the table is read for every row.
The plan says what will happen. How long it takes, you see when you run the query itself.
More precisely, if you are curious
In SQLite, EXPLAIN without QUERY PLAN shows the program SQLite turns the query into (instructions for its virtual machine). That is for the people who work on SQLite itself.
In PostgreSQL, EXPLAIN shows the plan with estimates: how many rows each step expects and how much it will “cost”. EXPLAIN ANALYZE really runs the query and adds the real time and the real row count of each step. When the estimate and the reality differ a lot, that is usually where a slow plan comes from. There a full read is called Seq Scan, a search through an index Index Scan, and a covering index Index Only Scan. Be careful: EXPLAIN ANALYZE on an UPDATE or DELETE really changes the data, so run it inside a transaction and then ROLLBACK.
MySQL has EXPLAIN, and since version 8.0.18 also EXPLAIN ANALYZE.
Exercise
Two tasks
1. The October report counts the visits from 1 October 2026 onwards. Create an index idx_visits_seen so that the plan is SEARCH.
An index that turns the plan of the October report into SEARCH:
Solution
CREATE INDEX idx_visits_seen ON visits(seen_at);CREATE INDEX idx_visits_seen ON visits(seen_at); The plan becomes SEARCH visits USING COVERING INDEX idx_visits_seen (seen_at>?). The index is sorted, so for >= the database finds the first date and reads on to the end. And it is covering, because COUNT(*) needs nothing from the table.
2. Customer 4321 asks for their data to be deleted. First the program needs only the numbers of their visits. The index on customer_id has already been created. Write the query so that the database does not read the table.
The numbers (id) of customer 4321’s visits, from the index only:
Solution
SELECT id FROM visits WHERE customer_id = 4321;SELECT id FROM visits WHERE customer_id = 4321; 60 visits. Check it with EXPLAIN QUERY PLAN in front of the query: it says USING COVERING INDEX. With SELECT * the result would be different, and the database would also open each of the 60 rows.
Test