SQL Indexes: B-Tree, Composite Indexes, EXPLAIN and Why an Index Is Not Used
The messages table has an index on created_at, yet SELECT * FROM messages WHERE DATE(created_at) = '2024-03-01'; still reads all 100 000 rows: EXPLAIN shows type: ALL and key: NULL. Write the same filter as a range, created_at >= '2024-03-01' AND created_at < '2024-03-02', and MySQL reads 1440 rows through the index. The only difference is the function wrapped around the column.
An index in a database is a separate, ordered structure that stores the values of one or more table columns together with references to the rows, so the database can find matching rows without scanning the whole table. In MySQL with the InnoDB engine almost every index is a B-tree (more precisely, a B+tree). You pay for fast lookups with disk space and slower INSERT, UPDATE and DELETE: every time a row changes, the database also updates each of its indexes.
What an SQL Index Is and Why You Need It
An SQL index lets the database find rows for a WHERE condition, join tables and sort a result without reading the whole table. Without an index MySQL checks every row (a full table scan); with one, it walks down an ordered tree straight to the values it needs. The price is disk space and slower writes.
Think of the index at the back of a book: to find every page about EXPLAIN, you do not leaf through the whole book. But the index takes up pages of its own, and every edit of the text means updating it.
| Operation | Without an index | With an index on the filtered column |
|---|---|---|
SELECT ... WHERE column = value | Reads every row of the table | Reads a few tree pages and only the matching rows |
SELECT ... WHERE column BETWEEN a AND b | Reads every row | Finds the start of the range and walks through neighbouring values |
ORDER BY column LIMIT 10 | Sorts all rows (Using filesort) | Takes the first 10 values from the index, no sorting needed |
INSERT of one row | Writes the row | Writes the row and adds an entry to every index of the table |
UPDATE of an indexed column | Changes the value | Changes the value and moves the entry inside the index |
| Disk space | Data only | Data plus a copy of the indexed columns and row references |
An index and a key are not the same thing: a key sets a rule (uniqueness, a reference to another table), while an index speeds up searching. MySQL builds an index for every PRIMARY KEY, UNIQUE and FOREIGN KEY on its own, whereas an ordinary index created with CREATE INDEX forbids nothing. More on this in the lesson on keys.
How a B-Tree Index Works in InnoDB
A B-tree index is a balanced tree that keeps values sorted and has all its leaves at the same depth. Looking up a value goes from the root down to a leaf in O(log n) page reads, and neighbouring leaves are linked to each other, so ranges and sorted output are read sequentially. A MySQL index in InnoDB is a B+tree variant: the data lives only in the leaves.
Simplified, an index on messages.created_at looks like this:
root: [ 2024-02-01 | 2024-03-01 ]
/ | \
leaves: [01-01 ... 01-31] <-> [02-01 ... 02-29] <-> [03-01 00:00 id=86404, 03-01 00:01 id=86405, ...] To find the messages from 1 March, MySQL compares the value with the keys in the root, goes into the right branch, finds the first entry 2024-03-01 00:00 and moves right along the leaves until the dates leave the range. One InnoDB page is 16 KB and holds hundreds of keys, so even for a table with tens of millions of rows the tree is usually only 3–4 levels deep.
Clustered Index: Rows Are Stored in the PRIMARY KEY
In InnoDB the table itself is an index on the primary key: the leaves of the clustered index hold complete rows, sorted by PRIMARY KEY. That is why a lookup by id is the fastest one — reach the leaf and you have the row. If there is no primary key, InnoDB uses the first UNIQUE index on NOT NULL columns, and if there is none either, it creates a hidden 6-byte row ID.
Secondary Index: First the Index, Then the Row
Every other index is a secondary index. Its leaves hold the values of the indexed columns and the primary key value, not a physical row address. A lookup through a secondary index takes two steps: find the matching id values in it, then for each id go down the clustered index to fetch the remaining columns.
| Index | What its leaves contain | How SELECT * is read |
|---|---|---|
Clustered (PRIMARY KEY (id)) | The whole row: id, topic_id, author_id, body, created_at | One descent through the tree |
Secondary (created_at) | created_at + id | A descent through the secondary index, then a descent through the clustered index for every row found |
Two practical consequences follow. A long primary key (say, a UUID in CHAR(36)) bloats every secondary index, because it is copied into each of its entries. And if a query asks only for columns that are already in the secondary index, the second step is not needed at all — such an index is called a covering index, and EXPLAIN marks it with Using index in the Extra column. See the example in MySQL EXPLAIN.
Practice Data: 100 000 Messages
To see indexes at work you need a table that is expensive to read in full. On five rows MySQL almost always picks a full scan: reading one data page is cheaper than going down an index and then into the clustered index as well. So we fill the messages table of the forum practice database with 100 000 rows.
Recreate the database with the script from The Forum Practice Database and run the block below in MySQL 8. It adds a date to messages, creates 98 more threads and generates the messages with a recursive CTE inside INSERT ... SELECT (inserting a query result is covered in INSERT ... SELECT: Inserting Query Results).
ALTER TABLE messages
ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- recursion is limited to 1000 steps by default
SET SESSION cte_max_recursion_depth = 100000;
-- threads with id 3..100
INSERT INTO topics (title, author_id)
WITH RECURSIVE seq (n) AS (
SELECT 3 UNION ALL SELECT n + 1 FROM seq WHERE n < 100
)
SELECT CONCAT('Thread ', n), 1 + n % 3 FROM seq;
-- 100 000 messages: 1000 per thread, one message per minute from 1 January 2024
INSERT INTO messages (topic_id, author_id, body, created_at)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT 1 + n % 100,
1 + n % 3,
CONCAT('Message ', n),
TIMESTAMP '2024-01-01 00:00:00' + INTERVAL n MINUTE
FROM seq;
ANALYZE TABLE messages; -- refresh the statistics for the optimizer
SELECT COUNT(*) FROM messages; +----------+
| COUNT(*) |
+----------+
| 100004 |
+----------+ The four messages from the original script were added to the 100 000 new rows. Keep the distribution in mind when you check the EXPLAIN output below: every thread has about 1000 messages (threads 1 and 2 have 1002 each, counting the two original messages), each of the three authors owns roughly a third of the table, and every day from 2 January to 9 March 2024 has exactly 1440 messages (1 January has 1439, because the first message is at 00:01).
Your EXPLAIN numbers will be slightly different
The values of rows, filtered and Cardinality are estimates that InnoDB builds from a random sample of pages. After ANALYZE TABLE on the same data you may see, for example, 99 812 instead of 99 800, and cost and timing depend on the machine and server version. The exact parts of the plans in this lesson are type, key, key_len and Extra — those are what you read a plan by.
CREATE INDEX, DROP INDEX and SHOW INDEX
You create an index with CREATE INDEX name ON table (columns);, with the same operation inside ALTER TABLE ... ADD INDEX, or right in CREATE TABLE. DROP INDEX name ON table; removes it, and SHOW INDEX FROM table; lists the indexes of a table. An index name must be unique within its table.
| Action | Command | What to know |
|---|---|---|
| Create an index | CREATE INDEX idx_topics_title ON topics (title); | A repeated name gives ERROR 1061: Duplicate key name |
| The same with ALTER TABLE | ALTER TABLE topics ADD INDEX idx_topics_title (title); | One ALTER TABLE can add several indexes |
| When creating the table | CREATE TABLE ... (..., INDEX idx_topics_title (title)); | KEY is a synonym for INDEX |
| Unique index | CREATE UNIQUE INDEX uq_topics_title ON topics (title); | Fails if the column already has duplicates (ERROR 1062) |
| Composite index | CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at); | Column order matters — see Composite Index |
| Drop an index | DROP INDEX idx_topics_title ON topics; | Or ALTER TABLE topics DROP INDEX idx_topics_title; |
| List indexes | SHOW INDEX FROM messages; | One row per column of every index |
What else ALTER TABLE can do is covered in ALTER TABLE: Changing a Table. A TEXT or BLOB column cannot be indexed in full: CREATE INDEX idx_messages_body ON messages (body); fails with ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length. For such columns you give a prefix length — (body(50)) — and the index stores only the first 50 characters.
You do not need to declare the indexes MySQL creates by itself: for the PRIMARY KEY, for every UNIQUE, and for a foreign key column if no suitable index exists yet (see Foreign Key). That is why messages already has indexes:
SHOW INDEX FROM messages; +----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Null | Index_type | Visible |
+----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+
| messages | 0 | PRIMARY | 1 | id | 99800 | | BTREE | YES |
| messages | 1 | fk_messages_topic | 1 | topic_id | 100 | | BTREE | YES |
| messages | 1 | fk_messages_author | 1 | author_id | 3 | YES | BTREE | YES |
+----------+------------+--------------------+--------------+-------------+-------------+------+------------+---------+ The output is trimmed: not every SHOW INDEX column is shown. Non_unique = 0 marks a unique index. Cardinality is an estimate of the number of distinct values: 100 threads, 3 authors. The closer it is to the row count, the more selective the index. With a cardinality of 3, each author_id value covers a third of the table, so that index filters out few rows — we come back to this in Why MySQL Is Not Using Your Index.
MySQL EXPLAIN: How to Read a Query Plan
EXPLAIN placed before a SELECT (and also UPDATE, DELETE, INSERT) shows the execution plan: the order in which MySQL reads the tables, which index it picked and how many rows it expects to read. The query itself is not executed. The key columns of the plan are type, key, rows and Extra.
Take the query from the top of the lesson — the messages from 1 March, written as a range. There is no index on created_at yet. The \G terminator in the mysql client prints the plan row vertically, which is easier to read:
EXPLAIN SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: messages
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99800
filtered: 11.11
Extra: Using where type: ALL is a full table scan: there are no suitable indexes (possible_keys: NULL), MySQL expects to read about 99 800 rows and filter them with the condition (Using where). filtered: 11.11 is a rough guess of the share of rows that pass the condition: without an index or a histogram, the optimizer assumes each inequality lets a third of the rows through. Now create an index and run it again:
CREATE INDEX idx_messages_created ON messages (created_at);
EXPLAIN SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: messages
partitions: NULL
type: range
possible_keys: idx_messages_created
key: idx_messages_created
key_len: 5
ref: NULL
rows: 1440
filtered: 100.00
Extra: Using index condition Now it is type: range — a range read of the index — and instead of 99 800 rows the plan promises 1440, exactly the number of messages in one day. key_len: 5 is how many bytes of the key are used: a DATETIME takes 5 bytes. Using index condition means the condition is checked directly on the index entries (Index Condition Pushdown), before MySQL goes to the clustered index for the rest of the row.
Covering Index: Using index in Extra
If a query needs only columns that are in the index, the trip to the clustered index is not needed at all. id is stored in every secondary index, so this query reads nothing but idx_messages_created:
EXPLAIN SELECT id, created_at FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'\G type: range
key: idx_messages_created
key_len: 5
rows: 1440
Extra: Using where; Using index (Here and below the output is trimmed to the relevant lines.) Using index is the sign of a covering index. Add body to the column list and it disappears: the body is only in the clustered index.
EXPLAIN type Values: From Best to Worst
| type | What MySQL does | Example on the messages table |
|---|---|---|
const | Finds at most one row by a primary or unique key and treats it as a constant | WHERE id = 500 (key PRIMARY, key_len 4) |
eq_ref | In a join, finds exactly one row of the second table for each row of the first, by a unique key | JOIN users u ON u.id = m.author_id — for table u |
ref | Finds all rows with a given value of a non-unique index | WHERE topic_id = 2 (key_len 4, ref const) |
range | Reads a range of the index: >, <, BETWEEN, IN, LIKE 'abc%' | WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02' |
index | Reads the whole index, but not the table | SELECT author_id FROM messages |
ALL | Reads the whole table | WHERE body LIKE '%ice%' |
index beats ALL only because an index is more compact than the table: the number of rows walked through is the same. Joins are covered in the lesson on SQL JOIN types, and DEPENDENT SUBQUERY in the select_type column — in Correlated Subqueries.
Other Columns and Extra
| Column / value | What it means | What to look for |
|---|---|---|
possible_keys | Indexes that match the query conditions | NULL — there is no suitable index at all |
key | The index the optimizer chose | possible_keys is not empty but key: NULL — the index exists, but MySQL decided to read the table |
key_len | How many bytes of the key are used | For a composite index, shows how many columns are actually used |
rows | Estimated number of rows to read | Compare it with the table size and the expected result |
filtered | Estimated percentage of rows left after the conditions | A low percentage with a large rows — a lot is read for nothing |
Using where | Rows are additionally checked against WHERE | Normal; bad together with type: ALL on a large table |
Using index | Covering index: table rows are not read | A good sign |
Using filesort | The result is sorted separately, the index did not provide the order | On many rows — a candidate for an index matching ORDER BY |
Using temporary | A temporary table is needed: GROUP BY, DISTINCT, UNION over non-indexed expressions | For example, GROUP BY DATE(created_at) |
key_len is easy to work out by hand. INT NOT NULL is 4 bytes, a nullable INT is 5 (one more byte for the NULL flag), DATETIME NOT NULL is 5, and a VARCHAR(50) in utf8mb4 declared NOT NULL is 50 × 4 + 2 = 202 bytes (2 bytes store the length). That is why WHERE author_id = 3 gives key_len: 5 while WHERE topic_id = 2 gives 4: the messages.author_id column allows NULL.
EXPLAIN ANALYZE and FORMAT=TREE
Since MySQL 8.0.18, EXPLAIN ANALYZE runs the query and prints the plan as a tree, with the real time and row counts next to the estimates. If the estimate (rows= in the parentheses with cost) and the actual value (actual ... rows=) differ several times over, run ANALYZE TABLE.
EXPLAIN ANALYZE SELECT * FROM messages
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'; -> Index range scan on messages using idx_messages_created, with index condition: ((messages.created_at >= TIMESTAMP'2024-03-01 00:00:00') and (messages.created_at < TIMESTAMP'2024-03-02 00:00:00')) (cost=648 rows=1440) (actual time=0.041..2.37 rows=1440 loops=1) Time and cost depend on the machine and the server version. EXPLAIN FORMAT=TREE SELECT ... gives the same tree format without running the query, and FORMAT=JSON adds detailed cost estimates.
EXPLAIN ANALYZE actually runs the query
A plain EXPLAIN does not execute the query, but EXPLAIN ANALYZE executes it in full and just does not send the rows to the client. A query that takes a minute will take a minute under EXPLAIN ANALYZE too. On a production database, start with a plain EXPLAIN.
Composite Index and the Leftmost Prefix Rule
A composite index is built on several columns: entries are ordered by the first column, then by the second when the first is equal, and so on. So an index on (a, b, c) works for conditions on a, on a and b, and on a, b and c, but not for conditions on b or c alone. This is the leftmost prefix rule.
It works like a phone book sorted by last name, then by first name: finding every Smith is easy, finding every John Smith is easy too, but the book will not help you find everyone named John — they are scattered across all last names.
Suppose the table has an index on (topic_id, author_id, created_at). Here is which conditions it supports and how many key bytes key_len shows (4 + 5 + 5, as worked out in MySQL EXPLAIN):
| WHERE condition | Is the index used? | key_len | Why |
|---|---|---|---|
topic_id = 2 | Yes | 4 | Prefix (a) |
topic_id = 2 AND author_id = 3 | Yes | 9 | Prefix (a, b) |
topic_id = 2 AND author_id = 3 AND created_at >= '2024-03-01' | Yes, all three columns | 14 | Equality on a and b, range on the last column |
topic_id = 2 AND created_at >= '2024-03-01' | First column only | 4 | author_id is skipped; the date condition is checked on index entries (Using index condition) |
topic_id > 50 AND author_id = 3 | First column only | 4 | After a range on a, entries with the needed b are no longer contiguous |
author_id = 3 | No | — | The first column is missing |
author_id = 3 AND created_at >= '2024-03-01' | No | — | The first column is missing |
There is one exception: skip scan (MySQL 8.0.13+), where the optimizer loops over the values of the first column and searches by the second for each of them. It rarely kicks in — only when the query reads nothing but the index columns and the first column has few distinct values. Do not design indexes around it.
The order of conditions in WHERE does not matter
WHERE author_id = 3 AND topic_id = 2 uses an index on (topic_id, author_id, ...) exactly like WHERE topic_id = 2 AND author_id = 3: the optimizer matches conditions to columns itself. What matters is the column order in the index, not in the query text.
Equality First, Range Second
For WHERE topic_id = 2 AND created_at >= '2024-03-01' the right index is (topic_id, created_at): inside thread 2 the entries are already sorted by date, so MySQL reads one contiguous slice and key_len is 4 + 5 = 9. An index on (created_at, topic_id) is worse for the same query: the range on the first column grabs messages of every thread after 1 March, and topic_id is checked inside that slice (key_len: 5). The rule: put the columns compared with equality first in the index, and the range column after them.
One Index for WHERE and ORDER BY
A typical forum query is the latest 20 messages of a thread. With a single-column index on topic_id, MySQL finds the thread’s messages but sorts them separately. To make the comparison fair, temporarily hide the date index from the earlier section from the optimizer — otherwise it might prefer that one:
ALTER TABLE messages ALTER INDEX idx_messages_created INVISIBLE;
EXPLAIN SELECT id, body, created_at FROM messages
WHERE topic_id = 2
ORDER BY created_at DESC
LIMIT 20\G type: ref
possible_keys: fk_messages_topic
key: fk_messages_topic
key_len: 4
ref: const
rows: 1002
Extra: Using filesort All 1002 messages of the thread are read and sorted, though only 20 are needed. A composite index on (topic_id, created_at) keeps them already ordered by date:
CREATE INDEX idx_messages_topic_created ON messages (topic_id, created_at);
EXPLAIN SELECT id, body, created_at FROM messages
WHERE topic_id = 2
ORDER BY created_at DESC
LIMIT 20\G type: ref
possible_keys: fk_messages_topic,idx_messages_topic_created
key: idx_messages_topic_created
key_len: 4
ref: const
rows: 1002
Extra: Backward index scan Using filesort is gone: MySQL reads the index from the end of thread 2’s slice (Backward index scan, MySQL 8.0+) and stops after 20 rows. rows: 1002 stays in the plan because this estimate ignores LIMIT. key_len: 4 is also correct: only topic_id is used for the lookup, while created_at supplies the ready-made order. Make the date index visible again, you will need it later:
ALTER TABLE messages ALTER INDEX idx_messages_created VISIBLE; Sorting by an index works when ORDER BY follows the index columns in their order, right after the equality columns. ORDER BY created_at DESC, id DESC also needs no sort (id is implicitly stored at the end of every secondary index), whereas mixed directions such as ORDER BY topic_id ASC, created_at DESC without a thread condition need an index declared as (topic_id ASC, created_at DESC) — descending indexes are supported since MySQL 8.0.
Why MySQL Is Not Using Your Index
MySQL does not use an index when the condition cannot be turned into a search over ordered values: the column is wrapped in a function, the pattern starts with %, the compared types do not match, or the leftmost prefix is broken. In one more case the optimizer simply decides a full scan is cheaper. Either way, EXPLAIN shows key: NULL or type: ALL.
| Reason | Example | What to do |
|---|---|---|
| A function or expression on the column | WHERE DATE(created_at) = '2024-03-01', WHERE YEAR(created_at) = 2024, WHERE id + 1 = 10 | Rewrite it as a range on the bare column or create a functional index |
LIKE with a leading % | WHERE title LIKE '%fishing%' | LIKE 'Ice%' does use the index; for word search use a FULLTEXT index and MATCH ... AGAINST |
| A string column compared with a number | WHERE phone = 14155552671 with phone VARCHAR | Pass a string in the stored format: phone = '+14155552671' |
| OR across different columns | WHERE topic_id = 2 OR author_id = 3 | An index on each column (index merge) or a UNION of two queries |
| Leftmost prefix broken | WHERE author_id = 3 with an index on (topic_id, author_id) | A separate index or a different column order (see Composite Index) |
| Low selectivity | WHERE author_id = 1 — a third of the table | Not a bug: a full scan really is cheaper here |
!=, NOT IN, NOT LIKE | WHERE topic_id != 2 — 99% of the rows | Usually nothing: almost the whole table matches |
| Different character sets or collations in a JOIN | A utf8mb3 column joined to a utf8mb4 column | Bring both columns to the same character set and collation |
A Function on the Column
The index stores the values of created_at, not of DATE(created_at). To check a condition with a function, MySQL has to compute it for every row:
EXPLAIN SELECT * FROM messages WHERE DATE(created_at) = '2024-03-01'\G type: ALL
possible_keys: NULL
key: NULL
rows: 99800
filtered: 100.00
Extra: Using where This is the query from the top of the lesson. Rewrite the condition so that the column stays “bare”: created_at >= '2024-03-01' AND created_at < '2024-03-02' — the plan with type: range and 1440 rows is shown in MySQL EXPLAIN. In the same way YEAR(created_at) = 2024 becomes created_at >= '2024-01-01' AND created_at < '2025-01-01'. Keep the upper bound strict: BETWEEN '2024-03-01' AND '2024-03-02' would also catch a message posted exactly at midnight on 2 March.
If the expression cannot be rewritten, MySQL 8.0.13+ offers a functional index — an index on an expression, written in double parentheses:
CREATE INDEX idx_messages_created_day ON messages ((DATE(created_at))); It works only for queries with exactly the same expression. For dates, rewriting the condition as a range is simpler and works everywhere.
Implicit Type Conversion: What Will This Query Show?
The users.phone column is a VARCHAR(20) with the unique index uq_users_phone. The number is passed as a number, without quotes:
EXPLAIN SELECT * FROM users WHERE phone = 14155552671;
SHOW WARNINGS; The plan shows type: ALL, and the warnings contain a note:
Note 1739: Cannot use ref access on index 'uq_users_phone' due to type or collation conversion on field 'phone' When a string is compared with a number, MySQL converts every value of the column to a number. The strings '14155552671', '+14155552671' and ' 14155552671' are all equal to the number 14155552671, yet in the index they sit in different places — the sorted order of strings is useless for such a search. The fix is to pass a string in exactly the format you store: phone = '+14155552671'. The opposite direction is safe: WHERE id = '5' on a numeric id uses the index, because only the constant is converted, not the column. The same Note 1739 appears when a JOIN links string columns with different character sets or collations.
The Index Exists, but the Optimizer Ignores It
The MySQL optimizer picks a plan by estimated cost, not by the mere existence of an index. Reading through a secondary index means a separate trip into the clustered index for every row, which is random page access. When a noticeable share of the table matches (for example, author_id = 1 — about 33 000 rows out of 100 000), reading the whole table sequentially is cheaper. In the plan it looks like this: possible_keys: fk_messages_author, but key: NULL. If you are sure the index is better, refresh the statistics first (ANALYZE TABLE) and keep FORCE INDEX hints as a last resort: once the data changes, they start to get in the way.
With OR across different columns MySQL can merge two indexes (type: index_merge, with Using union(...) in Extra), but if one of the columns has no index or one of the conditions is not selective, it reads the whole table. A reliable replacement is two queries combined with UNION, each using its own index.
When an Index Is Useless or Harmful
An index is useless on small tables, on columns nobody searches by, and on columns with two or three distinct values. It hurts where a table changes often: every extra index slows down INSERT, UPDATE and DELETE and takes up space. Duplicate indexes are a separate problem — they only waste resources.
A lookup table of a dozen rows fits on a single page, so it needs no indexes beyond the primary key. A column such as a view counter, updated every time a thread is opened, is expensive to keep in an index: on each UPDATE the index entry is deleted and inserted again at a new position.
Duplicate Index
An index on (a) is redundant if there is one on (a, b): by the leftmost prefix rule, the composite index serves the same queries. After the composite index section, messages is in exactly this state: fk_messages_topic (topic_id) duplicates the beginning of idx_messages_topic_created (topic_id, created_at). A foreign key is satisfied by any index that has its column first, so the extra index can go:
DROP INDEX fk_messages_topic ON messages; Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0 Without the composite index the same command would fail: MySQL does not let you drop the last index a foreign key relies on (ERROR 1553: Cannot drop index 'fk_messages_topic': needed in a foreign key constraint). The reverse does not hold: (a, b) cannot be replaced by (a), and (b, a) is a different index altogether.
Before dropping an index on a production database, make it invisible first, as in the composite index section: ALTER TABLE ... ALTER INDEX ... INVISIBLE (MySQL 8.0). The optimizer stops using it, but the index keeps being maintained, and if some query suddenly slows down, you restore visibility instantly, without rebuilding anything.
Indexes in a Java Application
In Java projects indexes are declared where the schema lives: in migration scripts (Flyway, Liquibase) or in the JPA annotation @Table(indexes = ...), which Hibernate schema generation picks up. Slow queries are found not in the code but in the MySQL slow query log, and then analysed with EXPLAIN.
@Entity
@Table(name = "messages",
indexes = @Index(name = "idx_messages_topic_created",
columnList = "topic_id, created_at"))
public class Message { ... } Every @ManyToOne relationship needs an index on its foreign key column: both a JOIN and lazy loading of the topic.getMessages() collection run WHERE topic_id = ?. MySQL creates that index automatically together with the foreign key; PostgreSQL does not, so there you create it explicitly.
Index Mistakes That Never Raise an Error
Most index problems produce no error at all: the query returns the correct result, it just reads the whole table. That is why any query that runs often or touches a large table gets checked with EXPLAIN — the table below lists what you will see there and how to fix it.
| Situation | What EXPLAIN shows | How to fix it |
|---|---|---|
A function on an indexed column: DATE(created_at) = ... | type: ALL, possible_keys: NULL | A range on the column or a functional index |
A number instead of a string: phone = 14155552671 | type: ALL, Note 1739 in SHOW WARNINGS | Pass a value of the same type; in Java — setString() |
Index (created_at, topic_id) for topic_id = ? AND created_at >= ? | type: range, key_len: 5 instead of 9 | Equality columns first: (topic_id, created_at) |
| Searching by the second column of a composite index | key: NULL | A separate index or a different column order |
Separate indexes on topic_id and created_at for WHERE topic_id = ? ORDER BY created_at | Using filesort | One composite index (topic_id, created_at) |
| An index on every column “just in case” | Plans do not change, inserts get slower | Index for specific queries, drop duplicates |
| Testing an index on a 5-row table | type: ALL although the index exists | Test on a data volume close to production |
CREATE INDEX on a TEXT column | Never gets to a plan: ERROR 1170 | A prefix (body(50)) or a FULLTEXT index |
Frequently Asked Questions
How many indexes can a MySQL table have?
InnoDB allows up to 64 secondary indexes per table, and one composite index can contain at most 16 columns. In practice you hit write speed long before the limit: every index is updated on every INSERT and DELETE. A typical application table needs the primary key, the foreign key indexes and a few composite indexes for frequent queries.
What is the difference between a B-tree index and a HASH index?
A hash index finds a row by exact equality but cannot handle ranges, ORDER BY or the leftmost prefix of a composite key. A B-tree handles all of that, which is why it is the default. InnoDB does not support explicit hash indexes: CREATE INDEX ... USING HASH on an InnoDB table silently creates a B-tree, as the Index_type column of SHOW INDEX shows. A real HASH index exists in the MEMORY engine, and InnoDB builds an internal adaptive hash index for frequently read pages on its own.
Does MySQL use an index for IS NULL?
Yes. Unlike Oracle, where rows with NULL in every indexed column do not get into a B-tree index, MySQL stores NULL in the index, and WHERE author_id IS NULL gives type: ref on fk_messages_author. The condition author_id = 3 OR author_id IS NULL runs in a single pass with the ref_or_null type.
Should the most selective column always come first in a composite index?
That rule is secondary. Columns the query compares with equality come first, then the column used for a range or sorting: (topic_id, created_at) beats (created_at, topic_id) for a search by thread and date, even though created_at has more distinct values. Selectivity only decides the order among columns that are all compared with equality. The breakdown with key_len is in Composite Index and the Leftmost Prefix Rule.
Why is pagination with a large OFFSET slow even with an index?
ORDER BY created_at DESC LIMIT 20 OFFSET 50000 makes MySQL walk through 50 020 index entries and throw away the first 50 000. The index saves the sort, not the skipping. For deep pages use keyset pagination: remember the date and id of the last row shown and ask for WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. LIMIT and OFFSET syntax is covered in LIMIT and OFFSET: First N Rows and Pagination.
Comments