MySQL indexes and EXPLAIN: reading plans, fixing queries

Most slow MySQL queries are slow for one reason: the server reads far more rows than it returns, because no index lets it go straight to the ones it needs. The fix is an index whose columns match how the query filters and sorts - equality columns first, then the range or sort column - and the tool that tells you whether you got it right is EXPLAIN. Look at the type column (ALL means a full table scan), the key column (which index was chosen, if any), rows (how many rows MySQL expects to examine) and Extra (Using filesort and Using temporary are the expensive ones). Then run EXPLAIN ANALYZE to see what actually happened rather than what the optimizer guessed.

This guide explains how InnoDB stores tables and indexes, why column order in a composite index decides whether it is used, the patterns that make MySQL ignore an index you have, and how to read both forms of EXPLAIN, ending with a worked example. It applies to MySQL 8.0 and 8.4 LTS.

How InnoDB stores a table#

An InnoDB table is a B+tree ordered by its primary key. The leaf pages of that tree hold the full rows. This is the clustered index, and it is not optional: if you declare no primary key, InnoDB uses the first UNIQUE NOT NULL index, and if there is none, it invents a hidden 6-byte row ID called GEN_CLUST_INDEX that you cannot use in queries.

Every other index is a secondary index: a separate B+tree ordered by the indexed columns, whose leaves hold those columns plus the primary key value. Looking up a row through a secondary index is therefore two searches - one through the secondary index to find the primary key, then one through the clustered index to fetch the row.

Three practical consequences follow:

• Keep the primary key small. It is copied into every secondary index. A BIGINT is 8 bytes; a UUID stored as CHAR(36) is at least 36 bytes in every index entry, and the index reserves room for four bytes per character in utf8mb4.
• Sequential primary keys insert cheaply. An AUTO_INCREMENT key appends to the end of the tree. Random UUIDs insert all over it, splitting pages and fragmenting the cache. If you need UUIDs, store them as BINARY(16) with UUID_TO_BIN(UUID(), 1), which reorders the time component so values are roughly sequential.
• A query that only needs indexed columns can skip the second lookup. That is a covering index, shown as Using index in EXPLAIN, and it is often the biggest single win available.

Always give tables an explicit primary key. MySQL 8 can enforce it with sql_require_primary_key=ON, and replication and many tools behave badly without one.

Composite indexes and the leftmost prefix#

A composite index on (a, b, c) is sorted by a, then by b within each a, then by c. Like a phone book sorted by surname then first name, it is useful for any search that starts from the left.

The rule that follows: equality columns first, then one range or sort column, last. Once the index hits a range condition, the columns after it cannot be used to narrow the search, only to filter. Of several equality columns, order matters less than people think; put the one shared by the most queries first so the index serves them all.

MySQL 8.0.13 added a skip scan, which can use (a, b) for WHERE b = 2 when a has very few distinct values, by searching once per a. It shows as Using index for skip scan. It is a rescue, not a design.

Selectivity matters for the first column less than old advice suggests. What matters is that the index matches the query's shape. An index on a low-cardinality column alone (a status with three values) is rarely useful; the same column as the first part of (status, created_at) for "the latest paid orders" is excellent.

When MySQL ignores your index#

You created the index and EXPLAIN still says type: ALL. The usual causes:

-- A function on the column: the index holds email, not LOWER(email)SELECT * FROM users WHERE LOWER(email) = 'ana@example.com';-- Implicit conversion: phone is VARCHAR, the literal is a numberSELECT * FROM users WHERE phone = 5551234;-- Leading wildcard: the index is sorted from the first characterSELECT * FROM products WHERE name LIKE '%phone%';-- Arithmetic on the columnSELECT * FROM orders WHERE created_at + INTERVAL 1 DAY > NOW();-- Date functions instead of a rangeSELECT * FROM orders WHERE YEAR(created_at) = 2026;

The fixes, in order: rewrite the condition so the column stands alone (created_at > NOW() - INTERVAL 1 DAY, created_at >= '2026-01-01' AND created_at < '2027-01-01'), quote string literals so they match the column type, and for searches inside text use a FULLTEXT index rather than LIKE '%...%'. When the function is unavoidable, MySQL 8.0.13 and later support functional indexes - note the double parentheses:

CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

Two less obvious causes. Joining columns with different character sets or collations prevents index use on the converted side, which is a common result of a half-finished utf8mb4 migration. And the optimizer may correctly decide that a full scan is cheaper: if a condition matches 40% of a table, reading the table sequentially beats tens of thousands of index lookups. Do not force an index in that case; fix the query to need fewer rows.

EXPLAIN, column by column#

EXPLAIN SELECT id, total FROM ordersWHERE customer_id = 4821 AND status = 'paid'ORDER BY created_at DESC LIMIT 20\G

*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: orders   partitions: NULL         type: refpossible_keys: idx_customer          key: idx_customer      key_len: 8          ref: const         rows: 2310     filtered: 10.00        Extra: Using where; Using filesort

Ending a statement with \G instead of ; in the mysql client prints each row vertically, which is far easier to read for EXPLAIN.

The type values in plain language: const is a single row by primary key or unique index; eq_ref is one row per row of the previous table in a join; ref is several rows matching an equality on a non-unique index; range is an index range (BETWEEN, >, IN); index reads the whole index; ALL reads the whole table. index is not the good news it sounds like - it is a full scan of the index instead of the table.

The Extra notes worth recognising:

• Using index - covering: answered from the index alone. Good.
• Using index condition - index condition pushdown, filtering inside the index before fetching rows. Good.
• Using where - rows are filtered after being read. Normal, but with rows high it means waste.
• Using filesort - results are sorted after reading, in memory or on disk. Expensive on many rows.
• Using temporary - a temporary table was built, usually for GROUP BY or DISTINCT.
• Using join buffer (hash join) - a hash join, used since 8.0.18 when a join has no usable index. A sign the join column needs one.

In the example, the plan found 2310 orders for the customer through idx_customer, then filtered by status (estimated 10% survive) and sorted them all to return 20. That works, but it reads a hundred times more rows than it returns.

key_len is the quiet detail that answers "is my composite index fully used?". An INT is 4 bytes, a BIGINT 8, a nullable column adds 1, a VARCHAR(n) in utf8mb4 counts 4 × n + 2. If key_len covers only the first column of a three-column index, the other two are not being used for the search.

EXPLAIN ANALYZE and the tree format#

EXPLAIN shows estimates. EXPLAIN ANALYZE, added in 8.0.18, actually runs the query and reports what happened at every step, in the tree format:

EXPLAIN ANALYZE SELECT id, total FROM ordersWHERE customer_id = 4821 AND status = 'paid'ORDER BY created_at DESC LIMIT 20;

-> Limit: 20 row(s)  (cost=520 rows=20) (actual time=9.84..9.85 rows=20 loops=1)    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk       (cost=520 rows=2310) (actual time=9.84..9.84 rows=20 loops=1)        -> Filter: (orders.`status` = 'paid')  (cost=520 rows=231)           (actual time=0.11..9.52 rows=1874 loops=1)            -> Index lookup on orders using idx_customer (customer_id=4821)               (cost=520 rows=2310) (actual time=0.10..9.21 rows=2310 loops=1)

Read it from the innermost line outwards. Each step shows the estimate (cost, rows) and the reality (actual time as first-row..all-rows in milliseconds, rows, loops). Two things to look for:

• Estimates far from reality. Here the filter was expected to keep 231 rows and kept 1874. Large gaps mean the optimizer is planning with bad statistics, and may pick the wrong plan for other values. ANALYZE TABLE orders; refreshes index statistics; for unindexed columns with skewed values, a histogram helps: ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;.
• Where the time goes. Multiply time by loops for nested steps. The step whose actual time jumps is where the work is.

EXPLAIN ANALYZE executes the query, including any side effects of functions it calls, and it takes as long as the query does. For UPDATE and DELETE, use plain EXPLAIN, or run the equivalent SELECT. EXPLAIN FORMAT=TREE shows the same tree with estimates only, without running anything; EXPLAIN FORMAT=JSON adds cost detail. MySQL Workbench draws the JSON form as a diagram, which some people find easier on large joins.

Indexes for joins, GROUP BY and prefixes#

Joins follow the same logic one table at a time. MySQL reads the first table in the plan, then, for each row, looks up matching rows in the next one. That lookup needs an index on the join column of the second table - the foreign key side, typically. InnoDB creates an index automatically when you declare a FOREIGN KEY, which is one reason declared foreign keys rarely produce slow joins and undeclared "logical" ones often do. In EXPLAIN, a well-indexed join shows eq_ref or ref on the inner table; ALL with Using join buffer (hash join) means every row of one table is being compared against a hash of the other.

GROUP BY and DISTINCT can be answered by walking an index in order instead of building a temporary table, when the grouped columns are a leftmost prefix of an index and any equality conditions come before them. GROUP BY customer_id on an index starting with customer_id streams; the same query on an unindexed column shows Using temporary. For COUNT(*) grouped by a column, a narrow index on that column is also covering, so the table itself is never touched.

Long string columns can be indexed by prefix: INDEX (url(100)) indexes the first 100 characters. InnoDB's limit on an index key is 3072 bytes with the default DYNAMIC row format, which is 768 characters of utf8mb4, so a VARCHAR(1000) cannot be indexed whole. A prefix index cannot be covering and cannot serve ORDER BY on the full value; for exact lookups on long values such as URLs, indexing a stored hash in a generated column is often better - JSON and generated columns shows the technique.

A worked example#

The query above, again: a customer's latest paid orders. The table has an index on customer_id alone. The fix is an index that matches both equalities and the sort:

ALTER TABLE orders  ADD INDEX idx_customer_status_created (customer_id, status, created_at),  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE, LOCK=NONE asks for an online build: reads and writes continue while the index is created, and the statement fails rather than silently locking the table if that is not possible. On a large table it still uses I/O and temporary space, so build at a quiet time.

-> Limit: 20 row(s)  (cost=12.4 rows=20) (actual time=0.06..0.09 rows=20 loops=1)    -> Index lookup on orders using idx_customer_status_created       (customer_id=4821, status='paid') (reverse)       (cost=12.4 rows=1874) (actual time=0.06..0.08 rows=20 loops=1)

Twenty rows read, no sort, no filter: the index delivers the rows in created_at order, read backwards for DESC, and the LIMIT stops after twenty. From ten milliseconds to under one, and more importantly the cost no longer grows with the customer's order count. Adding total as a fourth column would make the index covering and skip the clustered index lookups too - worth it for a query that runs on every page view, not for one that runs hourly.

Now remove the index this one replaces. (customer_id) is a prefix of the new index, so it is redundant and only costs writes and memory.

Keeping indexes in check#

Indexes are not free. Each one is updated on every INSERT, on DELETE, and on any UPDATE touching its columns, and each takes buffer pool space that would otherwise cache data. The sys schema finds the ones not earning their keep:

-- Indexes not used since the server startedSELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'appdb';-- Indexes made redundant by another (a prefix of a longer one)SELECT table_name, redundant_index_name, dominant_index_nameFROM sys.schema_redundant_indexes WHERE table_schema = 'appdb';

"Unused since start-up" is only meaningful if the server has been up through a full business cycle - an index used only by the month-end report looks unused for 29 days. Before dropping one, make it invisible, which keeps it maintained but hides it from the optimizer:

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;-- watch for a week; if nothing slows down:ALTER TABLE orders DROP INDEX idx_customer;-- or, if something did:ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;

Invisible indexes arrived in 8.0 and are the safest way to test a removal. MySQL 8 also supports genuinely descending indexes (created_at DESC in the definition), useful when a query sorts by one column ascending and another descending, which an ascending index cannot serve.

Finding which queries need attention in the first place is the job of the slow query log. For the same topic on PostgreSQL, see PostgreSQL indexes explained and EXPLAIN ANALYZE for slow queries; the ideas carry over, the output format does not.

FAQ#

How many indexes should a table have?

As many as the queries need and no more. Most tables are well served by the primary key plus two to five secondary indexes. A write-heavy table with ten indexes pays ten updates per insert; check sys.schema_unused_indexes and remove what nothing uses.

Does the order of columns in WHERE matter?

No. The optimizer reorders conditions freely; WHERE b = 2 AND a = 1 uses an index on (a, b) exactly as WHERE a = 1 AND b = 2 does. The order of columns in the index definition is what matters.

Why does EXPLAIN show an index in possible_keys but key is NULL?

The optimizer considered it and estimated a full scan to be cheaper - usually because the condition matches a large share of the table, or because statistics are stale. Run ANALYZE TABLE and check again; if it still prefers the scan, it is probably right, and the query needs a more selective condition.

Should I use FORCE INDEX?

Rarely, and as a temporary measure. A forced index stays forced after the data changes and the plan becomes wrong. Fix the statistics, the index or the query first, and if a hint is needed, prefer the optimizer hint syntax /*+ INDEX(orders idx_name) */ documented for 8.0 and later.

Does adding an index lock the table?

Not usually in MySQL 8. Adding a secondary index to an InnoDB table is an online operation that allows reads and writes, apart from short metadata locks at the start and end. Ask for it explicitly with ALGORITHM=INPLACE, LOCK=NONE so the statement fails instead of locking if online is impossible.