Why is this slow? Indexes and EXPLAIN

A full scan

Put EXPLAIN QUERY PLAN before a query to see the plan the database would use without running the query itself.

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE name = 'Quill Helmet';

SCAN products

SCAN means the database reads every row and tests each one. products.name has no index, so no faster path is available. The difference is small for 400 rows but grows with the table: the same plan reads ten million rows on a ten-million-row table.

An index search

price does have an index.

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price > 2000;

SEARCH products USING INDEX idx_products_price (price>?)

SEARCH indicates an index lookup. The database finds where prices above 2000 begin in the index and reads only the relevant rows. Other engines label the same distinction differently: PostgreSQL uses Seq Scan and Index Scan, MySQL type: ALL and type: range, and SQL Server Table Scan and Index Seek.

A join

EXPLAIN QUERY PLAN
SELECT o.order_number, c.last_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered';

SEARCH o USING INDEX idx_orders_status (status=?)
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

Read this as a loop: find delivered orders through the status index, then look up each customer by primary key. Both steps use searches. A SCAN on the inner side is a warning sign because it reads a whole table for each outer row. Foreign key columns usually need an index; most databases, including SQLite and MySQL, do not create one automatically.

How to lose an index

orders.ordered_at is indexed. This query filters on it and still reads the whole table.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE substr(ordered_at, 1, 4) = '2025';

SCAN orders

The index stores ordered_at, not substr(ordered_at, 1, 4). Wrapping the column in a function prevents the database from using the indexed order, so it must calculate the expression for every row. The same applies to WHERE YEAR(created_at) = 2025, WHERE UPPER(email) = ..., and WHERE price * 1.1 > 100.

Rewrite the condition so the indexed column is compared directly:

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at <  '2026-01-01';

SEARCH orders USING INDEX idx_orders_ordered_at (ordered_at>? AND ordered_at<?)

This returns the same rows but can use the index. Keep an indexed column by itself on one side of a comparison and move calculations to the other side. Replacing a date function with a half-open range is a common example.

ORDER BY can be free

EXPLAIN QUERY PLAN
SELECT * FROM products ORDER BY price DESC LIMIT 20;

SCAN products USING INDEX idx_products_price

This is a third pattern: not a search, but not an unordered scan either. The query needs price order, and the index already provides it, so the database walks the index and stops after 20 rows. Without the index it would read all 400 rows, sort them, and discard 380.

This is why indexes help ORDER BY ... LIMIT as well as WHERE. For “top 20 by X” queries, the right index can read 20 rows instead of the whole table.

A short checklist

• Index the columns you filter and join on — foreign keys especially, since they rarely get one automatically.
• Keep indexed columns out of function calls in WHERE.
• Prefer LIKE 'abc%' over LIKE '%abc%' where you can; a leading wildcard cannot use an index.
• Indexes are not free — every one of them has to be updated on write, so index what you actually query, not every column.
• Measure, do not guess. Run EXPLAIN before and after the change and compare the two plans.

Where to go next

That is the core path. The dialect pages put three tasks — paging, upsert, and string and date handling — side by side in MySQL, PostgreSQL, SQL Server, Oracle and SQLite, because those are the three where the syntax differs enough that a working query has to be rewritten before it runs on another engine.