DEV Community

t.labo
t.labo

Posted on Originally published at tlaboratory.net

SQL Indexes and EXPLAIN QUERY PLAN on 1M Rows

Series: SQL lecture 4/6 (intermediate). For developers who can write SELECT, JOIN and GROUP BY and now have a query that is "suddenly slow". After this lecture you will be able to read EXPLAIN QUERY PLAN, decide which index to create and in which column order, and spot the four patterns that make a database ignore an index.

If your app's order history page got slow after the table passed a few hundred thousand rows, or a code reviewer asked "is that column indexed?" and you were not sure how to check, this post is for you. Indexes are the single biggest performance lever in SQL, and also the easiest to get wrong: an index can make a query 1,900 times faster, and the wrong one can make another query ten times slower. Every number below comes from a 1,000,000-row table we generated with a fixed formula, so you can rebuild exactly the same data.

Environment: SQLite 3.51.0 (the sqlite3 CLI and the SQLite bundled with Python 3.9.6, also 3.51.0), Apple Silicon Mac (M1 Pro), macOS. Timings are medians; the table and its contents are deterministic.

Build a 1,000,000-row table in one second

SQLite's random() cannot be seeded, so the data comes from a linear congruential generator (a simple deterministic formula: next = previous × 1103515245 + 12345, modulo 2³¹) driven by a recursive CTE. Save this as make_orders.sql:

-- 1,000,000 shop orders, deterministic (no random(): an LCG on the row number)
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL,
  amount      INTEGER NOT NULL,   -- yen
  email       TEXT    NOT NULL,
  created_at  TEXT    NOT NULL    -- 'YYYY-MM-DD HH:MM:SS'
);
WITH RECURSIVE n(i, r) AS (
  SELECT 1, 42
  UNION ALL
  SELECT i + 1, (r * 1103515245 + 12345) % 2147483648 FROM n WHERE i < 1000000
)
INSERT INTO orders (id, customer_id, status, amount, email, created_at)
SELECT i,
       1 + r % 50000,
       CASE (r / 7) % 20 WHEN 0 THEN 'cancelled' WHEN 1 THEN 'refunded'
                         WHEN 2 THEN 'pending' ELSE 'paid' END,
       100 + (r / 13) % 20000,
       'User' || (1 + r % 50000) || '@example.com',
       datetime('2025-01-01', '+' || ((r / 17) % 31536000) || ' seconds')
FROM n;
Enter fullscreen mode Exit fullscreen mode
$ time sqlite3 shop.db < make_orders.sql
sqlite3 shop.db < make_orders.sql  0.73s user 0.08s system 99% cpu 0.824 total
$ sqlite3 shop.db "SELECT count(*), count(DISTINCT customer_id), min(created_at), max(created_at) FROM orders;"
1000000|50000|2025-01-01 00:00:00|2025-12-31 23:59:44
Enter fullscreen mode Exit fullscreen mode

One million orders from 50,000 customers across 2025, in a 62.6 MB file. (Recursive CTEs are lecture 6's topic; here we just use one as a row generator.)

Reading EXPLAIN QUERY PLAN: SCAN vs SEARCH

An index is a separate, sorted copy of one or more columns, each entry pointing back to its row (in SQLite, via the rowid). Because it is sorted, the database can binary-search it instead of reading every row. EXPLAIN QUERY PLAN (EQP) asks the database how it intends to run a query, without running it.

sqlite> .timer on
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SCAN orders
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.052 user 0.036612 sys 0.013766
Enter fullscreen mode Exit fullscreen mode

SCAN orders means "read the whole table". Now create an index and ask again:

sqlite> CREATE INDEX idx_orders_customer ON orders(customer_id);
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.000 user 0.000018 sys 0.000006
Enter fullscreen mode Exit fullscreen mode

The words to look for:

EQP says Meaning Usually
SCAN orders reads every row of the table slow on big tables, fine on small ones
SEARCH orders USING INDEX x (col=?) binary search in index x, then fetch each row what you want for selective filters
SEARCH … USING COVERING INDEX x the index alone has every needed column; table not touched fastest
SCAN orders USING COVERING INDEX x reads the whole index instead of the table still a full scan, just a narrower one
USE TEMP B-TREE FOR ORDER BY sorts the result separately an index could deliver the order for free
MULTI-INDEX OR each side of an OR uses its own index fine

The fourth row fools people: "USING COVERING INDEX" sounds good, but SCAN in front means every entry is still read.

The CLI's .timer has 1 ms resolution, which is too coarse here, so the timings in the rest of this post come from a small Python harness on the same SQLite version. It runs each query a few times untimed to warm the page cache, then reports the median:

import sqlite3, statistics, time
con = sqlite3.connect("shop.db")
def med(sql, params_list, reps=5):
    for p in params_list[:3]:
        con.execute(sql, p).fetchall()                    # warm-up
    ts = []
    for _ in range(reps):
        for p in params_list:
            t = time.perf_counter(); con.execute(sql, p).fetchall()
            ts.append((time.perf_counter() - t) * 1000)
    return statistics.median(ts)                          # ms
Enter fullscreen mode Exit fullscreen mode

Twenty customer ids from random.Random(42), five repetitions each:

Query Median
WHERE customer_id = ?, no index 46.595 ms
same, index on customer_id 0.024 ms
CREATE INDEX itself 459.7 ms, file 62.6 MB → 74.0 MB

About 1,900 times faster for half a second of build time and 11 MB of disk.

Composite indexes: column order decides everything

A composite index on (customer_id, created_at) is sorted like a phone book: by customer first, and by date within each customer. It can find "customer 5, December onwards" directly. An index on (created_at, customer_id) is sorted by date first; for the same question it can only jump to December and then check every December row for the customer.

(customer_id, created_at):    0.010 ms   SEARCH orders USING INDEX i (customer_id=? AND created_at>?)
(created_at, customer_id):    3.219 ms   SEARCH orders USING INDEX i (created_at>?)
Enter fullscreen mode Exit fullscreen mode

Both plans say SEARCH, so "it uses an index" is not enough. Read what is inside the parentheses: the second index uses only created_at>?. The rule of thumb is equality columns first, then the range or sort column.

The composite index also replaces the single-column one, because a query can use any leftmost prefix of it. After dropping idx_orders_customer:

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE customer_id = 5;
`--SEARCH orders USING COVERING INDEX idx_orders_cust_created (customer_id=?)
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE created_at >= '2025-12-01';
`--SCAN orders USING COVERING INDEX idx_orders_cust_created
Enter fullscreen mode Exit fullscreen mode

The second query skips the first column, so it scans. The same index also removes sorting:

-- index on (customer_id) only
SELECT id, amount, created_at FROM orders WHERE customer_id = 5 ORDER BY created_at DESC LIMIT 5;
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY
-- index on (customer_id, created_at)
`--SEARCH orders USING INDEX idx_orders_cust_created (customer_id=?)
Enter fullscreen mode Exit fullscreen mode

If you do not want to guess, the CLI has an index advisor, .expert, which proposed exactly this:

sqlite> .expert
sqlite> SELECT sum(amount) FROM orders WHERE customer_id = 12345 AND created_at >= '2025-06-01' ORDER BY created_at;
CREATE INDEX orders_idx_30ddf7e6 ON orders(customer_id, created_at);

SEARCH orders USING INDEX orders_idx_30ddf7e6 (customer_id=? AND created_at>?)
Enter fullscreen mode Exit fullscreen mode

Common mistake: the index exists, but the query cannot use it

All of these were run with indexes on customer_id, email and created_at present.

1. A function around the column. You wrote date(created_at) = '2025-03-01'. What happens: the index is sorted by created_at, not by date(created_at), so SQLite scans: 86.4 ms. Fix: rewrite as a range on the raw column: 0.069 ms.

-- SCAN orders USING COVERING INDEX idx_orders_created     86.407 ms
SELECT count(*) FROM orders WHERE date(created_at) = '2025-03-01';
-- SEARCH ... (created_at>? AND created_at<?)                0.069 ms
SELECT count(*) FROM orders WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';
Enter fullscreen mode Exit fullscreen mode

When you really need the function (case-insensitive e-mail lookup), index the expression itself:

sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SCAN orders
Run Time: real 0.160 user 0.138400 sys 0.013989
sqlite> CREATE INDEX idx_orders_email_lower ON orders(lower(email));
sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SEARCH orders USING INDEX idx_orders_email_lower (<expr>=?)
Run Time: real 0.000 user 0.000098 sys 0.000058
Enter fullscreen mode Exit fullscreen mode

2. LIKE on a normal index. LIKE 'User123@%' looks like a prefix search, yet it scanned. SQLite's LIKE is case-insensitive for ASCII by default, while a normal index is sorted case-sensitively (BINARY collation), so the index cannot answer it. GLOB (case-sensitive) can, and so can an index declared COLLATE NOCASE (condensed: query, plan, .timer real time):

WHERE email LIKE 'User123@%'             `--SCAN orders                                    0.081 s
WHERE email GLOB 'User123@*'             `--SEARCH ... idx_orders_email (email>? AND email<?)
-- after CREATE INDEX idx_orders_email_nocase ON orders(email COLLATE NOCASE);
WHERE email LIKE 'user123@%'             `--SEARCH ... idx_orders_email_nocase (email>? AND email<?)
WHERE email LIKE '%123@example.com'      `--SCAN orders                                    0.103 s
Enter fullscreen mode Exit fullscreen mode

The last line is the hard limit: with a leading % no B-tree index can help, because the index is sorted by the beginning of the string. That needs a full-text index (SQLite FTS5) or a reversed-string column.

3. OR across columns. customer_id = 7 OR email = 'User8@example.com' gave a MULTI-INDEX OR because both columns are indexed. customer_id = 7 OR amount = 500 gave SCAN orders: one unindexed side forces a full scan for the whole condition.

4. Skipping the first column of a composite index, shown above.

Something that does not break the index in SQLite: WHERE customer_id = '7' still showed SEARCH, because the INTEGER column's type affinity converts the text. In MySQL, comparing a string column to a number is a well-known index killer (not run here).

When an index makes a query slower

This is the measurement that surprised us. With an index on created_at, we summed amount over date ranges of increasing size, forcing each strategy with NOT INDEXED / INDEXED BY (7 runs each, median):

Range Rows matched Full scan Index on created_at Covering index (created_at, amount)
1 day 2,792 74.92 ms 4.40 ms 0.14 ms
1 month 84,728 78.96 ms 152.54 ms 3.34 ms
6 months 497,165 93.55 ms 965.40 ms 22.68 ms
12 months 1,000,000 94.53 ms 2,174.05 ms 44.25 ms

[[fig:sql-idx-range-ms]]

With a plain index, every matching entry means a separate lookup in the table to fetch amount, in random order. At about 8% of the table (one month) that was already twice as slow as reading the whole table in order. And SQLite chose that slow plan by itself, even after ANALYZE:

sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-07-01';
`--SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
497165|5022432858
Run Time: real 1.186 user 0.536726 sys 0.474209
sqlite> ANALYZE;
sqlite> SELECT * FROM sqlite_stat1;
orders|idx_orders_created|1000000 1
-- EQP again: same SEARCH ... USING INDEX idx_orders_created
Run Time: real 1.071 user 0.497285 sys 0.439523
Enter fullscreen mode Exit fullscreen mode

sqlite_stat1 only stores "rows per key", not the distribution of dates, so the planner cannot know that six months is half the table (the build we used was not compiled with SQLITE_ENABLE_STAT4, which would keep samples). Two fixes: make the index covering (22.7 ms), or tell SQLite not to use the index for this term with a unary plus:

sqlite> SELECT count(*), sum(amount) FROM orders WHERE +created_at >= '2025-01-01' AND +created_at < '2025-07-01';
`--SCAN orders
Run Time: real 0.095 user 0.077709 sys 0.013404
Enter fullscreen mode Exit fullscreen mode

What an index costs on writes

Every INSERT must also insert into every index. We added 100,000 rows to the 1,000,000-row table in a single transaction (3 runs each):

Secondary indexes Insert time (median) File size after
0 71.3 ms 68.8 MB
1 (customer_id) 213.6 ms 82.8 MB
3 (+ email, + created_at) 584.2 ms 151.3 MB

Three indexes made inserts about eight times slower and more than doubled the file. Text indexes (email, created_at) are the expensive ones. Indexes are not free; create them for queries you actually run.

[[fig:sql-idx-decide]]

Which index should I create?

Your query Index Why
WHERE a = ? (a) basic SEARCH
WHERE a = ? AND b = ? (a, b) or (b, a); put the more selective first both equality
WHERE a = ? AND b > ? (a, b) equality before range
WHERE a = ? ORDER BY b (a, b) removes the TEMP B-TREE sort
SELECT sum(c) … WHERE a BETWEEN … over many rows (a, c) covering avoids one table lookup per row
WHERE lower(x) = ? (lower(x)) expression index function must match exactly
WHERE x LIKE 'abc%' (x COLLATE NOCASE), or use GLOB LIKE is case-insensitive
WHERE x LIKE '%abc' none; FTS5 or a reversed column B-trees sort by prefix
WHERE status = 'paid' (85% of rows) usually none a scan is faster than 850,000 lookups

Exercises

  1. A page runs SELECT id, amount, created_at FROM orders WHERE status = 'paid' AND customer_id = ? ORDER BY created_at DESC LIMIT 3. Which single index gives a plan without USE TEMP B-TREE?
  2. WHERE substr(email, 1, 7) = 'User123' scans even though email is indexed. Rewrite it so that it uses the index, without creating a new one.

Answers

  1. CREATE INDEX idx_orders_cust_status_created ON orders(customer_id, status, created_at); Both equality columns first, the sort column last. Our plan: SEARCH orders USING INDEX idx_orders_cust_status_created (customer_id=? AND status=?), no temp B-tree, and the three newest paid orders of customer 5 came back already sorted.
  2. WHERE email >= 'User123' AND email < 'User124'. Every string starting with User123 sorts between those two values. Both versions returned 2,276 rows; the plan changed from SCAN … USING COVERING INDEX idx_orders_email to SEARCH … (email>? AND email<?).

In production: 301 queries to draw one statistics table

A lottery statistics page we run shows, for each of 43 numbers, how often it appeared in each of 7 positions. The old PHP code fills that table with one query per cell: 43 × 7 = 301 queries per page view, each against a database view that groups the whole results table. On 16 November 2026 our host's resource report caught a burst of them, single lookups like SELECT TIMES5 FROM <stats view> WHERE NUMBER5 = '41', each taking 30–40 ms in the snapshot, while one client loaded 166 of these pages within a minute.

We rebuilt the pattern in SQLite (2,155 draws, 7 number columns, one grouping view per column) and timed one page, median of 15 runs:

Python 3.9.6  SQLite 3.54.0  rows=2155
A) 301 lookups on aggregate views, no index :    19.67 ms per page (301 queries)
B) same 301 lookups, index on each column   :     1.06 ms per page (301 queries)
C) one UNION ALL + GROUP BY query           :     2.42 ms per page (1 query)
plan A/B: CO-ROUTINE v5 / SEARCH draws USING COVERING INDEX ix5 (n5=?) / SCAN v5
Enter fullscreen mode Exit fullscreen mode

The result was not what we expected:

  • In SQLite the index wins. The planner pushes WHERE number = 41 into the grouping view and answers it with SEARCH ... USING COVERING INDEX, so 301 tiny queries cost about 1 ms (19.67 ms without the index).
  • The single grouped query (C) is slower here, because it groups every row of every column. The page needs all of them anyway, so the work is the same; it just happens in one statement.
  • But SQLite runs inside the process. On a shared host the same page means 301 round trips to a MySQL server, and MySQL cannot merge a GROUP BY view into the outer query, so it tends to build the grouped result for each lookup. That is where 30–40 ms per query comes from. At that cost one page would need on the order of ten seconds of database time.

So an index answers "how expensive is one lookup", and the query count answers "how many times do we pay the fixed cost". Against a remote database the second one usually dominates. What we changed first was neither: the same statistics also reach the apps through JSON endpoints, and those now sit behind a 60-second response cache, so a burst of users costs one database pass instead of hundreds. The web page itself still runs its 301 queries; the rewrite we plan is option C, one query checked with EXPLAIN, plus the cache. The connection limits behind this story are in MySQL max_connections_per_hour on shared hosting.

Honest limits / version notes

  • Everything ran on SQLite 3.51.0 only. EQP output format changed over time (the tree drawing with `-- dates from 3.24); older versions print a flat table. .expert is a CLI feature and may be missing from minimal builds.
  • Expression indexes need SQLite 3.9 or later; NOT INDEXED, INDEXED BY and the unary-plus trick are SQLite-specific. MySQL has USE INDEX / IGNORE INDEX, PostgreSQL has no index hints at all (not run here).
  • PostgreSQL and MySQL keep histograms, so they would likely avoid the plain index for six months of data; we did not test them. The 8% crossover point depends on row width, cache and storage, so measure your own table.
  • Data is synthetic: an LCG is not a good random generator, and amounts repeat within a customer. That does not change the index behaviour, but do not use this table for statistics.
  • Timings are medians on one machine while other work was running; treat ratios as meaningful and absolute milliseconds as approximate.

Want to see why a parameterized query matters as much as an index? Read PDO + SQLite and SQL injection. For where SQL fits in a learning path, see the programming learning roadmap.


Previous: Lecture 3: JOIN, GROUP BY and subqueries · Next: Lecture 5: transactions, isolation and locking


Originally published at tlaboratory.net. Every output in this post was copied from a real terminal run.

Top comments (0)