The search that took ten seconds
The coffee shop has grown. The orders table now has a million rows.
A support agent types a customer's email to find their orders. The screen freezes for ten seconds. A developer adds one line to the database. The same search now takes a blink. What changed?
By the end of this lesson
Note
I can explain how an index makes a query faster.
Key words
| Word | What it means |
|---|---|
| Full scan | reading every row of a table |
| Index | a sorted copy of one column, pointing at each row |
| Query plan | the steps the database chooses to answer a query |
| Search | going straight to the rows through an index |
| Write | an insert, update or delete |
The cells students fill in are left blank in this preview.
Without an index, the database reads everything
Think of a paper phone book in no order. To find one name you read every page.
A table with no index is like that. To answer WHERE email = ... the database reads every row. This is a full scan. With ten rows it is instant. With a million rows it is slow, and every customer who searches waits.
An index is kept in sorted order
An index is a sorted copy of one column, with a pointer to each row. Because it is sorted, the database can jump to the middle and ask: is my value before or after this?
Each question throws away half of what is left. It is how you find a word in a dictionary without reading it from the start.
Worked example: halving a million rows
| Steps taken | Rows still to look at |
|---|---|
| 0 | 1,000,000 |
| 1 | 500,000 |
| 2 | 250,000 |
| 5 | about 31,000 |
| 10 | about 1,000 |
| 15 | about 30 |
| 20 | 1 |
The cells students fill in are left blank in this preview.
The query plan says SCAN or SEARCH
You can ask the database how it will run a query. Put EXPLAIN QUERY PLAN in front of it.
The answer is the query plan, in short words. SCAN orders means a full scan. SEARCH orders USING INDEX means it will use an index. When a page is slow, this is often the first thing a developer checks.
Try it: before and after an index
-- Ask how the database would run the query, without running it.
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE email = '[email protected]';
-- Build an index on the email column.
CREATE INDEX idx_orders_email ON orders(email);
-- Ask again. Look for SCAN or SEARCH in each answer.
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE email = '[email protected]';
Your turn: index the city column
-- No index on city yet: this plan says SCAN.
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE city = 'Leeds';
-- Write a CREATE INDEX for the city column here,
-- then run the plan again and look for SEARCH.
An index has a cost
An index is not free. It takes disk space. Every time a row is added, changed or deleted, each index on that table must be updated too.
So a table that is written to all day, such as a log of card payments, gets few indexes. A table that is searched all day gets indexes on the columns people search by.
A common mistake
Warning
Many people think an index on every column makes everything faster. It makes every write slower, because each index must be updated. Index the columns you often search, join or sort on, and no more.