Indexes and Query Plans

Introduction

Our shop has a handful of rows, so every query is instant. A real shop might have ten million orders. To find one customer's orders, the database could read every row and check each one. That is called a full table scan, and on a big table it is slow. An index is the fix.

The book analogy

A book has an index at the back. To find "photosynthesis", you do not read every page: you look the word up in the index, which tells you the page numbers.

A database index works the same way. It is an extra, sorted structure that maps a column's values to the rows that contain them. With it, the database can jump straight to the matching rows.

Reading a query plan

EXPLAIN QUERY PLAN shows how SQLite intends to run a query, without running it. Put it before any SELECT:

Look at the detail column. It says SCAN orders, which means every row is read. Now create an index on the column used in the WHERE and ask again:

Now it says SEARCH orders USING INDEX idx_orders_customer (customer_id=?). SEARCH means a fast lookup; SCAN means a full read. Learning to tell them apart is the heart of query tuning.

What an index can and cannot help

An index on customer_id helps when you filter or join on that column. It does not help when the query hides the column inside a calculation.

The first can use the index. The second must calculate price * 2 for every row, so it scans. The same goes for WHERE UPPER(name) = 'PEN SET' and for LIKE '%set', which starts with a wildcard. A pattern like LIKE 'Pen%' can sometimes use an index, but the exact rules depend on the database.

Primary keys and UNIQUE are already indexed

When you declare a PRIMARY KEY or UNIQUE constraint, the database builds an index for it automatically. That is how it checks quickly that a new value is not a duplicate.

Multi-column and covering indexes

An index can cover more than one column. Order matters: it works like a phone book sorted by last name, then first name. You can look up a last name alone, but not a first name alone.

Here the plan says USING COVERING INDEX: the index contains every column the query needs, so the table itself is not touched at all.

The cost of indexes

Indexes are not free.

• They take space.
• Every INSERT, UPDATE and DELETE must update the indexes too, so writes get slower.

So do not index every column. Index the columns you filter, join and sort by often, check the plan, and drop an index you do not use with DROP INDEX name;.

Try it yourself

Ask for the plan of a query that finds order lines for product 5: SELECT * FROM order_items WHERE product_id = 5. Note whether it scans or searches. Then create an index on order_items(product_id) and ask again.

Summary

• Without an index the database may scan every row. An index lets it search directly.
• EXPLAIN QUERY PLAN shows SCAN (slow, reads everything) or SEARCH ... USING INDEX (fast).
• Calculations on a column in WHERE stop an index from being used.
• Indexes speed reads but cost space and slow writes, so add them where queries need them.