Finding one name in a thick book
A phone book has 100,000 names. You do not read from page one. You use the sorted letters at the edge.
A table with a million rows has the same trouble. It has two tools to help.
Key words
| Word | What it means |
|---|---|
| Index | a sorted lookup that finds rows without reading every row |
| Partition | one slice of a big table, kept apart |
| Partitioning | splitting a big table into slices by a column |
| Full scan | reading every row to find a match |
| Database | an organised store of data that you can search |
| Table | rows and columns holding one kind of thing |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can explain how an index and a partition make a big table quicker to search.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
An index saves reading every row
Without help, the database does a full scan. It reads every row to find a match.
An index is a sorted lookup on one column. The database jumps close to the row it wants. The column that names each row is usually indexed for you.
Try it: count the steps
ids = [3, 7, 12, 19, 25, 31, 40, 52]
target = 40
steps = 0
for one in ids:
steps = steps + 1
if one == target:
break
print("A full scan took", steps, "steps")
# Try it: change target to 3. How many steps now?
Try it: make an index
-- An index is made for one column that is often searched.
create index orders_month_index on orders (order_month);
select order_id, amount_pence from orders where order_month = '2026-03';
-- Try it: search for 2026-01.An index has a cost
Tip
An index takes space. It must be updated when rows change, so adding rows is slower. Index only the columns you search often.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.