Covering Indexes

A covering index holds every column a query touches, so MySQL answers it from the index without reading a row.

SyntaxKEY idx (filter_col, sort_col, returned_col) -- EXPLAIN Extra: Using index

The manual's definition is blunt: an index that provides all the necessary results for a query is called a covering index. If every column the query reads — in the SELECT list, the WHERE, the ORDER BY, the GROUP BY — appears in one index, InnoDB answers the query from that index's leaf pages and never descends into the clustered index. EXPLAIN reports it in Extra as Using index.

What you get for free

InnoDB stores the primary key columns in every secondary index leaf, so the primary key is always available to a covering read without being named in the index definition. That is why SELECT id, ... so often stays covered.

Building one

MySQL has no INCLUDE clause for non-key payload columns. You widen the key itself: put the filter columns first, the sort column next, and the returned columns last. Those trailing columns do no narrowing, they just ride along. The cost is real — a wider index means fewer entries per page, a larger index, and more work on every write — so cover the two or three queries that matter, not all of them.

The lookalike

Do not read Using index condition as covering. That is Index Condition Pushdown: the storage engine evaluated part of the WHERE against index tuples before fetching rows, which saves reads but still reads rows. Only the bare string Using index means the table was never touched.

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,
  notes       TEXT            NULL,
  KEY idx_cust_placed (customer_id, placed_at)
) ENGINE=InnoDB;

-- NOT covered. total_cents is absent from idx_cust_placed, so each of the 50
-- index entries costs a second descent into the clustered index by id.
EXPLAIN SELECT placed_at, total_cents FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 50;
-- Extra: Backward index scan; Using index condition

-- Widen the key with the payload column. id needs no mention: InnoDB already
-- carries the primary key in every secondary index leaf.
ALTER TABLE orders
  ADD KEY idx_cust_placed_total (customer_id, placed_at, total_cents),
  ALGORITHM = INPLACE, LOCK = NONE;

-- Covered. The filter, the sort and both output columns all come from one
-- index; the table is never read.
EXPLAIN SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 50;
-- Extra: Backward index scan; Using index

ALTER TABLE orders DROP INDEX idx_cust_placed;   -- now a redundant prefix

When to use it

  • Making a high-traffic listing endpoint return without touching table rows.
  • Speeding up a COUNT or SUM report over a date range on a large table.
  • Paginating deep into a result set without reading the rows you skip.
  • Explaining why adding one column to a SELECT list tripled the query time.

More examples

A covering index behind a reporting aggregate

Aggregates are the best return on a covering index: the query reads a large contiguous run of entries and returns a handful of numbers, so avoiding one clustered-index descent per row is the whole cost of the report. Note that the date predicate is written as a half-open range on the bare column — wrapping placed_at in YEAR() would forfeit the index range and the covering read together.

Example · sql
-- status filters, placed_at ranges and groups, total_cents is the payload.
ALTER TABLE orders
  ADD KEY idx_status_placed_total (status, placed_at, total_cents),
  ALGORITHM = INPLACE, LOCK = NONE;

SELECT DATE_FORMAT(placed_at, '%Y-%m') AS month,
       COUNT(*)                        AS order_count,
       SUM(total_cents)                AS revenue_cents
FROM orders
WHERE status = 'paid'
  AND placed_at >= '2026-01-01'
  AND placed_at <  '2027-01-01'
GROUP BY month
ORDER BY month;
-- Extra: Using where; Using index

-- The same query with YEAR(placed_at) = 2026 loses both the range and the
-- covering read, because the index stores placed_at, not YEAR(placed_at).

Deferred join: covering the expensive half

When a listing page needs wide rows, you cannot cover the whole query. Split it: a covered subquery finds the primary keys for the page, reading only narrow index entries, and the outer query fetches full rows for just those keys. This is the standard fix for deep OFFSET pages, because the rows being skipped are index entries rather than full rows.

Example · sql
-- idx_cust_placed (customer_id, placed_at) covers the inner query: it filters
-- on customer_id, orders by placed_at, and returns id, which InnoDB already
-- stores in every leaf entry.
SELECT o.id, o.status, o.placed_at, o.total_cents, o.notes
FROM orders AS o
JOIN (
  SELECT id
  FROM orders
  WHERE customer_id = 42
  ORDER BY placed_at DESC
  LIMIT 20 OFFSET 10000
) AS page ON page.id = o.id
ORDER BY o.placed_at DESC;

-- The 10,000 skipped rows are now 10,000 index entries, not 10,000 full rows
-- including the TEXT column. The outer join does exactly 20 primary key
-- lookups (EXPLAIN shows type: eq_ref on o).

SELECT * cannot be covered

Any column outside the index breaks the covering read, and SELECT * guarantees at least one — here the TEXT column, which can also be stored off-page. This is the cheapest optimization available on a listing endpoint: name the columns you actually render. It also makes the query stable when someone later adds a column to the table.

Example · sql
-- KEY idx_cust_placed_total (customer_id, placed_at, total_cents)

-- Never covered: notes is not in any index, and TEXT may live off-page,
-- costing an extra read per row for data the caller discards.
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 20;
-- Extra: Backward index scan; Using index condition

-- Covered: same rows, same order, no table access.
EXPLAIN SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 20;
-- Extra: Backward index scan; Using index

-- Verify the claim rather than trusting the plan, by comparing measured rows
-- and time for the two forms.
EXPLAIN ANALYZE SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 20;

Related

sql index introsql composite indexsql explainsql slow queriessql limit offset

Discussion

  • Be the first to comment on this lesson.