Practice: indexes and query plans
A review of this module. Skip it if you feel confident; the next module does not need it.
Fix the mistakes
The slips that cost marks in this module. Spot each one, then check the fix.
Indexing comes down to a few repeatable moves: read the query, pick the columns and their order, check the plan, and prove the result with numbers. Each section below is one of those moves, practised on a 40,000-row hospital appointments table.
Choosing an index for a query
Pick an index from the query, not from the table. The script below chooses an index for one doctor's appointments on one day, on a 40,000-row table.
Example 1 · Choose an index: one doctor, one day
SET SESSION cte_max_recursion_depth = 40000;
CREATE TABLE appointments ( appointment_id INT PRIMARY KEY, doctor_id INT NOT NULL, patient_id INT NOT NULL, appt_date DATE NOT NULL, status VARCHAR(10) NOT NULL, fee DECIMAL(7,2) NOT NULL);
INSERT INTO appointmentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 40000)SELECT n, 1 + n % 80, 1 + (n * 13) % 8000, DATE '2026-01-01' + INTERVAL (n DIV 220) DAY, CASE WHEN (n DIV 3) % 10 < 7 THEN 'DONE' WHEN (n DIV 3) % 10 < 9 THEN 'BOOKED' ELSE 'CANCELLED' END, 300 + (n * 17) % 900FROM seq;
-- The reception screen: Dr. 17's appointments on 10 MarchSELECT COUNT(*) AS appointments, SUM(fee) AS feesFROM appointmentsWHERE doctor_id = 17 AND appt_date = '2026-03-10';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
CREATE INDEX idx_appt_doctor_date ON appointments (doctor_id, appt_date);
SELECT COUNT(*) AS appointments, SUM(fee) AS feesFROM appointmentsWHERE doctor_id = 17 AND appt_date = '2026-03-10';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;Output
+--------------+---------+ | appointments | fees | +--------------+---------+ | 3 | 2856.00 | +--------------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 40000 | 1 | +---------------+---------------+ +--------------+---------+ | appointments | fees | +--------------+---------+ | 3 | 2856.00 | +--------------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 3 | 1 | +---------------+---------------+
What this code does
- The generator spreads 40,000 appointments over 80 doctors and about 180 days, 220 a day. Without an index, finding Dr. 17's appointments on one day examined all 40,000 rows.
- Both conditions are equalities, so
(doctor_id, appt_date)leads straight to the matching entries: rows examined equals the rows counted. The same index also serves "all of Dr. 17's appointments" through its leftmost column.
Read the WHERE clause first: columns compared with = go first in the index, in any order among themselves; the one column compared with a range (BETWEEN, <, >=) goes after them. If the query sorts with ORDER BY after equality filters, the sort column can follow the equality columns, so the rows come out of the index already sorted. Then look at the other queries on the same table. If an existing index already starts with the columns you need, extend it or reuse it instead of adding a new one. Finally, prove the change: rows examined before and after, and the plan.
WHERE doctor_id = ? AND appt_date = ?→(doctor_id, appt_date).WHERE doctor_id = ? AND appt_date BETWEEN ? AND ?→(doctor_id, appt_date): equality first, range last.WHERE doctor_id = ? ORDER BY appt_date DESC LIMIT 10→(doctor_id, appt_date)serves both the filter and the sort.- Add a selected column at the end only when the query is frequent enough to deserve a covering index.
Reading and comparing plans
Two indexes can serve the same query. The script below compares their plans side by side and keeps the better one.
Example 2 · Compare two candidate indexes
SET SESSION cte_max_recursion_depth = 40000;
CREATE TABLE appointments ( appointment_id INT PRIMARY KEY, doctor_id INT NOT NULL, patient_id INT NOT NULL, appt_date DATE NOT NULL, status VARCHAR(10) NOT NULL, fee DECIMAL(7,2) NOT NULL);
INSERT INTO appointmentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 40000)SELECT n, 1 + n % 80, 1 + (n * 13) % 8000, DATE '2026-01-01' + INTERVAL (n DIV 220) DAY, CASE WHEN (n DIV 3) % 10 < 7 THEN 'DONE' WHEN (n DIV 3) % 10 < 9 THEN 'BOOKED' ELSE 'CANCELLED' END, 300 + (n * 17) % 900FROM seq;
CREATE INDEX idx_appt_date_doctor ON appointments (appt_date, doctor_id);CREATE INDEX idx_appt_doctor_date ON appointments (doctor_id, appt_date);ANALYZE TABLE appointments;
-- Let the plan show which index MySQL prefers for a rangeEXPLAIN SELECT COUNT(*) FROM appointmentsWHERE doctor_id = 17 AND appt_date BETWEEN '2026-03-01' AND '2026-03-31';
-- Force each one to compare themEXPLAIN SELECT COUNT(*) FROM appointments FORCE INDEX (idx_appt_date_doctor)WHERE doctor_id = 17 AND appt_date BETWEEN '2026-03-01' AND '2026-03-31';Sample output
+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.appointments | analyze | status | OK | +-----------------------+---------+----------+----------+ +----+-------------+--------------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | appointments | NULL | range | idx_appt_date_doctor,idx_appt_doctor_date | idx_appt_doctor_date | 7 | NULL | 85 | 100.00 | Using where; Using index | +----+-------------+--------------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+--------------------------+ +----+-------------+--------------+------------+-------+----------------------+----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+----------------------+----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | appointments | NULL | range | idx_appt_date_doctor | idx_appt_date_doctor | 7 | NULL | 6619 | 1.25 | Using where; Using index | +----+-------------+--------------+------------+-------+----------------------+----------------------+---------+------+------+----------+--------------------------+
What this code does
- With both indexes available, the optimizer picks
idx_appt_doctor_date: itsrowsestimate is the doctor's March appointments only.FORCE INDEX (name)makes MySQL use one index, which is handy for comparing plans; avoid it in app code, because it stops the optimizer from adapting as data changes. - Forced to
idx_appt_date_doctor, the plan has to range over every March appointment of all 80 doctors, so itsrowsestimate is far bigger. Same columns, wrong order. Drop the loser.
Read type and key to see which index is used, key_len to see how many of its columns take part, rows for the estimated work, and Extra for sorts and covering reads.
A plan with a smaller rows and no Using filesort is usually the better one, but plans are estimates. When the choice matters, confirm it with EXPLAIN ANALYZE or rows examined, which measure what really happened.
Fixing queries that cannot use an index
A suitable index is not enough when the query hides the column. The script below fixes a query first, before adding any index.
Example 3 · Fix the query before adding an index
SET SESSION cte_max_recursion_depth = 40000;
CREATE TABLE appointments ( appointment_id INT PRIMARY KEY, doctor_id INT NOT NULL, patient_id INT NOT NULL, appt_date DATE NOT NULL, status VARCHAR(10) NOT NULL, fee DECIMAL(7,2) NOT NULL);
INSERT INTO appointmentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 40000)SELECT n, 1 + n % 80, 1 + (n * 13) % 8000, DATE '2026-01-01' + INTERVAL (n DIV 220) DAY, CASE WHEN (n DIV 3) % 10 < 7 THEN 'DONE' WHEN (n DIV 3) % 10 < 9 THEN 'BOOKED' ELSE 'CANCELLED' END, 300 + (n * 17) % 900FROM seq;
CREATE INDEX idx_appt_doctor_date ON appointments (doctor_id, appt_date);
-- Blocked: MONTH() hides appt_date, so only doctor_id helpsSELECT COUNT(*) AS march FROM appointmentsWHERE doctor_id = 17 AND MONTH(appt_date) = 3;SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Fixed: a half-open range on the bare columnSELECT COUNT(*) AS march FROM appointmentsWHERE doctor_id = 17 AND appt_date >= '2026-03-01' AND appt_date < '2026-04-01';SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
-- Blocked: patient_id has no index, so the OR forces a scanSELECT COUNT(*) AS visits FROM appointmentsWHERE doctor_id = 17 OR patient_id = 4321;SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
CREATE INDEX idx_appt_patient ON appointments (patient_id);
SELECT COUNT(*) AS visits FROM appointmentsWHERE doctor_id = 17 OR patient_id = 4321;SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;Output
+-------+ | march | +-------+ | 85 | +-------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 500 | 1 | +---------------+---------------+ +-------+ | march | +-------+ | 85 | +-------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 85 | 1 | +---------------+---------------+ +--------+ | visits | +--------+ | 505 | +--------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 40000 | 1 | +---------------+---------------+ +--------+ | visits | +--------+ | 505 | +--------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 505 | 1 | +---------------+---------------+
What this code does
- With
MONTH(appt_date), MySQL could still use thedoctor_idpart of the index, so it examined all 500 of Dr. 17's appointments. The range version used both columns and examined only the March ones. - The
ORscanned all 40,000 rows untilpatient_idgot an index; then MySQL merged two lookups. Notice the order of work: fix the query for free first, add an index only when a branch truly lacks one.
When a query has a suitable index and still scans, look for the usual blockers. A function or calculation on the column (MONTH(appt_date)), a leading % in LIKE, a text column compared with a number, or an OR with an unindexed branch.
Fix the query first, because that costs nothing: move the calculation to the constant side, turn date functions into half-open ranges, quote text values. Add or change an index only when the query is already written well.
Find the bug
Find the blocker
Click the line that stops MySQL using an index.
Which line hides order_date from an index on that column?
Round 1 of 3Mixed review: the index checklist
Use one checklist on any slow query: measure, plan, query, index. The script below filters and sorts from a single index.
Example 4 · Filter and sort from one index
SET SESSION cte_max_recursion_depth = 40000;
CREATE TABLE appointments ( appointment_id INT PRIMARY KEY, doctor_id INT NOT NULL, patient_id INT NOT NULL, appt_date DATE NOT NULL, status VARCHAR(10) NOT NULL, fee DECIMAL(7,2) NOT NULL);
INSERT INTO appointmentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 40000)SELECT n, 1 + n % 80, 1 + (n * 13) % 8000, DATE '2026-01-01' + INTERVAL (n DIV 220) DAY, CASE WHEN (n DIV 3) % 10 < 7 THEN 'DONE' WHEN (n DIV 3) % 10 < 9 THEN 'BOOKED' ELSE 'CANCELLED' END, 300 + (n * 17) % 900FROM seq;
-- The doctor's app: the 5 latest appointments of Dr. 42EXPLAIN ANALYZESELECT appointment_id, appt_date, status FROM appointmentsWHERE doctor_id = 42ORDER BY appt_date DESC, appointment_id DESCLIMIT 5\G
CREATE INDEX idx_appt_doctor_date ON appointments (doctor_id, appt_date);
EXPLAIN ANALYZESELECT appointment_id, appt_date, status FROM appointmentsWHERE doctor_id = 42ORDER BY appt_date DESC, appointment_id DESCLIMIT 5\GSample output
*************************** 1. row ***************************
EXPLAIN: -> Limit: 5 row(s) (cost=4000 rows=5) (actual time=24.5..24.5 rows=5 loops=1)
-> Sort: appointments.appt_date DESC, appointments.appointment_id DESC, limit input to 5 row(s) per chunk (cost=4000 rows=40000) (actual time=24.5..24.5 rows=5 loops=1)
-> Filter: (appointments.doctor_id = 42) (cost=4000 rows=40000) (actual time=0.872..24 rows=500 loops=1)
-> Table scan on appointments (cost=4000 rows=40000) (actual time=0.859..21.4 rows=40000 loops=1)
*************************** 1. row ***************************
EXPLAIN: -> Limit: 5 row(s) (cost=50.8 rows=5) (actual time=0.412..0.415 rows=5 loops=1)
-> Index lookup on appointments using idx_appt_doctor_date (doctor_id = 42) (reverse) (cost=50.8 rows=500) (actual time=0.41..0.413 rows=5 loops=1)What this code does
- Before: a table scan, a filter keeping Dr. 42's 500 rows, and a
Sortstep, all to return 5 rows. - After: an
Index lookup ... (reverse)that reads Dr. 42's entries from the newest date backwards and stops after 5. The secondary index also storesappointment_id(the primary key) afterappt_date, so even the tie-break order comes from the index.
- Measure: rows examined and time, or
EXPLAIN ANALYZE; find the slow step. - Plan:
EXPLAIN; look forALLon a big table,key = NULL, a hugerows,Using filesort. - Query: is every filtered column bare, with matching types, no leading
%, no unindexedORbranch? - Index: equality columns first, range last, sort column after equality; reuse existing indexes; drop redundant ones, because each index slows writes.
Module quiz
Questions from the whole module. Your first answer is your score.
Problems
Graded easy to hard, using everything from this module.
Try each one yourself first. Use the hint if you get stuck. Mark it as done when it works.
Exercise 1· Easy
On the ShopLite orders table, create the index that suits "all orders of customer 2, newest first", then show those orders and the rows examined.
Expected output
+----------+------------+-----------+ | order_id | order_date | status | +----------+------------+-----------+ | 15 | 2025-06-01 | PLACED | | 7 | 2025-03-15 | SHIPPED | | 2 | 2025-01-18 | DELIVERED | +----------+------------+-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 3 | 3 | +---------------+---------------+
Exercise 2· Easy
Build the appointments table from Example 1. Count Dr. 9's cancelled appointments and show rows examined; then add an index on (doctor_id, status) and show both again.
Expected output
+-----------+ | cancelled | +-----------+ | 167 | +-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 40000 | 1 | +---------------+---------------+ +-----------+ | cancelled | +-----------+ | 167 | +-----------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 167 | 1 | +---------------+---------------+
Exercise 3· Medium
Build the 30,000-row ipl_innings table below. A stats site asks for one player's runs in seasons 2016 to 2020. Show the answer and rows examined, add the right composite index, and show them again.
SELECT SUM(runs) AS runs, SUM(balls) AS ballsFROM ipl_inningsWHERE player_id = 77 AND season BETWEEN 2016 AND 2020;Expected output
+------+-------+ | runs | balls | +------+-------+ | 780 | 810 | +------+-------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 30000 | 1 | +---------------+---------------+ +------+-------+ | runs | balls | +------+-------+ | 780 | 810 | +------+-------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 15 | 1 | +---------------+---------------+
Exercise 4· Medium
On ipl_innings with an index on (team, season), compare the plans of "CSK innings in 2023" and "all innings in 2023". Which one uses the index, and why?
Sample output
+----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | practice.ipl_innings | analyze | status | OK | +----------------------+---------+----------+----------+ +----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+ | 1 | SIMPLE | ipl_innings | NULL | ref | idx_innings_team_season | idx_innings_team_season | 14 | const,const | 120 | 100.00 | Using index condition | +----+-------------+-------------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+ +----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | ipl_innings | NULL | ALL | NULL | NULL | NULL | NULL | 28528 | 10.00 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
Exercise 5· Medium
This query on the 50,000-row shop_orders table (with an index on order_date) scans everything. Rewrite it so the index is used, and show the result and rows examined for both versions.
SELECT COUNT(*) AS orders, SUM(amount) AS salesFROM shop_ordersWHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-02';Expected output
+--------+------------+ | orders | sales | +--------+------------+ | 7840 | 4300000.00 | +--------+------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50000 | 1 | +---------------+---------------+ +--------+------------+ | orders | sales | +--------+------------+ | 7840 | 4300000.00 | +--------+------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 7840 | 1 | +---------------+---------------+
Exercise 6· Medium
A cinema stores ticket codes as text. Build cinema_tickets (below) with an index on ticket_code, and find ticket 600123 with the value written as a number, then as text. Show rows examined for both.
CREATE TABLE cinema_tickets ( ticket_id INT PRIMARY KEY, ticket_code VARCHAR(10) NOT NULL, seat VARCHAR(4) NOT NULL);Expected output
+-----------+------+ | ticket_id | seat | +-----------+------+ | 123 | D4 | +-----------+------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 10000 | 1 | +---------------+---------------+ +-----------+------+ | ticket_id | seat | +-----------+------+ | 123 | D4 | +-----------+------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 1 | 1 | +---------------+---------------+
Exercise 7· Hard
Build appointments. The billing team runs "total fees of Dr. 30 per status in April" every hour. Create one index that makes this query covered (no table rows read), show the result, and show the plan's Extra with EXPLAIN.
SELECT status, COUNT(*) AS visits, SUM(fee) AS feesFROM appointmentsWHERE doctor_id = 30 AND appt_date >= '2026-04-01' AND appt_date < '2026-05-01'GROUP BY statusORDER BY status;Sample output
+-----------+--------+----------+ | status | visits | fees | +-----------+--------+----------+ | CANCELLED | 27 | 19971.00 | | DONE | 55 | 41775.00 | +-----------+--------+----------+ +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | appointments | NULL | range | idx_appt_billing | idx_appt_billing | 7 | NULL | 82 | 100.00 | Using where; Using index; Using temporary; Using filesort | +----+-------------+--------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------------------------------------------+
Exercise 8· Hard
Three queries run on ipl_innings all day. Create at most two indexes that serve all three, and prove it with rows examined for each query.
-- 1. A player's career runsSELECT SUM(runs) FROM ipl_innings WHERE player_id = 250;-- 2. A player's runs in one seasonSELECT SUM(runs) FROM ipl_innings WHERE player_id = 250 AND season = 2019;-- 3. A team's innings in one seasonSELECT COUNT(*) FROM ipl_innings WHERE team = 'RCB' AND season = 2016;Expected output
+-------------+ | career_runs | +-------------+ | 4650 | +-------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 50 | 1 | +---------------+---------------+ +-------------+ | season_runs | +-------------+ | 279 | +-------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 3 | 1 | +---------------+---------------+ +---------+ | innings | +---------+ | 180 | +---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 180 | 1 | +---------------+---------------+
Interview questions
Questions interviewers ask on this topic.
Finish up
Mark complete & continue
Next: Mini project: speed up ShopLite queries