Indexes and Query Performance

Introduction

With 30 rows, every query is instant. With millions, a badly written query can take minutes. An index helps the database find rows without reading the whole table, like the index of a book. This lesson uses SQLite's tools to see what the database is doing, with real output from the library database.

Without an index

Ask for one member's loans, and view the plan:

print(con.execute("EXPLAIN QUERY PLAN SELECT * FROM loans WHERE member_id = 3").fetchall())

[(2, 0, 216, 'SCAN loans')]

SCAN loans means the database reads every row of the table to find the matches. With 30 rows this is nothing, but with 30 million it is slow. (The numbers at the start of the tuple are internal ids and may differ between versions. Read the text.)

Add an index

con.execute("CREATE INDEX idx_loans_member ON loans(member_id)")
print(con.execute("EXPLAIN QUERY PLAN SELECT * FROM loans WHERE member_id = 3").fetchall())

[(3, 0, 61, 'SEARCH loans USING INDEX idx_loans_member (member_id=?)')]

Now it says SEARCH ... USING INDEX. The database jumps straight to the rows for member 3, instead of scanning everything.

How indexes work and what they cost

An index is a sorted structure that points to rows. Searching it is fast, even for huge tables. But:

• It uses extra storage.
• It makes inserts, updates and deletes slower, because the index must be updated too.
• It only helps queries that filter, join or sort on the indexed columns.
• Too many indexes waste space and slow writes.

Primary keys are indexed automatically.

Where indexes help

• Columns in WHERE filters used often, such as member_id
• Columns used to JOIN tables, such as foreign keys (book_id, member_id)
• Columns used in ORDER BY on large results
• Composite indexes on several columns used together, where the order of columns matters

Indexes do not help when:

• The table is small.
• The query returns most of the rows anyway.
• You wrap the column in a function, such as WHERE lower(city) = 'leeds', which can prevent index use. Store clean values instead, or create an index on the expression where supported.
• The column has very few distinct values, such as a yes/no flag, and most rows match.

A measuring habit

1. Find slow queries by timing them.
2. Use EXPLAIN QUERY PLAN to see why.
3. Add one index that fits the query.
4. Re-check the plan and the time.
5. Remove indexes that nothing uses.

Never guess. The plan tells you what the database will really do. Other databases have similar tools, usually called EXPLAIN, with more detail.

Other speed tips

• Select only the columns you need, not SELECT * on wide tables.
• Filter early in the query, so less data flows through.
• Aggregate in the database rather than pulling millions of rows into pandas.
• Use LIMIT when exploring.
• For bigger workloads, use a database server designed for them, and read its documentation on indexing and statistics.

Try it yourself

Create a table with 100,000 rows of random loans, time the query for one member with and without an index, and look at both plans. Write down the difference you observe.

Summary

• A query plan shows how the database finds rows: SCAN reads everything, SEARCH USING INDEX jumps to matches.
• Index columns used in filters and joins, and accept the cost in storage and slower writes.
• Measure first, add one index at a time, and check the plan again.