Lesson 13 intermediate

Indexes and Query Performance

Make Orbit's queries fast: how InnoDB's clustered primary key and secondary indexes work, composite indexes and the leftmost-prefix rule, covering indexes, descending, prefix, functional and invisible indexes, reading EXPLAIN (type, key, rows, Extra), EXPLAIN ANALYZE, statistics and histograms, index hints, the slow query log, and finding unused and redundant indexes with the sys schema.

With five notes every query is instant. With five million, the difference between a good index and none is the difference between 1 millisecond and 10 seconds. This lesson explains how MySQL uses indexes, how to read its query plans and how to find the queries that need help.

Note: The plans in this lesson assume Orbit after a year of growth, about 2 million notes. On the five-row tables from earlier lessons, MySQL often prefers a full scan because it is cheaper, so your output will differ.

How InnoDB stores a table

InnoDB keeps every table as a B+tree ordered by the primary key. The leaves of that tree hold the full rows; this is the clustered index. Looking up a note by id walks a few levels of the tree and lands on the row: always fast.

Every other index is a secondary index: a separate B+tree ordered by its own columns, whose leaves contain those columns plus the primary key. Finding notes by user_id means searching idx_notes_user, collecting primary keys, then looking each row up in the clustered index.

Two consequences:

  • A short primary key keeps every secondary index small, since each index entry repeats it. INT UNSIGNED beats a 36-character UUID string.
  • If a secondary index already contains every column a query needs, MySQL skips the lookup into the clustered index. That is a covering index, shown later.

Creating and dropping indexes

CREATE INDEX idx_notes_created ON notes (created_at);
ALTER TABLE notes ADD INDEX idx_notes_title (title);
SHOW INDEX FROM notes;
DROP INDEX idx_notes_title ON notes;
DROP INDEX idx_notes_created ON notes;

Indexes are created online: reads and writes continue while InnoDB builds them. They are not free, though: every INSERT, UPDATE and DELETE must update every index on the table, and each index uses disk and buffer pool memory. Add the indexes your queries need, not one per column.

Reading EXPLAIN

EXPLAIN shows the plan MySQL chose without running the query. Orbit’s most frequent query lists a user’s notes, newest first:

EXPLAIN SELECT id, title, created_at FROM notes
WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | notes | NULL       | ref  | idx_notes_user | idx_notes_user |       4 | const | 8400 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------+
1 row in set (0.00 sec)

The columns to look at first:

ColumnMeaning
typeHow rows are found. Best to worst: const, eq_ref, ref, range, index, ALL
keyThe index actually used; NULL means none
rowsEstimated rows to examine
filteredEstimated percentage left after the WHERE conditions
ExtraAdditional work: Using index (covering, good), Using where, Using filesort and Using temporary (extra sorting or grouping work)

Here MySQL finds Ben’s 8,400 notes through idx_notes_user (type = ref), but then has to sort all of them to return the newest 20: Using filesort.

type = ALL means a full table scan. That is what a leading-wildcard search does:

EXPLAIN SELECT id FROM notes WHERE title LIKE '%tips%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | notes | NULL       | ALL  | NULL          | NULL |    NULL | NULL | 1987320 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set (0.00 sec)

No B-tree index helps with %tips%; lesson 17’s full-text index does.

Composite indexes and the leftmost prefix

An index on several columns is sorted by the first column, then by the second within equal first values, like a phone book sorted by last name then first name. Give the list query an index that matches both its filter and its sort:

ALTER TABLE notes ADD INDEX idx_notes_user_created (user_id, created_at);
EXPLAIN SELECT id, title, created_at FROM notes
WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20;
+----+-------------+-------+------------+------+---------------------------------------+------------------------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys                         | key                    | key_len | ref   | rows | filtered | Extra               |
+----+-------------+-------+------------+------+---------------------------------------+------------------------+---------+-------+------+----------+---------------------+
|  1 | SIMPLE      | notes | NULL       | ref  | idx_notes_user,idx_notes_user_created | idx_notes_user_created |       4 | const | 8400 |   100.00 | Backward index scan |
+----+-------------+-------+------------+------+---------------------------------------+------------------------+---------+-------+------+----------+---------------------+
1 row in set (0.00 sec)

The filesort is gone: Ben’s notes are already stored in created_at order inside the index, so MySQL reads the last 20 entries backwards and stops.

The leftmost-prefix rule says which queries a composite index can serve:

Query filters onCan (user_id, created_at) help?
user_id = ?Yes
user_id = ? AND created_at > ?Yes, fully
user_id = ? ORDER BY created_atYes, no sort needed
created_at > ? onlyNo, created_at is not the first column
user_id IN (...) AND created_at > ?Yes, as several ranges

Put columns compared with = first, then the column used for a range or ORDER BY. A range on an earlier column stops the later columns from being used for filtering.

The new index also makes idx_notes_user redundant: any query that used it can use the composite index, whose first column is the same. The foreign key is satisfied by the new index too, so drop the old one:

ALTER TABLE notes DROP INDEX idx_notes_user;

Covering indexes

If every column a query touches is in the index, MySQL never reads the table rows. Extra then says Using index:

EXPLAIN SELECT created_at FROM notes WHERE user_id = 2;
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | notes | NULL       | ref  | idx_notes_user_created | idx_notes_user_created |       4 | const | 8400 |   100.00 | Using index |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

id is covered as well, because secondary indexes always contain the primary key. Covering indexes are a big win for hot queries, and another reason not to write SELECT *.

Other kinds of index

KindExampleUse
UniqueUNIQUE KEY (email)Enforce uniqueness and speed lookups
Descending (8.0+)INDEX (user_id, pinned DESC, created_at DESC)Mixed sort directions without a filesort
PrefixINDEX (body(100))Index the start of long text; cannot cover
Functional (8.0.13+)INDEX ((DATE(created_at)))Index an expression; queries must use the same expression
Full-textFULLTEXT (title, body)Word search (lesson 17)
Multi-valued (8.0.17+)INDEX ((CAST(tags->'$' AS CHAR(30) ARRAY)))Search inside JSON arrays (lesson 17)
SpatialSPATIAL INDEX (location)Geometry types

Functional indexes need double parentheses. A query is only served when it uses exactly the indexed expression: WHERE DATE(created_at) = '2026-11-02' can use INDEX ((DATE(created_at))), but the better fix is usually the half-open range on the plain column, which any index on created_at serves.

Invisible indexes

Dropping an index on a big table and then discovering a query needed it is painful. Make it invisible first: MySQL keeps maintaining it but the optimizer ignores it. Say you suspect an index on title is no longer used:

ALTER TABLE notes ALTER INDEX idx_notes_title INVISIBLE;
-- watch performance for a few days
ALTER TABLE notes ALTER INDEX idx_notes_title VISIBLE;

If nothing slowed down, drop it; if something did, making it visible again is instant.

Why an index is not used

  • A function on the column: WHERE YEAR(created_at) = 2026 cannot use an index on created_at. Write a range instead.
  • Type conversion: comparing a string column with a number (lesson 11).
  • Leading wildcard: LIKE '%x'.
  • Low selectivity: if the condition matches a large part of the table, a full scan is genuinely cheaper, and the optimizer is right to skip the index.
  • Wrong column order in a composite index.
  • Stale statistics: the optimizer’s estimates are off.

Statistics, histograms and hints

InnoDB samples index statistics automatically. After big data changes, refresh them:

ANALYZE TABLE notes;

For columns without an index, a histogram tells the optimizer how values are distributed, which improves join order and filtering estimates:

ANALYZE TABLE notes UPDATE HISTOGRAM ON pinned WITH 2 BUCKETS;

When the optimizer still picks the wrong index and you know better, hints override it: FROM notes FORCE INDEX (idx_notes_user_created), USE INDEX (...) or IGNORE INDEX (...). Treat hints as a last resort and leave a comment explaining why, because they stop the optimizer adapting as data changes.

EXPLAIN ANALYZE

EXPLAIN ANALYZE (MySQL 8.0.18+) runs the query and reports actual times and row counts next to the estimates, in tree form:

EXPLAIN ANALYZE SELECT id, title, created_at FROM notes
WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s)  (cost=1250 rows=20) (actual time=0.051..0.093 rows=20 loops=1)
    -> Index lookup on notes using idx_notes_user_created (user_id=2) (reverse)  (cost=1250 rows=8400) (actual time=0.049..0.088 rows=20 loops=1)

Read the tree from the innermost line outward. Big differences between estimated rows and actual rows point to stale statistics or a missing histogram. Because it really runs the statement, do not use EXPLAIN ANALYZE on slow writes in production. EXPLAIN FORMAT=TREE shows the same tree without running anything.

Finding slow queries

Turn on the slow query log to record every statement that takes longer than a threshold:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- seconds
SHOW VARIABLES LIKE 'slow_query_log_file';

Put the same settings in my.cnf to keep them after a restart. Summarise the log with mysqldumpslow -s t /var/lib/mysql/host-slow.log (top queries by total time) or Percona’s pt-query-digest for richer reports.

The sys schema answers the same question from live statistics, without a log file:

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 5;

And it finds indexes worth removing:

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'orbit';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'orbit';

Unused-index data is only meaningful after the server has run a representative workload since its last restart.

Quick reference

TaskSQL
Add an indexCREATE INDEX idx ON t (a, b);
List indexesSHOW INDEX FROM t;
See the planEXPLAIN SELECT ...; / EXPLAIN FORMAT=TREE ...
Measure actual executionEXPLAIN ANALYZE SELECT ...;
Refresh statisticsANALYZE TABLE t;
Hide before droppingALTER TABLE t ALTER INDEX idx INVISIBLE;
Expression indexCREATE INDEX idx ON t ((expr));
Slow query logSET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;
Unused indexesSELECT * FROM sys.schema_unused_indexes;

Exercise

Which query can use an index on (user_id, created_at)?

  1. SELECT * FROM notes WHERE created_at > ‘2026-10-01’
  2. SELECT * FROM notes WHERE YEAR(created_at) = 2026
  3. SELECT * FROM notes WHERE title LIKE ‘%tips%’
  4. SELECT * FROM notes WHERE user_id = 2 AND created_at > ‘2026-10-01’
Show answer

Answer: D. A composite index serves queries that use its leftmost column. The first query skips user_id, the second wraps the column in a function and the third searches a different column.

Exercise

EXPLAIN shows Extra = Using index. What does that mean?

  1. The index contains every column the query needs, so table rows are not read
  2. The query uses an index hint
  3. An index is used, but rows still need a filesort
  4. MySQL created a temporary index for the query
Show answer

Answer: A. “Using index” marks a covering index: the answer comes from the index alone, skipping lookups into the clustered index.

Exercise

You suspect an index is no longer needed on a large production table. What is the safest first step?

  1. Drop it during a quiet hour
  2. Run OPTIMIZE TABLE to see whether it is used
  3. Make it INVISIBLE, watch performance, then drop it
  4. Add IGNORE INDEX to every query
Show answer

Answer: C. An invisible index is still maintained but ignored by the optimizer. If a query slows down, making it visible again is instant, whereas rebuilding a dropped index takes time.

Key takeaways

  • InnoDB stores rows in primary-key order; secondary indexes hold their columns plus the primary key.
  • Design composite indexes with equality columns first, then the range or sort column.
  • Read EXPLAIN: type, key, rows and Extra (Using filesort, Using temporary, Using index).
  • Avoid functions and type conversions on indexed columns in WHERE.
  • Find slow queries with the slow query log or sys.statement_analysis; retire indexes via INVISIBLE first.

FAQ

How many indexes should a table have?

As few as your important queries need. Typical OLTP tables have three to six. Each extra index slows writes and uses memory. Use the sys schema to remove unused and redundant ones.

Should I index every foreign key column?

MySQL does it for you: a foreign key requires an index on the child column and creates one if needed. Often a composite index starting with the foreign key column, like (user_id, created_at), serves both the constraint and your queries.

Does the order of conditions in WHERE matter?

No. WHERE created_at > ? AND user_id = ? and the reverse produce the same plan. Only the column order inside a composite index matters.

Why is my query fast on my laptop but slow in production?

Different data volume and distribution lead to different plans. Compare EXPLAIN on both, check that statistics are fresh, and test with production-sized data before shipping new queries.

↑ ↓ navigate Enter open Esc close