Indexes and Query Performance

In the SQL for Beginners series every query you have written so far has read rows one after another: filtering in chapter 4 and joining in chapter 10 both meant looking at every row in the table. Chapter 19 of this 24-chapter course introduces indexes, the structure that lets SQLite jump straight to the rows you asked for, and it teaches you to read the one command that proves whether an index is actually being used: EXPLAIN QUERY PLAN.

What an index actually is

An index is the difference between reading a whole textbook to find one fact and flipping to the index at the back. Physically, SQLite stores each index as a second, much smaller B-tree: every entry holds the indexed column value plus the rowid of the row that value belongs to, and the tree is kept sorted by that value. A lookup becomes a handful of page reads (roughly log n) instead of a walk over every row (n). Your table is untouched — the index is an extra structure that SQLite maintains for you.

Six customers will not break a sweat either way, which is exactly why you need a tool that reports the plan rather than a stopwatch. EXPLAIN QUERY PLAN prints how SQLite intends to answer a query without running it:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'Berlin';"

Expected output:

QUERY PLAN
`--SCAN customers

Read that line as a set of instructions. SCAN customers means “open the table and walk every row, checking each one”. For a table of six rows that costs microseconds; for a table of six million it is the difference between a web page and a timeout. The plan comes back as a tree — the CLI draws branches with |-- and `--, and the innermost step (the one that runs for each row of its parent) sits at the bottom.

The planner’s four phrases

Every SQLite plan is built from a handful of verbs, and learning them takes one reading of this list:

• SCAN table — visit every row in the table. The thing you are trying to eliminate.
• SEARCH table USING INDEX idx (col=?) — descend a named index using a value from the WHERE clause, then fetch only the matching rows. The goal.
• SEARCH table USING INTEGER PRIMARY KEY (rowid=?) — a direct jump to one row by its integer primary key. Free and instant — this is why WHERE id = ... needs no index of its own.
• USE TEMP B-TREE FOR ORDER BY — the rows arrived in the wrong order, so SQLite is sorting them in memory (the same marker appears for GROUP BY and for DISTINCT).

Two commands share a name: plain EXPLAIN dumps the internal opcodes of the query (hundreds of rows, useful when you are debugging the engine), while EXPLAIN QUERY PLAN — the one you want — prints just the route. Note also that plans name tables by their alias: if you wrote FROM orders o, the plan will say o.

Your first index, before and after

Creating an index is a single statement naming the table and the column, with a convention of idx_table_column so the purpose is obvious a year later:

CREATE INDEX idx_customers_city ON customers(city);

Re-run the same plan now that the index exists:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'Berlin';"

Expected output:

QUERY PLAN
`--SEARCH customers USING INDEX idx_customers_city (city=?)

SEARCH ... USING INDEX ... (city=?) is the good news: SQLite descends the index B-tree using the value from your WHERE city = 'Berlin' — shown as the placeholder ? — collects the matching rowids, then fetches each row from the table. The query still returns exactly the same data; only the route changed:

sqlite3 shop.db "SELECT id, first_name, city FROM customers WHERE city = 'Berlin';"

Expected output: 3|Lena|Berlin

The index definition is stored in the schema like everything else, so you can always see what a database really has:

sqlite3 shop.db "SELECT sql FROM sqlite_master WHERE name = 'idx_customers_city';"

Expected output: CREATE INDEX idx_customers_city ON customers(city) (without a trailing semicolon, because that is how SQLite stores it). To see every index you have added in one shot, list the ones SQLite did not create for you:

sqlite3 shop.db "SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND name NOT LIKE 'sqlite_%' ORDER BY name;"

Expected output: idx_customers_city|customers — one line per index, with the table it belongs to. Auto-generated indexes behind PRIMARY KEY and UNIQUE constraints are also rows in sqlite_master, but they carry the reserved sqlite_autoindex_ prefix, which is why the query excludes them. Other engines spell the inventory differently: MySQL answers SHOW INDEX FROM customers;, PostgreSQL shows it in \d customers or in the pg_indexes catalogue, and both accept an EXPLAIN of their own — note that PostgreSQL’s EXPLAIN ANALYZE and MySQL’s EXPLAIN FORMAT = JSON tell you even more, because EXPLAIN ANALYZE actually runs the query and reports real timings.

Covering indexes: the plan that never touches the table

An index already holds the values of its column, so a query that needs nothing else can be answered entirely inside the index — SQLite says so with the word COVERING. Ask for one extra column and you can watch the difference in a single word:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT city FROM customers WHERE city = 'Berlin';"

Expected output:

QUERY PLAN
`--SEARCH customers USING COVERING INDEX idx_customers_city (city=?)

SELECT city never leaves the index: the answer is sitting right there in the key. Ask for the whole row instead and SQLite must jump from each index entry to the table:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'Berlin';"

Expected output:

QUERY PLAN
`--SEARCH customers USING INDEX idx_customers_city (city=?)

The same trick makes COUNT(*) cheap, because counting rows needs no table columns at all — SQLite simply picks the smallest index it has and walks that:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT COUNT(*) FROM customers;"

Expected output:

QUERY PLAN
`--SCAN customers USING COVERING INDEX idx_customers_city

Which columns deserve an index

Three places pay for an index: columns in a WHERE clause, columns that join tables together, and the leading columns of an ORDER BY or GROUP BY. When two of them appear together, one composite index can serve all three — the order of the columns matters, and only the leftmost prefix is guaranteed to be usable:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Before creating it, this plan scans and then sorts with a temporary B-tree; afterwards the index gives sorted rows for free:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1 ORDER BY order_date;"

Expected output before:

QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

Expected output after:

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_date (customer_id=?)

Ask for the second column alone and the index is useless, because entries are sorted by customer_id first — the leftmost rule:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE order_date = '2026-01-12';"

Expected output:

QUERY PLAN
`--SCAN orders

Nothing is free, so every index is a trade:

When an index is quietly ignored

The most common trap: wrapping the column in a function. The index stores city sorted, not LOWER(city), so the engine has no choice but to read every row and compute the expression itself:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM customers WHERE LOWER(city) = 'berlin';"

Expected output:

QUERY PLAN
`--SCAN customers

The fix is to index the expression itself, so the value being searched for and the value being sorted are the same thing:

CREATE INDEX idx_customers_lowercity ON customers(LOWER(city));
-- now the plan becomes SEARCH customers USING INDEX idx_customers_lowercity (<expr>=?)

The same limitation shows up in patterns with a leading wildcard: WHERE name LIKE '%tech%' can never use a B-tree, because the index cannot know where a match begins. A prefix pattern (LIKE 'tech%') at least gives the engine something to work with — whether it actually uses the index then depends on case sensitivity and collation, which is exactly why the plan, not the shape of your query text, is the thing to trust.

One more reason to distrust a plan: it is a guess. SQLite decides between an index and a scan using the statistics it keeps about each index, and an empty statistics table means guesswork. Run ANALYZE; after loading real data (or on a schedule) to refresh them — MySQL runs this automatically as part of its adaptive sampling, and PostgreSQL keeps per-column statistics updated by ANALYZE on its own schedule. Finally, remember that indexes are not free lunch: each one slows every INSERT, UPDATE and DELETE on that table, so a column you never search does not deserve one.

Indexes and joins

Join keys are where indexes earn their keep twice over. order_items.order_id points at orders.id, and SQLite will happily scan all eight item rows for every order it visits — unless the column is indexed, in which case each visit becomes a B-tree lookup:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT o.status, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.customer_id = 1;"

Expected output before:

QUERY PLAN
|--SCAN oi
`--SEARCH o USING INTEGER PRIMARY KEY (rowid=?)

Now add the index on the join key and both sides are searched instead of scanned:

CREATE INDEX idx_order_items_order ON order_items(order_id);

Expected output after: (same command, re-run)

QUERY PLAN
|--SEARCH o USING INDEX idx_orders_customer_date (customer_id=?)
`--SEARCH oi USING INDEX idx_order_items_order (order_id=?)

Two engine differences worth knowing before you port this habit. SQLite does not index a foreign key for you — you declare it, as above. MySQL’s InnoDB creates one automatically for every foreign key, so SHOW INDEX surprises newcomers with entries they never wrote. PostgreSQL, like SQLite, creates nothing on its own: a REFERENCES clause without a matching CREATE INDEX is a slow join waiting to happen. The habit to keep is mechanical — write the join, print the plan, and make sure both sides say SEARCH.

Common mistakes

• Indexing every column — “it might help” multiplies write costs and doubles the table’s footprint. Index the columns your WHERE, joins and ORDER BY actually use, then verify with EXPLAIN QUERY PLAN.
• Forgetting the leftmost rule — an index on (customer_id, order_date) never serves a bare order_date lookup. Order composite columns from most-used equality first.
• Wrapping the column in a function — WHERE LOWER(city) = ... silently reverts to a full scan. Search the column as stored, or index the expression.
• Assuming foreign keys are indexed — SQLite and PostgreSQL do not create one; every child-row lookup becomes a scan. Add the index with the REFERENCES.
• Tuning without a plan — guessing that a query is “probably fine” is how a report that took a second becomes one that takes a minute. Read the plan first, then decide.

Practice checklist

1. Print the plan for a query you already know is slow, and find the word SCAN in it.
2. Add idx_customers_city, re-run the plan, and confirm it changes to SEARCH ... USING INDEX.
3. Create idx_orders_customer_date and prove the leftmost rule by querying order_date alone.
4. Write a query with LOWER(city), watch the index get ignored, then add an expression index and watch it come back.
5. Run the join from this chapter before and after indexing order_items.order_id, and compare both plans side by side.

What's next?

An index speeds up how a query reads one shape — but you probably retype that shape everywhere. Next you store it once: Views and Query Reuse shows how a saved SELECT becomes a name you can query like a table.