Indexes

The problem indexes solve

Suppose a table has 40,000 rows and you ask for the rows of one user. Without help, MySQL has to read every row and check it, a full table scan. That is fine for a hundred rows and painful for a hundred million. An index is a separate, sorted structure that points to the rows, like the index at the back of a book: instead of reading every page for "Joins", you look it up alphabetically and go straight to page 142.

MySQL's InnoDB engine stores indexes as B-trees, a balanced tree where each lookup takes a handful of steps even for enormous tables. Lookups, range searches (BETWEEN, <, >) and sorting all benefit.

A table big enough to feel it

This lesson uses a generated table of 39,304 rows. The statement builds it from the shop data in a repeatable way, so you get the same rows we do:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS log_entries;
CREATE TABLE log_entries (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  user_id    INT NOT NULL,
  action     VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL,
  amount     DECIMAL(8,2) NOT NULL
);
INSERT INTO log_entries (user_id, action, created_on, amount)
SELECT (a.id * 7 + b.id * 3 + c.id) % 2000 + 1,
       ELT((a.id + b.id + c.id) % 4 + 1, 'login', 'view', 'purchase', 'logout'),
       DATE_ADD('2024-01-01', INTERVAL (a.id * b.id + c.id) % 300 DAY),
       ((a.id * 31 + b.id * 17 + c.id * 13) % 50000) / 100
FROM shop.order_items a, shop.order_items b, shop.order_items c;
SELECT COUNT(*) AS rows_in_table FROM log_entries;

+---------------+
| rows_in_table |
+---------------+
|         39304 |
+---------------+

(The three-way cross join of a 34-row table gives 34 x 34 x 34 rows. It is a handy way to make test data.)

Measuring work without a stopwatch

Timings vary from run to run, so we measure something exact: how many rows the storage engine had to read. MySQL counts them in Handler_read_* status variables. Handler_read_rnd_next is the number of rows read during a table scan, and Handler_read_next is the number read by following an index.

First, without an index on user_id:

FLUSH STATUS;
SELECT COUNT(*) AS entries_for_user FROM log_entries WHERE user_id = 77;
SHOW SESSION STATUS LIKE 'Handler_read%';

+------------------+
| entries_for_user |
+------------------+
|               86 |
+------------------+
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 39305 |
+-----------------------+-------+

The scan counter shows that every one of the roughly 39,000 rows was read to find the matches. Now create an index and repeat:

CREATE INDEX idx_log_user ON log_entries (user_id);
FLUSH STATUS;
SELECT COUNT(*) AS entries_for_user FROM log_entries WHERE user_id = 77;
SHOW SESSION STATUS LIKE 'Handler_read%';

+------------------+
| entries_for_user |
+------------------+
|               86 |
+------------------+
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 86    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Now MySQL made a single key lookup (Handler_read_key) and then stepped through only the matching entries. The same answer needed a tiny fraction of the work. That is the whole point of an index.

Creating and dropping indexes

There are several equivalent ways to create one:

CREATE INDEX idx_log_created ON log_entries (created_on);
ALTER TABLE log_entries ADD INDEX idx_log_action (action);

You can also declare them inside CREATE TABLE with INDEX name (column) or KEY name (column). List what exists, and remove what you do not need:

SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'practice' AND TABLE_NAME = 'log_entries'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

+-----------------+-------------+--------------+------------+
| INDEX_NAME      | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE |
+-----------------+-------------+--------------+------------+
| idx_log_action  | action      |            1 |          1 |
| idx_log_created | created_on  |            1 |          1 |
| idx_log_user    | user_id     |            1 |          1 |
| PRIMARY         | id          |            1 |          0 |
+-----------------+-------------+--------------+------------+

DROP INDEX idx_log_action ON log_entries;

You get some indexes for free. A PRIMARY KEY and every UNIQUE constraint create one, and so does each FOREIGN KEY (if no suitable index exists).

Composite indexes and the leftmost prefix

An index can cover several columns, and the order matters. The index below is sorted by user_id first and, within each user, by created_on:

CREATE INDEX idx_log_user_date ON log_entries (user_id, created_on);

Think of a phone book sorted by last name, then first name. You can quickly find all "Sharma"s, or "Sharma, Priya", but you cannot quickly find everyone called "Priya": the book is not sorted by first name. This is the leftmost-prefix rule. The index above helps queries that filter on:

• user_id alone,
• user_id and created_on,
• user_id plus a range on created_on,

but not on created_on alone. Put the column you always filter on with = first, and range or sort columns after it.

A composite index can also make a query faster than any single-column index when it covers the query: every column the query needs is inside the index, so MySQL never touches the table at all (shown as Using index in EXPLAIN).

Unique indexes

A unique index adds a rule on top of the speed: no duplicates.

CREATE UNIQUE INDEX uq_customers_phone ON shop.customers (phone);

Because phone allows NULL, several customers can still have no phone. Remember it before adding uniqueness to existing data: duplicates must be fixed first.

Text, prefix and full-text indexes

An index on a long VARCHAR is big. For long text you can index only the first part, a prefix index: CREATE INDEX idx_title ON articles (title(30));. For searching words inside long text, MySQL offers FULLTEXT indexes and MATCH ... AGAINST queries, which are much better than LIKE '%word%':

ALTER TABLE shop.products ADD FULLTEXT INDEX ft_products_name (name);
SELECT name FROM shop.products WHERE MATCH(name) AGAINST('desk');

+-------------------+
| name              |
+-------------------+
| Desk Lamp         |
| Standing Desk Mat |
+-------------------+

What an index costs

Indexes are not free:

• Each insert, update and delete must also update every index on the table.
• Indexes take disk space and memory.
• Too many indexes can confuse the optimiser and slow writes badly.

So index with intent. Good candidates are columns used in WHERE, JOIN ... ON, ORDER BY and GROUP BY of queries that actually run often. Poor candidates are tiny tables, columns with few distinct values (like a yes/no flag, whose selectivity is low), and columns you never search on.

When an index stops being useful, MySQL 8 lets you hide it first with ALTER TABLE ... ALTER INDEX name INVISIBLE; to check nothing breaks before you drop it.

Common mistakes

• No index on foreign key or join columns (InnoDB adds one for foreign keys, but not for plain join columns).
• Wrong column order in a composite index.
• Wrapping the indexed column in a function (WHERE YEAR(created_on) = 2024), which stops the index being used. Compare the raw column with a range instead.
• A leading wildcard: LIKE '%son' cannot use a normal index.
• Adding indexes randomly instead of checking EXPLAIN on real slow queries.

Exercise

Using log_entries, create a composite index that suits the query WHERE action = 'purchase' AND created_on >= '2024-06-01'. Then confirm with the Handler_read counters, or with EXPLAIN, that the index is used.

CREATE INDEX idx_log_action_date ON log_entries (action, created_on);
FLUSH STATUS;
SELECT COUNT(*) AS purchases FROM log_entries WHERE action = 'purchase' AND created_on >= '2024-06-01';
SHOW SESSION STATUS LIKE 'Handler_read%';

+-----------+
| purchases |
+-----------+
|      3878 |
+-----------+
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 3878  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+