Composite Indexes and Column Order

A composite index is usable only from its leftmost column inward, so the column order decides which queries it can serve.

SyntaxKEY idx (a, b, c) -- usable prefixes: (a), (a, b), (a, b, c)

A composite (multiple-column) index stores its key parts concatenated in the declared order, and MySQL can use it only for a leftmost prefix of that order. An index on (a, b, c) serves lookups on (a), (a, b) and (a, b, c). It does nothing for a query that filters only on b, or only on c. One well-ordered composite index therefore replaces three single-column indexes; a badly ordered one replaces none of them. An index may contain up to 16 columns.

Equality first, then the range

The optimizer walks the key parts in order using =, <=> and IS NULL, and stops narrowing as soon as it hits >, <, >=, <=, BETWEEN, != or a LIKE with a trailing wildcard. Everything after that key part is only rechecked per row, not used to narrow the scan. So put every equality column first and the range or sort column last. This single rule accounts for most composite indexes that exist but are not helping.

Escape hatches

Skip Scan (MySQL 8.0.13 and later) can sometimes rescue a missing leading column by stepping over each distinct value of it and range-scanning underneath, shown as Using index for skip scan. It requires a low-cardinality leading column and a query that touches only indexed columns. Treat it as luck, not design.

Example

Example · sql
CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  status      VARCHAR(20)     NOT NULL,
  placed_at   DATETIME        NOT NULL,
  total_cents INT             NOT NULL,
  -- Two equality columns first, the range/sort column last.
  KEY idx_cust_status_placed (customer_id, status, placed_at)
) ENGINE=InnoDB;

-- Uses all three key parts: two equalities, then a range on the last one.
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'shipped' AND placed_at >= '2026-01-01';

-- Uses (customer_id, status). A leftmost prefix is a first-class use.
SELECT id FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- Uses (customer_id) only. status is skipped, so placed_at cannot narrow
-- anything either; every order for customer 42 is read and rechecked.
SELECT id FROM orders WHERE customer_id = 42 AND placed_at >= '2026-01-01';

-- Cannot use idx_cust_status_placed at all: status is not a leftmost prefix.
-- EXPLAIN shows type: ALL.
SELECT id FROM orders WHERE status = 'shipped';

When to use it

  • Replacing three single-column indexes with one composite index that serves them all.
  • Fixing a query that uses an index but still reads far more rows than it returns.
  • Designing the index behind a "most recent N rows for this customer" screen.
  • Deciding where a date-range predicate belongs in the key part order.

More examples

The same index serving the ORDER BY

A composite index is stored in order, so after the equality columns pin a section of the tree, the trailing key part is already sorted. That removes "Using filesort" and lets LIMIT stop early instead of sorting the whole result. Reach for this on any "latest N for this entity" screen — the ordering column belongs at the end of the index, not in an index of its own.

Example · sql
-- KEY idx_cust_status_placed (customer_id, status, placed_at)

-- Filter and order both come from the index. Extra has no "Using filesort",
-- and LIMIT 20 stops after 20 leaf entries.
SELECT id, placed_at FROM orders
WHERE customer_id = 42 AND status = 'shipped'
ORDER BY placed_at DESC
LIMIT 20;
-- A single-column DESC sort over an ASC index is fine: InnoDB walks the
-- leaves the other way, shown in Extra as "Backward index scan".

-- Mixed directions are different: no single-direction index supplies this
-- order, so you get a filesort unless the key parts match.
SELECT id FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC, id ASC;

ALTER TABLE orders ADD KEY idx_cust_feed (customer_id, placed_at DESC, id ASC);

A range in the middle wastes the rest of the index

This is the most common composite-index defect: a date range sits between the equality columns, so every key part after it is dead weight. The fix is a reorder, not a new column. Check key_len in EXPLAIN to confirm — it tells you how many bytes of the key the optimizer actually used, and therefore how many key parts survived.

Example · sql
-- Wrong order: the range column sits before an equality column.
ALTER TABLE orders ADD KEY idx_bad (customer_id, placed_at, status);

-- placed_at is a range, so status never narrows anything. Every order for
-- customer 42 in the whole date window is read and filtered row by row.
EXPLAIN SELECT id FROM orders
WHERE customer_id = 42
  AND placed_at >= '2026-01-01'
  AND status = 'shipped';
-- key: idx_bad   key_len: 14   (customer_id + placed_at only)

-- Right order: both equalities first, range last.
ALTER TABLE orders ADD KEY idx_good (customer_id, status, placed_at);

EXPLAIN SELECT id FROM orders
WHERE customer_id = 42
  AND placed_at >= '2026-01-01'
  AND status = 'shipped';
-- key: idx_good  key_len: 96   (all three key parts used)

ALTER TABLE orders DROP INDEX idx_bad;

Skip Scan, and why not to rely on it

MySQL 8.0.13 added Skip Scan, which can use a composite index even when the leading column has no predicate, by iterating its distinct values and range-scanning under each. It only applies to a single-table query that references columns in the index and has no GROUP BY or DISTINCT, and it only pays off when the leading column has very few distinct values. Use it to explain a surprising plan, not to justify skipping a proper index.

Example · sql
CREATE TABLE subscriptions (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_id  TINYINT UNSIGNED NOT NULL,   -- only a handful of distinct values
  seats    INT              NOT NULL,
  KEY idx_plan_seats (plan_id, seats)
) ENGINE=InnoDB;

-- No predicate on plan_id, yet the index is still usable: MySQL steps over
-- each distinct plan_id and range-scans seats beneath it.
EXPLAIN SELECT plan_id, seats FROM subscriptions WHERE seats > 500;
-- Extra: Using where; Using index for skip scan

-- Adding a non-indexed column to the select list disqualifies it: the query
-- must reference only columns in the index.
EXPLAIN SELECT id, plan_id, seats FROM subscriptions WHERE seats > 500;

-- The dependable answer is an index that leads with the filtered column.
ALTER TABLE subscriptions ADD KEY idx_seats (seats);

Related

sql create indexsql covering indexsql explainsql sargablesql order by

Discussion

  • Be the first to comment on this lesson.