Reading a slow query

A query is slow mostly because of how much data it makes the database read. With no help, finding the orders of one customer means reading every row of orders and checking each one: a full scan. That's fine for ten rows and painful for fifty million.

An index is a sorted copy of one or more columns with a pointer back to each row, like the index at the back of a book. With an index on orders(customer_id), the database jumps straight to customer 3's rows. Indexes speed up lookups, range filters, joins and sorts on those columns, but they take space and slow down every insert and update a little, so you add them for the queries you actually run.

EXPLAIN shows the plan: what the database will do, step by step. In SQLite it's EXPLAIN QUERY PLAN; in PostgreSQL EXPLAIN ANALYZE also runs the query and shows the real time and row count of each step. Here is SQLite before and after adding an index:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;
-- SCAN orders

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;
-- SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

Some habits stop an index from being used. Wrapping the column in a function, like WHERE strftime('%Y', ordered_on) = '2024', means the database has to work the function out for every row. Ask for a range on the raw column instead: WHERE ordered_on >= '2024-01-01' AND ordered_on < '2025-01-01'. A pattern that starts with a wildcard, LIKE '%@example.com', can't use a normal index either, for the same reason a phone book can't help you find everyone whose name ends in -son.

Other things to look for in a slow query: SELECT * reading columns nobody uses; a join that fans out to millions of rows, followed by DISTINCT or GROUP BY to squash them back; filtering after a big join when you could filter before it; a correlated subquery running once for each of a million rows; and sorting a huge result that only needed the top ten.

Data warehouses (BigQuery, Snowflake, Redshift and the like) store each column separately and mostly don't use indexes like these. They rely on partitioning (splitting a table by date, say) and clustering or sort keys so a query can skip whole blocks. There, SELECT * really costs: you read, and often pay for, every column. Filter on the partition column and select only what you need.

A routine that works anywhere: read the plan, find the step that handles the most rows, check that filters can use an index or partition, check row counts after each join, change one thing, and measure again.