MENU


Index Strategy

Index strategy is the practice of shaping indexes so the optimizer can find the shortest path to matching rows – and will actually choose to. See Indexes for the CREATE INDEX syntax and index types this page assumes.

Composite Index Column Order

A composite (multi-column) index is usable only for a leftmost prefix of its columns, taken in the order they were declared. Given:

CREATE TABLE orders (
  id         BIGINT UNSIGNED PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      VARCHAR(20) NOT NULL,
  created_at  DATETIME NOT NULL,
  KEY idx_cust_status_date (customer_id, status, created_at)
);

The index is usable for predicates on (customer_id), (customer_id, status), and (customer_id, status, created_at) – but not for a query that filters on status or created_at alone, since neither is a leftmost prefix.


A practical rule of thumb for ordering columns: equality columns first (in any order among themselves), then at most one range or sort column last. A range predicate (<, >, BETWEEN, LIKE 'prefix%') stops the index scan from narrowing any further on subsequent columns, so putting a range column before an equality column wastes the equality column's selectivity.

Covering Indexes

A covering index contains every column a query needs – in the SELECT list, the WHERE clause, and any ORDER BY/GROUP BY – so the storage engine never has to look up the full row. EXPLAIN reports this as Using index in the Extra column (see EXPLAIN and Execution Plans). Extending idx_cust_status_date to include id makes it covering for a common lookup:

ALTER TABLE orders DROP INDEX idx_cust_status_date,
  ADD INDEX idx_cust_status_date_cov (customer_id, status, created_at, id);

SELECT id, created_at FROM orders WHERE customer_id = 42 AND status = 'SHIPPED'; now reads only the index – no table access at all.


Covering indexes trade write cost and storage for read speed – each added column widens every index entry and must be maintained on every INSERT/UPDATE/DELETE, so reserve them for hot, frequently-run queries rather than adding them speculatively (see Common Anti-Patterns on over-indexing).

Cardinality and Selectivity

SHOW INDEX FROM tbl_name; reports a Cardinality column – the optimizer's estimate of distinct values in that index (or index prefix). Selectivity is cardinality divided by row count; a value close to 1 (every row distinct, e.g. an email column) is highly selective and a good candidate for a standalone index, while a value close to 0 (a boolean flag, a status column with three values) is poorly selective and rarely worth indexing on its own – a full scan can be cheaper than the extra index lookups. Cardinality is an estimate refreshed by ANALYZE TABLE or automatically as the table changes; stale statistics after bulk loads are a common cause of the optimizer picking a worse plan than expected.

When the Optimizer Ignores an Index

An index that exists is not guaranteed to be used. Common reasons the optimizer bypasses one:

Force the issue for testing with index hints: FORCE INDEX (idx_name), USE INDEX (idx_name), or IGNORE INDEX (idx_name) appended after the table name. Hints are useful for diagnosis but brittle in application code – a schema change can silently make one wrong.

Functional Indexes and Generated Columns

MySQL 8.0.13+ supports functional key parts – an index on an expression rather than a bare column:

CREATE INDEX idx_order_year ON orders ((YEAR(created_at)));

The extra parentheses mark the key part as an expression. A query filtering on YEAR(created_at) = 2024 can now use this index directly.


The equivalent, more portable approach is a generated column plus an ordinary index on it – useful for engines or MySQL versions without functional key parts, and for indexing JSON document fields:

ALTER TABLE orders ADD COLUMN order_year INT AS (YEAR(created_at)) STORED,
  ADD INDEX idx_order_year2 (order_year);

A STORED generated column is materialized and indexed like any other column; VIRTUAL (the default) computes on read but can still be indexed since MySQL 8.0.


Either form lets a query written against the original expression use an index instead of a full scan – without it, WHERE YEAR(created_at) = 2024 can never be sargable no matter what plain index exists on created_at.

For choosing which indexes are actually paying for themselves in a running system, see Performance Schema and Sys Schema. 

CREATE TABLE orders (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  status       VARCHAR(20) NOT NULL,
  created_at   DATETIME NOT NULL,
  KEY idx_cust_status_date (customer_id, status, created_at)
) ENGINE=InnoDB;

-- Leftmost prefix: usable
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'SHIPPED';

-- Not a leftmost prefix: idx_cust_status_date cannot be used
EXPLAIN SELECT * FROM orders WHERE status = 'SHIPPED';

-- Widen to a covering index for a hot lookup
ALTER TABLE orders DROP INDEX idx_cust_status_date,
  ADD INDEX idx_cust_status_date_cov (customer_id, status, created_at, id);

EXPLAIN SELECT id, created_at
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';
-- Extra: Using index -- no row lookup needed

-- Cardinality / selectivity
ANALYZE TABLE orders;
SHOW INDEX FROM orders;

-- Functional index on an expression (MySQL 8.0.13+)
CREATE INDEX idx_order_year ON orders ((YEAR(created_at)));
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Equivalent via a stored generated column
ALTER TABLE orders ADD COLUMN order_year INT AS (YEAR(created_at)) STORED,
  ADD INDEX idx_order_year2 (order_year);