What an index actually is

Key points

• InnoDB stores the table in the primary key - it is a clustered index, so the row data lives in the leaves of that B+ tree. There is no separate heap.
• That makes primary key choice consequential. A random UUID inserts into the middle of the tree, causing page splits and fragmentation, while an auto-increment or a time-ordered UUIDv7 always appends.
• Every secondary index stores the primary key as its pointer, so a wide primary key makes every secondary index wider. This is a second reason a 36-character UUID string is expensive.
• A composite index is ordered left to right, like a phone book sorted by surname then first name. An index on (a, b, c) serves lookups on a, on (a, b) and on (a, b, c) - never on b alone.
• A covering index contains every column the query needs, so the engine answers from the index without touching the row. EXPLAIN shows Using index, and it is often an order of magnitude faster.
• An index cannot be used if the column is wrapped in a function. WHERE DATE(created_at) = ... scans; rewriting it as a range over created_at uses the index.

Example

-- CLUSTERED: the row lives in the primary key tree.
--
--   PRIMARY KEY (id) B+ tree
--        [ 50 | 100 ]
--       /      |      \
--   [1..49] [50..99] [100..]      <- leaves hold the ENTIRE ROW
--
-- SECONDARY index on email:
--   leaf holds:  (email, id)      <- then a lookup INTO the tree above
--                                    unless the index covers the query

-- Column order is not cosmetic. This index:
CREATE INDEX idx_cust_status_date ON orders (customer_id, status, created_at);

--   USES the index:
--     WHERE customer_id = 5
--     WHERE customer_id = 5 AND status = 'PAID'
--     WHERE customer_id = 5 AND status = 'PAID' AND created_at > ...
--     WHERE customer_id = 5 ORDER BY status, created_at   (no filesort)
--
--   CANNOT use it:
--     WHERE status = 'PAID'                 <- skips the leftmost column
--     WHERE created_at > ...                <- same

-- COVERING: everything the query needs is in the index.
CREATE INDEX idx_covering ON orders (customer_id, status, total);
EXPLAIN SELECT status, total FROM orders WHERE customer_id = 5;
--   Extra: Using index          <- rows never read; index-only scan

-- A function on the column disables the index:
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-08-03';
--   type: ALL                   <- full scan

EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-08-03' AND created_at < '2026-08-04';
--   type: range                 <- index used

-- Or index the expression itself (MySQL 8.0.13+):
ALTER TABLE orders ADD INDEX idx_created_date ((DATE(created_at)));

-- Primary key choice, measured:
--   AUTO_INCREMENT BIGINT   8 bytes, always appends       best
--   UUIDv7 as BINARY(16)   16 bytes, time-ordered         good
--   UUIDv4 as CHAR(36)     36 bytes, random inserts       worst - splits
--                          pages AND bloats every secondary index

The table lives inside the primary key, secondary indexes point back through it, and a composite index only works left to right.