SQL Indexing Quiz: B+Trees, EXPLAIN and Sargable Queries
Test yourself: 12 questions and three SQL tasks on MySQL indexes, clustered keys, composite index order, EXPLAIN, sargable rewrites and covering indexes.
- Course: SQL study plan
- Module: Indexes and performance
- Kind: Checkpoint — cleared at 70%
- Reading time: 25 min
- Runtime: MySQL 8 syntax on SQLite 3.49
Checkpoint — Indexes and performance is the checkpoint that closes the Indexes and performance module: a graded quiz and whole-program exercises, passed at 70%.
Instructions
This checkpoint covers the whole module: what an index is and why it is a B+tree, InnoDB's clustered primary key and the secondary indexes that point to it, selectivity and the cost of every index, composite indexes and the leftmost prefix rule, how a range stops a seek, reading MySQL's EXPLAIN and EXPLAIN ANALYZE, sargable predicates and their rewrites, and covering indexes.
How it works. Twelve questions and three SQL tasks. You need 70% on the questions and every task accepted to clear the module. You can retake it as often as you like; your best score counts.
Before you start, make sure you can answer these from memory:
- Why is a B+tree three or four levels deep for millions of rows, and what do its linked leaves make cheap?
- What does an InnoDB secondary index store as its pointer to the row, and what follows from that?
- Which of
WHERE a = 1,WHERE b = 2andWHERE a = 1 AND c = 3can seek on an index on(a, b, c), and how far? - Why do equality columns go before the range column in a composite index?
- Put
ALL,const,range,ref,indexandeq_refin order from best to worst. - What do
Using index,Using index condition,Using filesortandUsing temporarymean? - Rewrite
WHERE YEAR(d) = 2024andWHERE LEFT(code, 2) = 'IN'so an index can be used. - Why does
SELECT *almost never use a covering index?
The three tasks are practical maintenance work: remove the duplicate rows that block a UNIQUE index, create it and show what remains; build an index for a monthly payments report and write the report with a sargable date range; and find the indexes a usage log says are never read, the ones a DBA would consider dropping.
Common questions
What is the difference between a clustered and a secondary index in InnoDB?
The clustered index is the table itself, a B+tree on the primary key whose leaves hold the full rows, so there is exactly one. A secondary index holds its columns plus the primary key, so a lookup through it finds the key and then searches the clustered index for the row unless the index covers the query.
Why can an index on (a, b) not serve WHERE b = 5?
The entries are sorted by a first and by b only within each value of a, so the b = 5 entries are scattered across the whole index. The leftmost prefix rule allows a seek on a, or on a and b, but not on b alone; MySQL can only scan the index or, sometimes, use a skip scan.
How do you find the rows that stop a UNIQUE index being created?
Group by the column, or the columns, the index will cover and keep the groups with more than one row: SELECT email, COUNT() FROM users GROUP BY email HAVING COUNT() > 1. Resolve those duplicates first, then CREATE UNIQUE INDEX succeeds.
Exercises
Clear the way for a UNIQUE index
Sign-up allowed duplicate e-mail addresses until today, and CREATE UNIQUE INDEX fails while they exist. Write a script that:
- lists the duplicated addresses —
email,copies— ordered byemail; - deletes every duplicate row except the one with the lowest
idper address; - creates the unique index
uq_users_emailonusers (email); - returns the remaining users —
id,email— ordered byid(this SELECT is in the starter).
MySQL refuses a DELETE whose subquery reads the table being deleted from (error 1093) unless the subquery is wrapped in a derived table — write it that way.
users:
| id | name | |
|---|---|---|
| 1 | asha@example.com | Asha |
| 2 | ravi@example.com | Ravi |
| 3 | asha@example.com | Asha R |
| 4 | lee@example.com | Lee |
| 5 | ravi@example.com | Ravi K |
| 6 | asha@example.com | A. Rao |
email | copies
asha@example.com | 3
ravi@example.com | 2
id | email
1 | asha@example.com
2 | ravi@example.com
4 | lee@example.comAn indexed monthly payments report
Create an index named idx_payments_paid_at on payments (paid_at), then report April 2024 by payment method with a sargable range on paid_at (a DATETIME).
payments:
| id | paid_at | method | amount |
|---|---|---|---|
| 1 | 2024-03-31 23:59:59 | upi | 500 |
| 2 | 2024-04-01 00:00:00 | upi | 250 |
| 3 | 2024-04-12 14:20:00 | card | 1800 |
| 4 | 2024-04-30 22:15:00 | upi | 900 |
| 5 | 2024-05-01 00:00:00 | card | 700 |
| 6 | 2024-04-18 09:00:00 | netbanking | 1150 |
| 7 | 2024-04-18 09:05:00 | card | 200 |
Output columns: method, payments (count), total (sum of amount), ordered by total descending, then method.
method | payments | total
card | 2 | 2000
netbanking | 1 | 1150
upi | 2 | 1150Indexes nobody reads
Before a release, the DBA wants the indexes that cost writes and serve no reads. table_indexes lists every index; index_usage holds the read counters gathered since the last restart (an index absent from it was never read). Return every index that was never read (no usage row, or read_count 0) and is neither the PRIMARY key nor unique — those enforce constraints and stay.
table_indexes:
| table_name | index_name | is_unique |
|---|---|---|
| orders | PRIMARY | 1 |
| orders | idx_orders_customer | 0 |
| orders | idx_orders_status | 0 |
| orders | idx_orders_customer_old | 0 |
| users | PRIMARY | 1 |
| users | uq_users_email | 1 |
| users | idx_users_city | 0 |
index_usage:
| table_name | index_name | read_count |
|---|---|---|
| orders | PRIMARY | 98000 |
| orders | idx_orders_customer | 4300 |
| orders | idx_orders_status | 0 |
| users | idx_users_city | 12 |
Output columns: table_name, index_name, ordered by table_name, then index_name.
table_name | index_name
orders | idx_orders_customer_old
orders | idx_orders_statusIn this module: Indexes and performance
← Covering indexes and index-only reads · Transactions and ACID →