MySQL
Composite indexes, the leftmost prefix and EXPLAIN
How MySQL uses a multi-column index: equality columns from the left, at most one range, ORDER BY for free, and covering. Read it all from EXPLAIN.
After this lesson you can
- Predict which key parts of a composite index a WHERE clause can use
- Order index columns so one index serves filtering and sorting
- Read type, key, key_len, rows and Extra in EXPLAIN output
- Recognise a covering index and a filesort in a plan
A composite index on (customer_id, status, created_at) is one B+tree
sorted by customer_id, then by status within each customer, then by
created_at within each status. Everything in this lesson follows from
that sort order. The examples run on 4,000 orders for 400 customers, ten
orders each.
The leftmost prefix rule
The index can be searched (a seek, not a scan) using:
- equality conditions on its columns from the left, with no gaps;
- then at most one range condition (
>,<,BETWEEN,LIKE 'ab%') on the next column.
Anything after a gap or after the range column cannot narrow the search. It may still be checked against index entries before the row is read (MySQL calls that index condition pushdown), but it does not decide where the search starts and stops.
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, created_at DATETIME NOT NULL, amount INT NOT NULL, KEY idx_cust_status_created (customer_id, status, created_at));SET SESSION cte_max_recursion_depth = 5000;INSERT INTO orders (customer_id, status, created_at, amount)WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)SELECT n % 400, ELT(1 + (n DIV 400) % 4, 'paid', 'new', 'shipped', 'refunded'), '2026-01-01' + INTERVAL n HOUR, n % 97FROM s;ANALYZE TABLE orders;-- Helper: show_plan(query) stores EXPLAIN FORMAT=JSON; plan_summary picks out the fields.CREATE TABLE plans (n INT AUTO_INCREMENT PRIMARY KEY, query TEXT, plan JSON);CREATE PROCEDURE show_plan(IN q TEXT)BEGIN SET @sql = CONCAT('EXPLAIN FORMAT=JSON INTO @p ', q); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; INSERT INTO plans (query, plan) VALUES (q, @p);END;CREATE VIEW plan_summary ASSELECT n, query, JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.access_type'), '$[0]')) AS access_type, JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.key'), '$[0]')) AS index_used, CAST(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.used_key_parts'), '$[0]') AS CHAR) AS key_parts, IF(JSON_CONTAINS(JSON_EXTRACT(plan, '$**.using_index'), 'true'), 'yes', 'no') AS index_only, IF(JSON_CONTAINS(JSON_EXTRACT(plan, '$**.using_filesort'), 'true'), 'yes', 'no') AS filesortFROM plans;CALL show_plan('SELECT * FROM orders WHERE customer_id = 7');CALL show_plan('SELECT * FROM orders WHERE customer_id = 7 AND status = ''paid''');CALL show_plan('SELECT * FROM orders WHERE status = ''paid''');CALL show_plan('SELECT * FROM orders WHERE customer_id = 7 AND created_at >= ''2026-03-01'''); SELECT n, query, access_type, index_used, key_partsFROM plan_summaryORDER BY n;What to look for
customer_id = 7uses one key part; addingstatus = 'paid'uses two.status = 'paid'alone cannot seek: the index is not sorted by status, so the table is scanned (ALL). (Since 8.0.13 the optimizer has one fallback, the skip scan: when a query needs only index columns and the leading column has few distinct values, it can run one search per leading value, shown asUsing index for skip scan. Do not design an index around it.)customer_id = 7 AND created_at >= ...skipsstatus, so onlycustomer_idis used to seek.created_atis checked afterwards.
access_type is EXPLAIN's type column. From best to worst for a single
table: const (one row by primary or unique key), eq_ref, ref
(equality on a non-unique prefix), range, index (read the whole index),
ALL (read the whole table).
Sorting and covering
Because entries are sorted by created_at within each pair, the index can also deliver rows in order.
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, created_at DATETIME NOT NULL, amount INT NOT NULL, KEY idx_cust_status_created (customer_id, status, created_at));SET SESSION cte_max_recursion_depth = 5000;INSERT INTO orders (customer_id, status, created_at, amount)WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)SELECT n % 400, ELT(1 + (n DIV 400) % 4, 'paid', 'new', 'shipped', 'refunded'), '2026-01-01' + INTERVAL n HOUR, n % 97FROM s;ANALYZE TABLE orders;-- Helper: show_plan(query) stores EXPLAIN FORMAT=JSON; plan_summary picks out the fields.CREATE TABLE plans (n INT AUTO_INCREMENT PRIMARY KEY, query TEXT, plan JSON);CREATE PROCEDURE show_plan(IN q TEXT)BEGIN SET @sql = CONCAT('EXPLAIN FORMAT=JSON INTO @p ', q); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; INSERT INTO plans (query, plan) VALUES (q, @p);END;CREATE VIEW plan_summary ASSELECT n, query, JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.access_type'), '$[0]')) AS access_type, JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.key'), '$[0]')) AS index_used, CAST(JSON_EXTRACT(JSON_EXTRACT(plan, '$**.used_key_parts'), '$[0]') AS CHAR) AS key_parts, IF(JSON_CONTAINS(JSON_EXTRACT(plan, '$**.using_index'), 'true'), 'yes', 'no') AS index_only, IF(JSON_CONTAINS(JSON_EXTRACT(plan, '$**.using_filesort'), 'true'), 'yes', 'no') AS filesortFROM plans;CALL show_plan('SELECT * FROM orders WHERE customer_id = 7 AND status = ''paid'' ORDER BY created_at');CALL show_plan('SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at');CALL show_plan('SELECT id, status, created_at FROM orders WHERE customer_id = 7 AND status = ''paid''');CALL show_plan('SELECT id, amount FROM orders WHERE customer_id = 7 AND status = ''paid'''); SELECT n, query, key_parts, filesort, index_onlyFROM plan_summaryORDER BY n;What to look for
- With
customer_idandstatusboth fixed,ORDER BY created_atreads the index in order and needs no sort. - With only
customer_idfixed, rows come out ordered by status first, so MySQL must sort them: filesort (an in-memory or on-disk sort, despite the name). SELECT id, status, created_atis answered from the index alone (covering;idis the primary key, which every InnoDB secondary index carries). Asking foramountrequires a lookup into the table per row.
A practical ordering rule follows: equality columns first, then the column you sort or range on.
Reading classic EXPLAIN
Plain EXPLAIN returns one row per table in the plan.
CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, created_at DATETIME NOT NULL, amount INT NOT NULL, KEY idx_cust_status_created (customer_id, status, created_at));SET SESSION cte_max_recursion_depth = 5000;INSERT INTO orders (customer_id, status, created_at, amount)WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)SELECT n % 400, ELT(1 + (n DIV 400) % 4, 'paid', 'new', 'shipped', 'refunded'), '2026-01-01' + INTERVAL n HOUR, n % 97FROM s;ANALYZE TABLE orders;EXPLAINSELECT * FROM ordersWHERE customer_id = 7 AND created_at >= '2026-03-01';What to look for
typerefandkeyshow the index search;refconstsays it is compared with a constant.key_len4 is the bytes of the index actually used: oneINT,customer_id, which confirmsstatusandcreated_atwere not used to seek.rowsis the optimizer's estimate of rows to examine (here exact, 10), andfilteredits estimate of the percentage left after the remaining conditions.ExtraUsing index conditionis the pushdown described above. Other values to know:Using index(covering),Using filesort,Using temporary,Using where.
Common mistakes
- Putting a range column before an equality column. An index on
(created_at, customer_id)cannot seek on once is a range.
Exercise · Pro
Which ORDER BY can the index deliver?
Exercises are free in the first 3 lessons of every course. From here on they are part of Pro and Sprint; the lesson and its runnable examples stay open.
Upgrade