Home›SQL›MySQL – Indexes

MySQL – Indexes

October 23, 20247 min readUpdated 8/25/2026

Indexes are the largest single lever on query speed, and the one place where a small change turns a query from unusable into instant. Every plan in this lesson was measured against pizza_lab — the pizza schema filled to 400,000 orders and 1,000,000 line items, because at the demo database's 18 rows InnoDB reads everything and there is nothing to learn.

What an index is

A B-tree holding the indexed column's values in sorted order, each with a pointer back to the row. Sorted order is the whole trick: finding a value is a handful of steps down the tree instead of a walk through every row.

Without one:

EXPLAIN SELECT * FROM customer_order WHERE customer_name = 'Customer 42';
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | customer_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 396091 |    10.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+

type: ALL and no possible_keys — a full table scan of every row to find a handful. Add the index:

CREATE INDEX idx_demo_name ON customer_order (customer_name);

EXPLAIN SELECT id, total FROM customer_order WHERE customer_name = 'Customer 42';
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer_order | NULL       | ref  | idx_demo_name | idx_demo_name | 602     | const |    8 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

396,091 rows examined becomes 8. That is the entire subject in one comparison.

One caveat on every plan in this lesson: rows is an estimate the optimizer derives from sampled statistics, and it moves by a fraction of a percent between identical runs. Read these figures as approximate — it is the order of magnitude that carries the argument.

What it costs

An index is a second copy of that column, kept sorted, updated on every insert, update and delete that touches it. So:

  • Writes get slower, roughly in proportion to the number of indexes on the table.
  • It takes disk and, more importantly, space in the buffer pool.
  • An index nothing uses is pure cost. Find them in INFORMATION_SCHEMA and drop them.

Index the columns you filter, join and sort on. Not every column, and not "just in case".

The clustered primary key

InnoDB stores the rows themselves inside the primary key's B-tree. That has two consequences worth knowing:

A primary-key lookup is one operation. The plan says const:

EXPLAIN SELECT id, total FROM customer_order WHERE id = 12345;
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer_order | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

A secondary index lookup is usually two. The secondary index stores the primary key as its pointer, so MySQL finds the key there and then walks the clustered index to fetch the row. This is why a wide primary key inflates every other index, and why the CREATE TABLE lesson insists on a small ascending one.

Covering indexes

If the index contains every column the query needs, the second lookup never happens — the plan says Using index:

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE status = 'PAID';
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys             | key                       | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | customer_order | NULL       | ref  | idx_customer_order_status | idx_customer_order_status | 122     | const | 14398 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+

Using index is the phrase to look for — it means the table itself was never touched. This is also the strongest practical argument against SELECT *: one extra column can be the difference between a covered query and one that fetches every row.

Composite indexes and the leftmost rule

An index on several columns is ordered by the first, then the second within that, and so on — like a phone book sorted by surname then first name.

CREATE INDEX idx_demo_composite ON customer_order (status, order_type, created_at);

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE status = 'PAID' AND order_type = 'DELIVERY';
+----+-------------+----------------+------------+------+----------------------------------------------+--------------------+---------+-------------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys                                | key                | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+----------------------------------------------+--------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | customer_order | NULL       | ref  | idx_customer_order_status,idx_demo_composite | idx_demo_composite | 204     | const,const | 9718 |   100.00 | Using index |
+----+-------------+----------------+------------+------+----------------------------------------------+--------------------+---------+-------------+------+----------+-------------+

Both leading columns used, and covered. The leftmost prefix rule says an index on (a, b, c) serves queries on a, on a, b, and on a, b, c — but not on b alone, because the index is not sorted by b until you have fixed a.

Except MySQL 8 sometimes can skip it

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE order_type = 'DELIVERY';
+----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+----------------------------------------+
| id | select_type | table          | partitions | type  | possible_keys      | key                | key_len | ref  | rows   | filtered | Extra                                  |
+----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+----------------------------------------+
|  1 | SIMPLE      | customer_order | NULL       | range | idx_demo_composite | idx_demo_composite | 204     | NULL | 197630 |   100.00 | Using where; Using index for skip scan |
+----+-------------+----------------+------------+-------+--------------------+--------------------+---------+------+--------+----------+----------------------------------------+

The query names only the second column and MySQL used the index anyway — "Using index for skip scan", an optimisation added in MySQL 8.0.13. It works by treating the index as one sub-range per distinct value of the leading column, which pays off only when that column has few distinct values. status has five, so it does.

Do not read this as "column order no longer matters". Look at the row estimate: 198,045 against 9,718 for the properly-ordered query. Skip scan rescues a query the index was not designed for; it is not a substitute for designing it right. Put the equality columns first, and the most selective of them first among those.

Drop it again before the next section, so the plans below reflect the schema's own indexes:

DROP INDEX idx_demo_composite ON customer_order;

The four ways to lose your index

A function around the column is the classic:

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE YEAR(created_at) = 2024;

That plan reports possible_keys: NULL and type: index, and the usual summary of it is too strong. The index cannot be used to find anything — but MySQL still scanned the whole index rather than the whole table, because the index is narrower and happens to cover the query. So it is not quite as slow as a table scan, and it still examines essentially every row in the table.

(Its rows figure is deliberately not quoted here: it is an estimate that changes between identical runs. EXPLAIN shows why.)

The range form is the fix, and its plan is a different shape entirely:

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';
+----+-------------+----------------+------------+-------+-------------------------------+-------------------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys                 | key                           | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+----------------+------------+-------+-------------------------------+-------------------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | customer_order | NULL       | range | idx_customer_order_created_at | idx_customer_order_created_at | 8       | NULL | 33296 |   100.00 | Using where; Using index |
+----+-------------+----------------+------------+-------+-------------------------------+-------------------------------+---------+------+-------+----------+--------------------------+

The four to watch for:

  1. A function on the column. YEAR(created_at), DATE(created_at), UPPER(name). Rewrite as a range.
  2. A leading wildcard. LIKE '%roni' cannot use sorted order at all. LIKE 'Pep%' can. See LIKE.
  3. A type mismatch. Comparing a VARCHAR column to a number makes MySQL convert the column, which is a function in disguise.
  4. Low selectivity. If a value matches a large share of the table, the optimizer decides a scan is cheaper than millions of index lookups plus row fetches — and it is usually right.

If you genuinely need a computed value indexed, MySQL 8 lets you index a generated column:

ALTER TABLE customer_order
    ADD COLUMN order_year INT AS (YEAR(created_at)) VIRTUAL,
    ADD INDEX idx_demo_year (order_year);

Reading and cleaning up

SHOW INDEX FROM customer_order;
DROP INDEX idx_demo_name ON customer_order;
DROP INDEX idx_demo_year ON customer_order;
ALTER TABLE customer_order DROP COLUMN order_year;

SHOW INDEX reports Cardinality, the estimated number of distinct values — an estimate, refreshed by ANALYZE TABLE. When a query suddenly picks a bad plan, stale statistics are a real suspect.

What to remember

  • An index turns a scan into a lookup: 396,091 rows became 8.
  • It costs on every write. Unused indexes are pure loss.
  • Using index means covered — the table was never touched.
  • Composite indexes work left to right; skip scan sometimes rescues you, at ~20× the rows.
  • Functions, leading wildcards and type mismatches all take the lookup away.