Why one query takes 2 ms and another 20 s

A table is stored in one order, usually by its primary key. Ask for “orders where customer_id = 4242” and, without help, the database has one option: read every order and keep the ones that match. That’s a full table scan.

An index is a second, sorted copy of one or more columns, each entry pointing back to its row. With an index on customer_id, the database does what you’d do with a phone book: jump to the right page. SQLite’s documentation describes the lookup as a binary search, so the time grows with log N, the logarithm of the number of rows, not with N itself.

Measure it

The console below builds 200,000 orders and 20,000 customers in your browser. That takes a second or two. Run the query, and read the plan SQLite chose and how many rows it had to read.

Measured for this lesson with the same data and the same SQLite build, in Node 24 on a Windows laptop: the scan took 31 ms and the indexed lookup 0.48 ms. Your browser’s numbers will differ, but the ratio is the point.

That’s how a 2 ms query becomes a 20 s query. A scan’s cost grows with the table, so a thousand times more rows means roughly a thousand times longer. The indexed lookup grows with log N and barely moves. The query that was fine in testing with a thousand rows is the one that takes the site down at a hundred million.

The function that hides the column

Log-in pages usually match emails without caring about capitals: WHERE lower(email) = lower(?). Add an index on email and the plan is still SCAN customers. The index stores email, and the query asks about lower(email), a different value. SQLite uses an expression index only when the query contains the expression exactly as it’s written in CREATE INDEX. PostgreSQL’s documentation uses this very lower(col1) example.

Two fixes work:

• Index the expression: CREATE INDEX ... ON customers(lower(email)).
• Store the email already lower-cased, and compare plain values.

sqlite3 :memory:

.timer on
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, created_at TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)
INSERT INTO orders SELECT i, (i * 7919) % 20000 + 1, ((i * 104729) % 50000) / 100.0, date('2023-01-01', '+' || ((i * 31) % 1095) || ' days') FROM n;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 4242;
SELECT count(*) FROM orders WHERE customer_id = 4242;
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 4242;
SELECT count(*) FROM orders WHERE customer_id = 4242;