MySQL Indexes

InnoDB indexes are B-trees. The primary key is the table itself; every secondary index stores the primary key value as its row pointer.

Composite index order

Equality columns first, then the range column, then the sort column:

-- SELECT ... WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20
ALTER TABLE orders
  ADD KEY orders_customer_status_created (customer_id, status, created_at DESC);

The leftmost-prefix rule applies: (a, b, c) serves a, a,b and a,b,c — not b alone. Descending index support is real in MySQL 8.0; in earlier versions a DESC keyword was accepted and ignored.

Covering indexes

When every column the query needs is in the index, InnoDB never touches the clustered index. EXPLAIN shows Using index.

ALTER TABLE orders
  ADD KEY orders_cover (customer_id, created_at, status, total_cents);

This matters more in InnoDB than elsewhere, because the alternative is a second B-tree traversal per row.

Prefix indexes

For long text columns you can index a prefix:

ALTER TABLE articles ADD KEY articles_title_prefix (title(64));

The trade: a prefix index cannot be used for covering reads or for ORDER BY on that column. Choose the length from measured selectivity rather than a round number:

SELECT
  COUNT(DISTINCT LEFT(title, 16)) / COUNT(*) AS sel_16,
  COUNT(DISTINCT LEFT(title, 32)) / COUNT(*) AS sel_32,
  COUNT(DISTINCT LEFT(title, 64)) / COUNT(*) AS sel_64
FROM articles;

Pick the shortest prefix whose selectivity approaches that of the full column.

Functional and multi-valued indexes

MySQL 8.0.13+ supports functional key parts; the query must use the identical expression:

ALTER TABLE users ADD KEY users_lower_email ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'a@example.com';

Multi-valued indexes index arrays inside JSON columns and are used with MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS.

Finding index problems

-- Indexes that have never been used since the last server start.
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql','performance_schema','sys')
ORDER BY object_schema, object_name;
 
-- Redundant and duplicate indexes.
SELECT * FROM sys.schema_redundant_indexes;

sys.schema_redundant_indexes finds indexes that are a prefix of another — pure write cost with no read benefit.