Indexes and Query Plans

Mark has a dashboard that shows orders for a chosen day, and on the production database it’s become slow. “Same query as always. Why is it suddenly slow?” On Cartwheel’s two thousand orders, every query in this course returns instantly. Production has years of orders, and there the difference between a query that reads every row and one that jumps to the right rows is seconds versus minutes. You can’t feel that difference on a small database, but you can see it in the query plan.

What an index is

An index is a separate structure that keeps the values of one or more columns in sorted order, each with a pointer to its row. Finding ‘2025-11-28’ in a sorted list is like finding a word in a dictionary: you jump to roughly the right page instead of reading from the start. Without an index, the database has to read the whole table and test every row, which is a scan.

Cartwheel’s database has a few indexes already. You can list them from the schema table:

SELECT name, tbl_name, sql
FROM sqlite_schema
WHERE type = 'index';

orders is indexed on customer_id and order_date, order_items on order_id and product_id. The sqlite_autoindex_customers_1 entry exists because email is declared UNIQUE; SQLite enforces uniqueness with an index.

Reading a query plan

Prefix any SELECT with EXPLAIN QUERY PLAN and SQLite describes how it will run it instead of running it. The interesting part is the detail column:

EXPLAIN QUERY PLAN
SELECT order_id, status
FROM orders
WHERE order_date >= '2025-11-28'
  AND order_date <  '2025-11-29';

SEARCH orders USING INDEX idx_orders_date (order_date>? AND order_date<?). SEARCH means the index narrowed the work to a range. Now Mark’s dashboard query, which someone “simplified” last month:

EXPLAIN QUERY PLAN
SELECT order_id, status
FROM orders
WHERE date(order_date) = '2025-11-28';

SCAN orders. Same result, but now every row is read and date() is computed for each one. The index stores raw order_date values; it knows nothing about date(order_date), so it can’t help. That’s the “suddenly slow”.

Two more plan phrases are worth recognising. USING COVERING INDEX means the index alone held every column the query needed, so the table wasn’t touched at all, the fastest kind of lookup. And USE TEMP B-TREE FOR GROUP BY or FOR ORDER BY means SQLite had to sort rows itself, which is fine on small results and worth a look on large ones.

Plans for joins show one line per table, in the order SQLite visits them:

EXPLAIN QUERY PLAN
SELECT o.order_id, SUM(oi.quantity) AS units
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.customer_id = 42
GROUP BY o.order_id;

Both lines say SEARCH: find customer 42’s orders through idx_orders_customer, then each order’s items through idx_items_order. An index on every foreign key you join on is the single most valuable indexing habit.

Plans are predictions

A query plan is the optimizer’s choice, not a guarantee of speed, and it depends on the data. On a tiny table the optimizer may decide a scan is cheaper than an index, and it’ll be right. Both engines can keep statistics about each table, gathered by the ANALYZE command (PostgreSQL’s autovacuum runs it for you; in SQLite someone has to run it), and use them to estimate how many rows each step returns. When those statistics are stale, plans go wrong in surprising ways, which is one more question to ask the database owner about a query that slowed down “for no reason”.

Some filters can’t use an ordinary index however you write them. name LIKE '%mat%' has to look inside every value, because a sorted list can’t help you find text in the middle of words. If a search like that is frequent, the answer is a different tool, such as full-text search, rather than another index.

When to ask for a new index

Analysts often don’t own the schema, but you’ll be the one who notices the slow query. Support filters tickets by opened_at constantly, and that column has no index, so the plan for those queries is a SCAN. The request to the database owner would be:

CREATE INDEX idx_tickets_opened_at ON support_tickets (opened_at);

Indexes aren’t free. Each one takes storage and must be updated on every insert, update and delete, so a table with ten indexes writes noticeably slower. Ask for indexes on columns that frequent queries filter, join or sort on, and that narrow the result a lot. An index on status, with five values, rarely helps; an index on a date or an id usually does. For a multi-column index, put the column you test with = first and the range column second: (customer_id, order_date) serves “customer 42’s orders in 2025” with one search.

Fast queries are only useful if people can read them. Next: the style habits and dialect differences that make your SQL reviewable and portable.