The page that took ten seconds
A database index lets a query find rows without reading the whole table, and EXPLAIN shows whether the index is used. It matters because a query that is instant on a small table can crawl on a big one.
A support team opens a customer's tickets. On the first day the page is instant. A year later there are millions of tickets and the same page takes ten seconds.
Nobody changed the query. The table grew, and the database now reads every ticket to find yours.
By the end of this lesson
Note
I can make a query faster with an index and read EXPLAIN to check it.
Key words
| Word | What it means | Example |
|---|---|---|
| Index | a sorted list kept beside a table so the database can find rows without reading them all | |
| Full scan | reading every row of a table | type ALL in EXPLAIN |
| EXPLAIN | a command that shows how the database plans to run a query, without running it | |
| Query plan | the steps the database chooses to answer a query | |
| Estimate | the database's own guess, not a count |
The cells students fill in are left blank in this preview.
A table big enough to feel slow
The lab's shop has only a few dozen products, so every query is instant. To see an index at work you need more rows.
These Runs make a table called tickets in your own database, with 5000 rows. The shop is not touched.
Run them again at any time to start over.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the client
alias db='mariadb -h db -u student -pstudent -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make your own database
db "CREATE DATABASE IF NOT EXISTS student_depot"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Throw away an old table
db "DROP TABLE IF EXISTS student_depot.tickets"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the table
db "CREATE TABLE student_depot.tickets (id INT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, opened_on DATE NOT NULL)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Fill it with 5000 tickets
db "SET SESSION cte_max_recursion_depth = 10000; INSERT INTO student_depot.tickets WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT i, 1 + (i * 37) % 800, IF(i % 5 = 0, 'open', 'closed'), DATE_ADD('2024-01-01', INTERVAL i % 365 DAY) FROM n"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count them
db "SELECT COUNT(*) AS rows_in_table FROM student_depot.tickets"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
EXPLAIN shows the plan
Put the word EXPLAIN in front of a query. The database does not run it. It shows how it would.
Three columns matter. type says how the table is read. key names the index used, or NULL for none. rows is its estimate of how many rows it must look at.
Ask it about one customer's tickets.
Plan for one customer, no index
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: reading the plan
| Column | What you saw | What it means |
|---|---|---|
| type | ALL | a full scan: every row is read |
| key | NULL | no index is used |
| rows | about 5000 | it expects to read the whole table |
| Extra | Using where | it reads each row, then tests customer_id |
The cells students fill in are left blank in this preview.
An index is a sorted shortcut
A book's index lists words in order with page numbers. You do not read the whole book to find one word.
A database index works the same way. It keeps customer_id values in order, each with the place of its row.
CREATE INDEX makes one. Give it a name and the table and column.
Make an index on customer_id
db "CREATE INDEX idx_customer ON student_depot.tickets (customer_id)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Ask for the plan again
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
From 5000 rows to about 6
Now type is ref, key is idx_customer and rows is 6. The database goes straight to that customer's tickets.
The query did not change. Only the plan did.
A primary key already has an index. So does a foreign key: MySQL makes one for you.
Look at the orders table's indexes
mariadb -h db -u student -pstudent shop -t -e "SHOW INDEX FROM orders"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
An index has a price
An index is a second copy of a column, kept in order. It takes space.
Every INSERT, UPDATE and DELETE must also change the index. So each extra index makes writing a little slower.
Add an index for the questions you ask often, not for every column.
See the space an index takes
db "ANALYZE TABLE student_depot.tickets; SELECT data_length, index_length FROM information_schema.tables WHERE table_schema = 'student_depot' AND table_name = 'tickets'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think an index is always used once it exists. It is not. If the query hides the column inside a function, the database cannot use the sorted list. Look at the plan, do not assume.
Index the opened date
db "CREATE INDEX idx_opened ON student_depot.tickets (opened_on)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Plan with a function on the column
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE YEAR(opened_on) = 2024"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Plan with a range on the column
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE opened_on >= '2024-03-01' AND opened_on < '2024-03-08'"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.