MySQL Indexes: How They Work and How to Use Them
If you've ever seen a SELECT query that used to return instantly suddenly slow down as a table grew, you're not alone. In most cases, the cause is a missing index on the column used in the search condition. An index is a data structure that, much like the index at the back of a book, keeps track of where each value lives so you can jump straight to the row you need instead of scanning the whole table. This article walks through how MySQL indexes work under the hood, how to create them, how column order matters for composite indexes, how to verify their effect with the EXPLAIN statement, and the situations where an index quietly stops being used — the kind of details that tend to trip people up in practice.
What Is an Index?
An index is a data structure that speeds up searches by mapping the values in a specific column (or combination of columns) to the location of the rows that hold them. Without an index, the database has no choice but to check every row from the first to the last to find the ones that match a given condition. This is called a full table scan, and the time it takes grows in proportion to the number of rows in the table.
With an index in place, the database can narrow down matching values efficiently from the index itself and then access only the rows it actually needs. On tables with millions of rows, it's not unusual for an index to make the difference between a query that takes milliseconds and one that takes seconds. That said, indexes aren't free — they add write overhead and disk usage — so choosing which columns to index is worth thinking through carefully.
How MySQL's B-Tree Indexes Work
InnoDB, the storage engine MySQL uses most often, implements its indexes using a data structure called a B-tree (short for balanced tree). A B-tree branches out from a root node down to leaf nodes, and it's built so that every leaf sits at roughly the same depth. That's what keeps lookups fast even as a table grows: the number of steps needed to reach a given value increases only gradually as the row count goes up.
Each node in a B-tree stores a range of values along with pointers to the child nodes that correspond to those ranges. A search starts at the root, picks the child whose range contains the target value, and repeats that process node by node until it reaches the leaf node holding the actual data. This structure isn't limited to equality (=) lookups — it also handles range conditions (BETWEEN, <, >, and so on) and ORDER BY sorting efficiently.
InnoDB tables also have something called a clustered index, where the row data itself is stored in B-tree form, keyed by the primary key. Any index you create on a column other than the primary key — known as a secondary index — is managed as a separate B-tree that holds only that column's value alongside the primary key value. Fetching the actual row data through a secondary index means looking up the clustered index a second time using that primary key. This two-step lookup is an important detail to keep in mind, since it's the whole reason covering indexes (discussed later) matter.
https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html
Creating an Index
There are two ways to add an index: specify it when you create the table, or add it to an existing table afterward. To add one to an existing table, use the CREATE INDEX statement.
CREATE INDEX idx_users_email ON users (email);
Creating an index on a single column like this is called a single-column index. There's no strict naming convention, but a name like idx_table_column that makes it obvious which table and column the index belongs to makes life easier when you come back to review it later.
Once an index is no longer needed, you can remove it with DROP INDEX. For example, here's how you'd drop an index you created on the prefecture column.
CREATE INDEX idx_users_prefecture ON users (prefecture);
DROP INDEX idx_users_prefecture ON users;
Adding or dropping an index can take a while on tables with a lot of rows. InnoDB supports online index operations in most cases, but it's still worth estimating the table size and expected runtime before running one in production.
https://dev.mysql.com/doc/refman/8.4/en/create-index.html
Composite Indexes and Column Order
You can also combine multiple columns into a single index — this is called a composite index (or multi-column index). For example, if you frequently search by a user's prefecture and signup date together, you'd index both columns like this.
CREATE INDEX idx_users_prefecture_created_at ON users (prefecture, created_at);
The most important decision when building a composite index is column order. A composite B-tree index is sorted by its first column, and within each value of that first column, it's sorted again by the next column — a nested structure. Because of this, the index above helps queries that filter on prefecture alone, or on both prefecture and created_at together, but it doesn't help a query that filters on created_at alone. This behavior is known as the leftmost prefix rule.
The general approach to designing a composite index is to base the column order on which combinations of columns actually show up together in your search conditions, weighted by how often each one is used. The table below shows which search conditions this particular index can and can't help with, depending on column order.
| Search condition | With (prefecture, created_at) |
|---|---|
WHERE prefecture = ? | Can use the index |
WHERE prefecture = ? AND created_at = ? | Can use the index |
WHERE created_at = ? only | Can't use the index |
You'll sometimes see advice to put the column with higher cardinality (the one with more distinct values) first. In practice, though, it's usually more effective to base the order on which column combinations your queries actually use most often.
https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html
Checking an Index's Impact with EXPLAIN
Once you've created an index, the EXPLAIN statement is how you check whether a query is actually using it. Put EXPLAIN in front of a SELECT statement, and MySQL shows you the execution plan it intends to use instead of running the query.
EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_users_email | idx_users_email | 1022 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
The output includes several columns, but a handful are worth paying particular attention to.
| Column | Meaning |
|---|---|
type | The access method. ALL means a full table scan, while ref or range mean an index is being used |
possible_keys | The indexes MySQL could potentially use |
key | The index MySQL actually chose |
rows | The estimated number of rows the plan will scan |
Extra | Additional details. Using index here means the query is satisfied entirely from the index, without touching the table itself |
If type shows ALL, that means no index is being used and a full table scan is happening — it's the first thing to check when you want to confirm whether an index you expected to be used is actually being used. A rows value that's higher than you expected can also be a sign that, even though an index was chosen, it isn't narrowing things down very effectively.
MySQL 8.0 and later also offer EXPLAIN FORMAT=TREE, which renders the output as a more human-readable tree. Run it against a query that joins multiple tables, and you'll see, laid out hierarchically, the order in which each table gets accessed — handy for getting a feel for the execution plan as a whole.
EXPLAIN FORMAT=TREE
SELECT u.email, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.prefecture = 'Tokyo';
-> Nested loop inner join (cost=450 rows=100)
-> Table scan on o (cost=100 rows=1000)
-> Filter: (u.prefecture = 'Tokyo') (cost=0.25 rows=0.1)
-> Single-row index lookup on u using PRIMARY (id=o.user_id) (cost=0.25 rows=1)
The more deeply indented a line is, the earlier it runs. In this example, execution starts with a full scan of the orders table, and for every matching row found there, users gets looked up by primary key. Since there's no index yet on orders.user_id, a full scan is what gets picked as the starting point for the join.
https://dev.mysql.com/doc/refman/8.4/en/explain.html
Covering Indexes
As mentioned earlier, an InnoDB secondary index first finds the primary key value for a matching row, then uses that primary key to look up the actual row data a second time. That second lookup is called a table access, and it's relatively expensive compared to the index lookup itself.
If every column your SELECT statement needs is already contained in the index, that second table access can be skipped entirely — the query is satisfied from the index lookup alone. An index that makes this possible is called a covering index.
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 123;
In this example, both user_id and status — the columns the SELECT clause asks for — are contained in the index, so it works as a covering index with no access to the table itself. The bigger the number of columns you're fetching, or the larger each row is, the more a covering index tends to help. That said, adding too many columns to an index increases its size and drives up write costs, so it's best applied to your highest-frequency queries rather than everything.
Running EXPLAIN confirms this: the Extra column shows Using index, meaning no access to the table itself occurred.
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_orders_user_status | idx_orders_user_status | 4 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
Cases Where an Index Doesn't Get Used
Even with an index in place, the way you write a query can keep it from being used the way you'd expect. Here are a few common patterns.
The first is applying a function or calculation to an indexed column. Transforming the column side like this means the value no longer matches what's stored in the index, so it tends to fall back to a full table scan. To check this, we've indexed created_at on its own and compared the two with EXPLAIN.
CREATE INDEX idx_users_created_at ON users (created_at);
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2026;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
Even though created_at has an index, type shows ALL, confirming a full table scan is happening. Rewriting the condition to transform the comparison value instead lets the index get used.
EXPLAIN SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | idx_users_created_at | idx_users_created_at | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
After the rewrite, type changes to range and key now shows idx_users_created_at was selected.
The second is searching with a leading wildcard instead of a prefix match. A pattern like LIKE '%keyword%' can't take advantage of a B-tree index's sort order, so it won't use the index. A prefix match like LIKE 'keyword%', on the other hand, works fine with a regular index.
The third is violating the leftmost prefix rule for a composite index. As covered earlier, if your query doesn't include the leading column of a composite index in its condition, that index usually won't be used.
The fourth is stale table statistics, which can lead the optimizer to conclude — incorrectly — that skipping the index is actually faster. Running ANALYZE TABLE to refresh the statistics can resolve this.
ANALYZE TABLE users;
Things to Keep in Mind When Using Indexes
Indexes speed up reads, but they come with trade-offs, so adding them indiscriminately isn't a good idea.
The biggest trade-off is the impact on write performance. Every INSERT, UPDATE, or DELETE has to update not just the row itself but every index tied to that row. The more indexes a table has, the more write overhead you take on.
Disk usage is another cost that's easy to overlook. Indexes occupy their own storage separate from the table, and with enough composite indexes in play, the combined size of your indexes can end up larger than the table itself.
Unused indexes are also a common sight in practice. An index added for a query pattern that's since changed just sits there adding write overhead without providing any benefit. Checking views like sys.schema_unused_indexes periodically, and clearing out indexes that aren't pulling their weight, helps keep a table in good shape.
Summary
We covered how MySQL indexes work at the B-tree level, how to create them, how column order matters for composite indexes, how to verify their effect with EXPLAIN, and the situations where an index stops being used. A few things worth keeping in mind when designing and operating tables in practice:
- An index is a structure for narrowing down the rows a search has to scan, and its B-tree structure handles equality lookups as well as range conditions and sorting efficiently
- When designing a composite index, base the column order on which combinations of columns your queries actually use, following the leftmost prefix rule
- A covering index — one that contains every column a query needs — lets you skip the extra lookup against the table itself
EXPLAINtells you whether an index is actually being used, and columns liketypeandExtrareveal the details of the execution plan- Indexes come with write-performance and disk-usage costs, so it's worth periodically reviewing which ones are actually being used