MySQL Indexes

InnoDB implements primary and secondary indexes with B+ trees. An index reduces I/O when its order matches selective predicates, joins, range scans, or ordering requirements; it increases write work and storage for every indexed row.

Clustered and Secondary Indexes #

An InnoDB table stores row data in its clustered index. InnoDB selects the declared primary key; otherwise it selects the first suitable non-null unique key, or creates an internal hidden key. Declare a short, stable primary key because each secondary-index entry stores the primary-key value.

A secondary index stores its indexed columns and the primary-key value. A query that needs columns absent from the secondary index performs a clustered-index lookup for each matching entry. Add a covering index only when it removes a measured cost; wider indexes reduce page fan-out and increase write overhead.

Composite Indexes #

For (tenant_id, created_at, id), a B+ tree can efficiently locate:

WHERE tenant_id = ? AND created_at >= ?
ORDER BY created_at, id

It cannot efficiently seek from created_at alone because tree order begins with tenant_id. Equality predicates precede range predicates in a common design, followed by columns needed to satisfy ordering or coverage. Verify the choice with EXPLAIN ANALYZE; index order depends on actual predicates, data distribution, and result size.

Index Types and Diagnostics #

UNIQUE enforces an invariant as well as creating an access path. Most InnoDB indexes are B-trees. FULLTEXT indexes support token-based text search, and SPATIAL indexes over geometry values use R-trees. MySQL also describes hash indexes for the MEMORY engine; InnoDB does not expose a general hash index, although its adaptive hash index can cache frequently accessed B-tree pages as an internal optimization. Prefix indexes can reduce the byte size of long text keys, but only if the chosen prefix remains selective.

EXPLAIN ANALYZE
SELECT id, created_at
FROM event
WHERE tenant_id = 7
  AND created_at >= '2026-01-01'
ORDER BY created_at, id
LIMIT 100;

Inspect actual rows, loop counts, and timing rather than assuming an index is used. Statistics, cardinality estimates, and a small result set can make a table scan cheaper than an index traversal.

SHOW INDEX reports estimated cardinality, not an exact distinct-value count. Refresh stale statistics with ANALYZE TABLE when justified, then compare the optimizer estimate with EXPLAIN ANALYZE measurements. An invisible index lets administrators test plans as if an index were absent while InnoDB continues to maintain it:

ALTER TABLE event ALTER INDEX event_tenant_created INVISIBLE;
ALTER TABLE event ALTER INDEX event_tenant_created VISIBLE;

Do not drop the index until representative queries and constraint requirements have been checked. Every additional index consumes pages and adds insert, delete, update, redo-log, buffer-pool, backup, and replication work. Thus index selection must balance measured read benefit against write amplification and operational cost.

References #

• How MySQL Uses Indexes
• InnoDB Index Structures
• Invisible Indexes