MySQL Indexes Explained — B-Tree, Composite, and EXPLAIN

What Is a MySQL Index?

A MySQL index is a separate, sorted data structure that stores a copy of one or more column values alongside a pointer back to the full row. Instead of reading every row to find a match, the storage engine walks the much smaller, sorted index structure and jumps straight to the matching entries.

InnoDB, MySQL's default storage engine, implements every index as a B-tree. The MySQL 8.0 Reference Manual notes that a B-tree index traversal on a table with roughly a million rows typically takes only 2-3 page reads, compared with reading every single row in sequence. That gap is the entire reason indexes exist.

Without an index, MySQL has exactly one option for finding matching rows: read the table from start to end and check every row against the condition. That's a full table scan, and it's the default behaviour for any column without an index.

How B-Tree Indexes Work

A B-tree keeps its structure shallow by packing many keys into each 16 KB page and using high fanout — every internal page can point to dozens of child pages. That's why the tree stays only 3-4 levels deep even at millions of rows, and why a lookup costs a handful of page reads rather than scaling linearly with table size.

In InnoDB specifically, the primary key is the table — data rows live directly in the primary key's B-tree, a design called a clustered index. Every secondary index (any index other than the primary key) stores the indexed column plus the primary key value, then does a second lookup into the clustered index to fetch the rest of the row. That's why a secondary index lookup is technically two B-tree traversals, not one, though both are still fast.

The chart above isn't to scale — the indexed bar would be invisible if it were — but it makes the point: an index turns an O(n) scan into something close to O(log n). On small tables the difference doesn't matter. On a table with millions of rows, it's the difference between a query that returns instantly and one that times out.

Creating and Managing Indexes

MySQL gives you three equivalent ways to add an index: inline during CREATE TABLE, standalone with CREATE INDEX, or appended later with ALTER TABLE. All three produce the same on-disk structure.

Inline during table creation:

CREATE TABLE orders (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    INT UNSIGNED NOT NULL,
    status     VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status)
);

Standalone, on an existing table:

CREATE INDEX idx_orders_created_at ON orders (created_at);

Added with ALTER TABLE:

ALTER TABLE orders ADD INDEX idx_orders_status (status);

To enforce uniqueness alongside the lookup speedup, use UNIQUE:

CREATE UNIQUE INDEX idx_users_email ON users (email);

Name your indexes with a consistent pattern, such as idx_table_column. It keeps SHOW INDEX FROM table_name readable and makes dropping a specific index straightforward: DROP INDEX idx_orders_status ON orders;. This mirrors the same naming discipline covered in the foreign key guide for constraint names.

MySQL: INDEXES are awesome — Bro Code (YouTube, 2022)

Composite Indexes and the Leftmost Prefix Rule

A composite index spans multiple columns, sorted first by the first column, then by the second within each group of matching first-column values, and so on. INDEX idx_orders_lookup (user_id, status, created_at) sorts first by user_id, then by status within each user, then by created_at within each status.

CREATE INDEX idx_orders_lookup ON orders (user_id, status, created_at);

That physical ordering is why the leftmost prefix rule exists. MySQL can use this index for a query filtering on user_id alone, on user_id and status, or on all three columns — because each of those is a contiguous slice of the sorted structure. It can't use the index for a query filtering on status alone, or on created_at alone, because neither is a leftmost prefix.

The practical implication is that column order in a composite index is a design decision, not an afterthought. Put the column your application filters on most often — usually a foreign key like user_id — first, and reserve the rightmost position for the column that benefits most from range queries, like a timestamp, since range conditions stop the leftmost-prefix matching at that point.

When MySQL Ignores Your Index

An index existing doesn't guarantee MySQL uses it. The query optimizer estimates the cost of each available access path and picks the cheapest one — and sometimes that's a full table scan, even with a matching index in place. Why would the optimizer skip a structure built specifically to speed up the query?

When the optimizer estimates a query will touch a large percentage of a table's rows, a table scan's sequential reads often beat an index's random reads, since random I/O is far more expensive per page than sequential I/O on spinning disks and still carries overhead on SSDs. Percona's benchmarking found that in a disk-bound scenario where the working set didn't fit in memory, a full table scan completed in about 4 seconds while a full index scan on the same query took about 30 seconds — the opposite of what most developers expect.

This is the benchmark result that surprises developers building a schema visually for the first time: adding an index isn't always a free win. Random reads scattered across a disk-bound table can cost far more than reading the whole table in sequence, so index design has to account for row count, memory, and how selective the condition actually is — not just "does a matching column have an index."

The optimizer also skips indexes on very small tables — typically under about ten rows — since a direct scan is cheaper than the overhead of a B-tree traversal. And as covered above, it ignores a composite index entirely once a query's filter conditions fall outside the leftmost prefix.

Reading EXPLAIN Output

EXPLAIN shows the execution plan MySQL chose for a query, including which index (if any) it used. Run it by prefixing any SELECT:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

The key column tells you which index MySQL actually chose — NULL means no index was used, which is your signal to investigate. The rows column estimates how many rows MySQL expects to examine; a number close to the table's total row count on a query that should be selective is a red flag. Extra showing Using filesort or Using temporary often points to a missing index on an ORDER BY or GROUP BY column.

For a deeper look at actual (not estimated) execution timing, EXPLAIN ANALYZE (available since MySQL 8.0.18) runs the query and reports real cost per plan node rather than the optimizer's estimate:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

To find indexes that exist but are never used — pure write overhead with no read benefit — query MySQL 8.0's built-in sys schema:

SELECT * FROM sys.schema_unused_indexes;

This view surfaces indexes with zero recorded reads in Performance Schema since the last restart or stats reset. Confirm over a reasonably long observation window before dropping anything — a monthly report query still needs its index even if it hasn't run in the last hour.

Common Mistakes

• Indexing every column "just in case." Every index MySQL must update on every INSERT, UPDATE, and DELETE. Add indexes to match queries you've actually observed via EXPLAIN or the slow query log, not speculatively.
• Wrong column order in a composite index. An index on (status, user_id) is nearly useless for a query that filters mostly on user_id, since user_id isn't the leftmost column. Match the order to your most common filter pattern.
• Functions wrapped around indexed columns. WHERE YEAR(created_at) = 2026 can't use an index on created_at, because MySQL must evaluate the function per row before it can compare. Rewrite as a range: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.
• Ignoring cardinality. An index on a low-cardinality column like a boolean is_active flag rarely helps, since it can't narrow the row set enough to beat a table scan. Reserve indexes for columns with many distinct values.
• Never revisiting indexes after schema changes. Indexes that made sense a year ago can become dead weight once query patterns shift. Periodically check sys.schema_unused_indexes as part of routine maintenance, the same way you'd review foreign key constraints after a schema refactor.

Visualise Indexes Before Writing DDL

Index planning is easier once the schema's query paths are visible. A diagram makes it obvious which foreign key columns need composite indexes, and which lookup columns are worth indexing before you've written a single CREATE INDEX statement.

You can design your schema visually and export the correct MySQL DDL, including primary keys and foreign key indexes, directly from the diagram. The crow's foot notation guide explains how to read the relationship lines that typically indicate where a foreign key index belongs, and the database schema examples post shows complete indexed schemas across common application types.