Index Design: Which Indexes to Create

Knowing how indexes work is half the job. The other half is deciding which ones to create, and when to stop.

A method that works

1. Collect the real queries: the slow query log, performance_schema, or the queries in your code. Do not guess.
2. Sort them by cost: how often they run times how long they take. A 50 ms query that runs a million times a day matters more than a 5 second query that runs once.
3. For each important query, note:

• the equality conditions (=, IN),
• the range condition (>, <, BETWEEN, LIKE 'abc%'),
• the sort and group columns,
• the columns selected.

1. Design one index per query shape: equality columns, then sort, then range. Consider making it covering if the query is critical and narrow (Composite Indexes, Covering Indexes).
2. Test with EXPLAIN and EXPLAIN ANALYZE on realistic data.
3. Measure again after deployment.

Always index

Usually do not index

The over-indexing problem

Each index slows every INSERT, UPDATE (of its columns) and DELETE, uses disk and memory in the buffer pool, and makes the optimizer’s job harder. A table with 15 indexes where 5 would do pays a constant tax on every write. Keep the smallest set that serves your real queries.

Find redundant indexes

An index is redundant if another index has the same leading columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_customer (customer_id),
    KEY idx_customer_status (customer_id, status),
    KEY idx_customer_status_created (customer_id, status, created_at)
);

SELECT table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema = 'learn'
ORDER BY redundant_index_name;

1
2
3
4
5
6
7
8

+------------+----------------------+-----------------------------+
| table_name | redundant_index_name | dominant_index_name         |
+------------+----------------------+-----------------------------+
| orders     | idx_customer         | idx_customer_status         |
| orders     | idx_customer         | idx_customer_status_created |
| orders     | idx_customer_status  | idx_customer_status_created |
+------------+----------------------+-----------------------------+
3 rows in set

idx_customer and idx_customer_status are both prefixes of idx_customer_status_created, so the big index serves their queries too. Drop the two smaller ones (after making them invisible and checking).

Find unused indexes

The sys schema reports indexes that have not been used since the server started:

1

SELECT object_schema, object_name, index_name FROM sys.schema_unused_indexes WHERE object_schema = 'learn' ORDER BY object_name, index_name;

1
2
3
4
5
6
7
8

+---------------+-------------+-----------------------------+
| object_schema | object_name | index_name                  |
+---------------+-------------+-----------------------------+
| learn         | orders      | idx_customer                |
| learn         | orders      | idx_customer_status         |
| learn         | orders      | idx_customer_status_created |
+---------------+-------------+-----------------------------+
3 rows in set

(This list depends on the server’s activity since its last restart, so it is not compared.) Be careful: an index may be unused simply because the server restarted recently, or because it is used only in a monthly report. Observe for a full business cycle before you act. Test with an invisible index first (Invisible Indexes).

Check the index size

1
2
3
4

SELECT table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'learn' AND stat_name = 'size'
ORDER BY size_mb DESC;

1
2
3
4
5
6
7
8
9

+------------+-----------------------------+---------+
| table_name | index_name                  | size_mb |
+------------+-----------------------------+---------+
| orders     | PRIMARY                     |    0.02 |
| orders     | idx_customer                |    0.02 |
| orders     | idx_customer_status         |    0.02 |
| orders     | idx_customer_status_created |    0.02 |
+------------+-----------------------------+---------+
4 rows in set

(Sizes differ per server.) On big tables, this shows which indexes cost the most.

Indexes and the write path

When you insert a row into a table with six indexes, MySQL updates six B-trees. For bulk loads, you can create secondary indexes after loading the data (Fast Inserts).

Primary key choice affects everything

Because every secondary index contains the primary key, a short, ever-increasing primary key (INT or BIGINT UNSIGNED AUTO_INCREMENT) keeps all the indexes smaller and inserts fast (PRIMARY KEY, UUIDs as Keys).

Review checklist

• [ ] Every foreign key column is indexed (and the index starts with that column).
• [ ] Every frequent WHERE/JOIN/ORDER BY has an index, verified with EXPLAIN.
• [ ] Composite indexes follow: equality, then sort, then range.
• [ ] No redundant indexes (sys.schema_redundant_indexes).
• [ ] Candidate unused indexes were tested invisible for a full cycle.
• [ ] Statistics are fresh (ANALYZE TABLE after big changes).
• [ ] Writes on the table are not slowed too much by the number of indexes.
• [ ] Index creation is in a migration, with a comment about which query it serves.