Our sample tables have 10 employees and 8 orders, so every query comes back instantly. A real shopping site has millions of orders. Ask "show me the orders of customer 7920" on such a table, and without help the database must read every single row to find the 20 that match. That is slow, and it gets slower as the business grows.
An index is the help it needs. It is the single most important tool for making SQL queries fast, and "how do indexes work?" is one of the most common database interview questions.
An index is a separate, sorted data structure built on one or more columns of a table, which lets the database find matching rows without scanning the whole table.
In this chapter we will build a table with one million rows, measure a query before and after adding an index, learn to read EXPLAIN, and understand composite indexes, covering indexes, and the mistakes that stop an index from being used. Timings are real MySQL 8.4 measurements on a laptop; your numbers will differ, but the ratios will be similar.
The Book Index Analogy
Think of a 900-page DBMS textbook. To find the page about "deadlock", you have two options:
Read every page from 1 to 900 looking for the word. This is a full table scan.
Go to the index at the back, which lists topics alphabetically with page numbers. Find "deadlock" quickly (because the list is sorted), then jump straight to page 412.
A database index works the same way:
It stores the indexed column's values in sorted order, each with a pointer to the row.
Because it is sorted, the database can find a value quickly instead of reading everything.
It takes extra space (the index pages at the back of the book), and it must be updated whenever the data changes (a new edition needs a new index).
How an Index Is Stored: the B+ Tree
MySQL's InnoDB engine stores indexes as B+ trees. You will study B+ trees in detail in the DBMS tutorial; here is the intuition you need for SQL.
[ 20000 | 40000 ] <- root
/ | \
[ 5000 | 12000 ] [ 26000 | 33000 ] [ 45000 | ... ] <- internal nodes
/ | \ ... ...
[1..4999] [5000..11999] [12000..19999] ... <- leaf pages
<-------> <-------> (leaves linked in sorted order)
Each node is a disk page (16 KB in InnoDB) holding hundreds of keys, so the tree is very wide and short. Even a table with millions of rows needs only about 3 or 4 levels.
To find one value, MySQL walks from the root to a leaf: 3 or 4 page reads instead of thousands.
The leaf pages are linked together in sorted order, so after finding the start of a range (say, dates from 1 Jan to 31 Jan), MySQL simply walks along the leaves. That is why indexes help with
=,<,>,BETWEEN,ORDER BYand prefixLIKE.
Clustered and Secondary Indexes in InnoDB
InnoDB has two kinds of B+ tree indexes:
Clustered index | Secondary index | |
|---|---|---|
Built on | The PRIMARY KEY | Any other column(s) you index |
Leaf pages contain | The full rows themselves. The table is this tree. | The indexed column value(s) + the row's primary key |
How many per table | Exactly one | Many |
Lookup cost | One tree walk | Walk the secondary tree to get the PK, then walk the clustered tree to get the row |
If a table has no primary key, InnoDB clusters on the first UNIQUE NOT NULL index, or failing that, on a hidden internal row ID. This is one more reason every table should have a primary key.
Indexes You Already Have
MySQL creates some indexes automatically: for every PRIMARY KEY, every UNIQUE constraint, and every FOREIGN KEY column (InnoDB needs an index there to check references quickly). Let us look at the employees table in company_db:
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;+------------+--------------+-------------+------------+
| INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | NON_UNIQUE |
+------------+--------------+-------------+------------+
| dept_id | 1 | dept_id | 1 |
| email | 1 | email | 0 |
| manager_id | 1 | manager_id | 1 |
| PRIMARY | 1 | emp_id | 0 |
+------------+--------------+-------------+------------+We never wrote CREATE INDEX, but there are four: PRIMARY (from the primary key), email (from UNIQUE, so NON_UNIQUE = 0), and dept_id and manager_id (from the two foreign keys). SHOW INDEX FROM employees; shows the same information with many more columns.
Setting Up a Big Table
Indexes only matter when there is a lot of data, so let us create a practice table with one million orders. Run this in a practice database (not in company_db). It uses a 10-row digits table cross-joined with itself six times (106 combinations), a trick from the SELF JOIN & CROSS JOIN chapter. You do not need to understand every expression; it just generates varied but repeatable data.
CREATE TABLE digits (d INT PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE big_orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
ref_code VARCHAR(20) NOT NULL
);
INSERT INTO big_orders
SELECT n,
1 + (n * 7919) % 50000,
'2020-01-01' + INTERVAL (n * 37) % 1827 DAY,
100 + (n * 104729) % 99000 / 10,
ELT(1 + n % 10, 'Delivered','Delivered','Delivered','Delivered','Delivered',
'Delivered','Shipped','Shipped','Pending','Cancelled'),
CONCAT('ORD', LPAD(n, 7, '0'))
FROM (SELECT a.d + 10*b.d + 100*c.d + 1000*e.d + 10000*f.d + 100000*g.d + 1 AS n
FROM digits a CROSS JOIN digits b CROSS JOIN digits c
CROSS JOIN digits e CROSS JOIN digits f CROSS JOIN digits g) AS nums;Query OK, 1000000 rows affected (5.64 sec)
Records: 1000000 Duplicates: 0 Warnings: 0We now have 1,000,000 orders from 50,000 customers (exactly 20 orders each), dated 2020 to 2024, with 60% Delivered, 20% Shipped, 10% Pending and 10% Cancelled. The table has only one index so far: the primary key on order_id.
Before and After: Measuring an Index
Without an Index
How many orders has customer 7920 placed, and for how much?
SELECT COUNT(*), SUM(amount) FROM big_orders WHERE customer_id = 7920;+----------+-------------+
| COUNT(*) | SUM(amount) |
+----------+-------------+
| 20 | 97858.00 |
+----------+-------------+
1 row in set (0.64 sec)0.64 seconds to find 20 rows. That sounds small, but a busy website may run this query hundreds of times per second, and the time grows with the table. Why so slow? Let us ask MySQL how it ran the query.
Reading EXPLAIN
Put EXPLAIN in front of a query and MySQL shows its execution plan instead of running it:
EXPLAIN SELECT COUNT(*), SUM(amount) FROM big_orders WHERE customer_id = 7920;+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ALL | NULL | NULL | NULL | NULL | 941131 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)The important columns:
Column | Meaning | Here |
|---|---|---|
| How rows are accessed (see the table below) |
|
| Indexes that could be used | None |
| The index actually chosen | None |
| MySQL's estimate of rows it must examine | ~941,131 (an estimate of the real 1,000,000) |
| Additional notes |
|
The type column, from best to worst (the common values):
type | Meaning |
|---|---|
| At most one matching row, found via PRIMARY KEY or UNIQUE index with a constant |
| In a join, exactly one row per row of the previous table, via a PK/UNIQUE index |
| All rows matching a single value of a non-unique index |
| A range of an index ( |
| The whole index is scanned (better than ALL only because the index is smaller) |
| The whole table is scanned |
EXPLAIN ANALYZE goes one step further: it actually runs the query and reports real timings and row counts for each step. Its output is a tree (printed inside a one-cell table, whose border we have removed here):
EXPLAIN ANALYZE SELECT COUNT(*), SUM(amount) FROM big_orders WHERE customer_id = 7920;-> Aggregate: count(0), sum(big_orders.amount) (cost=107091 rows=1) (actual time=600..600 rows=1 loops=1)
-> Filter: (big_orders.customer_id = 7920) (cost=97680 rows=94113) (actual time=4.56..600 rows=20 loops=1)
-> Table scan on big_orders (cost=97680 rows=941131) (actual time=4.55..570 rows=1e+6 loops=1)Read it from the innermost line up: a table scan read 1e+6 (one million) rows, the filter kept 20 of them, and the aggregate added them up. 999,980 rows were read for nothing.
Creating the Index
CREATE INDEX index_name ON table_name (column1, column2, ...);CREATE INDEX idx_customer ON big_orders (customer_id);Query OK, 0 rows affected (2.10 sec)
Records: 0 Duplicates: 0 Warnings: 0Building the index took 2 seconds, because MySQL had to read all million rows and sort their customer IDs. That is a one-time cost. Now run the same query:
SELECT COUNT(*), SUM(amount) FROM big_orders WHERE customer_id = 7920;+----------+-------------+
| COUNT(*) | SUM(amount) |
+----------+-------------+
| 20 | 97858.00 |
+----------+-------------+
1 row in set (0.00 sec)From 0.64 s to under 0.01 s (the client rounds it to 0.00). Same answer, no change to the query. The plan explains why:
EXPLAIN SELECT COUNT(*), SUM(amount) FROM big_orders WHERE customer_id = 7920;+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_customer | idx_customer | 4 | const | 20 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)-> Aggregate: count(0), sum(big_orders.amount) (cost=13.7 rows=1) (actual time=0.0447..0.0448 rows=1 loops=1)
-> Index lookup on big_orders using idx_customer (customer_id=7920) (cost=11.7 rows=20) (actual time=0.0402..0.0418 rows=20 loops=1)type changed from ALL to ref, the estimate from 941,131 rows to 20, and the actual time from 600 ms to about 0.04 ms. MySQL walked the B+ tree of idx_customer straight to customer 7920, read the 20 entries, and used their primary keys to fetch the 20 rows.
Primary Key Lookups
The primary key is itself an index (the clustered one), so lookups by order_id were fast all along:
EXPLAIN SELECT * FROM big_orders WHERE order_id = 500000;+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | big_orders | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)EXPLAIN SELECT * FROM big_orders WHERE order_id BETWEEN 1000 AND 1100;+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 101 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)const for one exact key, range for a range of 101 keys.
When an Index Cannot Be Used
Having an index does not mean MySQL can use it. The index is sorted by the raw column value. If your condition is not about the raw value, the sorted order is useless. Let us add an index on the order date:
CREATE INDEX idx_date ON big_orders (order_date);1. A Function or Calculation on the Column
Two ways to ask the same question, "orders on 30 June 2022 above 9900":
SELECT order_id, amount FROM big_orders
WHERE order_date = '2022-06-30' AND amount > 9900;+----------+---------+
| order_id | amount |
+----------+---------+
| 21998 | 9954.20 |
| 308837 | 9917.30 |
| 442208 | 9963.20 |
| 729047 | 9926.30 |
| 862418 | 9972.20 |
+----------+---------+
5 rows in set (0.03 sec)SELECT order_id, amount FROM big_orders
WHERE DATE_ADD(order_date, INTERVAL 1 DAY) = '2022-07-01' AND amount > 9900;+----------+---------+
| order_id | amount |
+----------+---------+
| 21998 | 9954.20 |
| 308837 | 9917.30 |
| 442208 | 9963.20 |
| 729047 | 9926.30 |
| 862418 | 9972.20 |
+----------+---------+
5 rows in set (0.67 sec)Same 5 rows, but 0.03 s vs 0.67 s. Compare the plans:
-- order_date = '2022-06-30'
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_date | idx_date | 3 | const | 548 | 33.33 | Using where |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
-- DATE_ADD(order_date, INTERVAL 1 DAY) = '2022-07-01'
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ALL | NULL | NULL | NULL | NULL | 941131 | 33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+With the function, possible_keys is NULL: MySQL does not even consider idx_date. It cannot know which index entries give DATE_ADD(...) = '2022-07-01' without computing the function for every row. The same is true for YEAR(order_date) = 2023, salary * 12 > 1000000, LOWER(email) = '...' and so on.
For the "all of 2023" question, we measured:
Condition | Plan | Time |
|---|---|---|
|
| 0.17 s |
|
| 0.02 s |
Both returned COUNT(*) = 199782. This is why the Date & Time Functions chapter recommended range conditions on the bare column.
Rule: keep the indexed column alone on one side of the comparison. Move calculations to the constant side:
salary > 1000000 / 12instead ofsalary * 12 > 1000000.
2. LIKE With a Leading Wildcard
A prefix search is a range in sorted order (like finding all words starting with "data" in a dictionary). A search for a suffix is not. Let us add a unique index on ref_code:
CREATE UNIQUE INDEX idx_ref ON big_orders (ref_code);
EXPLAIN SELECT * FROM big_orders WHERE ref_code LIKE 'ORD000123%';+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | big_orders | NULL | range | idx_ref | idx_ref | 82 | NULL | 10 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)EXPLAIN SELECT * FROM big_orders WHERE ref_code LIKE '%0123';+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ALL | NULL | NULL | NULL | NULL | 941131 | 11.11 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)'ORD000123%' is a range of exactly 10 entries (ORD0001230 to ORD0001239). '%0123' is a full scan. For searching words inside text, MySQL offers separate FULLTEXT indexes.
A unique index also enforces uniqueness, just like a UNIQUE constraint:
INSERT INTO big_orders VALUES (1000001, 5, '2024-12-31', 500, 'Pending', 'ORD0000042');ERROR 1062 (23000): Duplicate entry 'ORD0000042' for key 'big_orders.idx_ref'3. Comparing a String Column With a Number
This one surprises even experienced developers:
SELECT COUNT(*) FROM big_orders WHERE ref_code = 0;+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set, 65535 warnings (0.28 sec)Every row matched! When you compare a string column with a number, MySQL converts each string to a number. 'ORD0000042' does not start with a digit, so it converts to 0, and 0 = 0. On top of the wrong answer, the index cannot be used because the conversion is a function applied to every row:
EXPLAIN SELECT * FROM big_orders WHERE ref_code = 0;+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ALL | idx_ref | NULL | NULL | NULL | 941131 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+Always quote values for string columns: WHERE ref_code = 'ORD0000042'. (The opposite direction is harmless: WHERE customer_id = '7920' converts the one constant to a number and still uses the index.)
Composite (Multi-Column) Indexes
An index can cover several columns. A composite index on (customer_id, order_date) is sorted by customer first, and by date within each customer, exactly like a phone book sorted by surname, then first name:
(customer_id, order_date)
(7919, 2024-05-24)
(7919, 2024-09-02)
(7920, 2020-02-07) <- all of customer 7920's orders are together,
(7920, 2020-02-29) already sorted by date
(7920, 2020-06-09)
...
(7921, 2020-04-04)Let us replace our single-column indexes with it. idx_customer becomes redundant, because the new index starts with customer_id:
DROP INDEX idx_date ON big_orders;
DROP INDEX idx_customer ON big_orders;
CREATE INDEX idx_cust_date ON big_orders (customer_id, order_date);The Leftmost Prefix Rule
A composite index can be used for any leftmost prefix of its columns. With (customer_id, order_date):
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 7920 AND order_date >= '2023-01-01';+----+-------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | big_orders | NULL | range | idx_cust_date | idx_cust_date | 7 | NULL | 8 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)key_len = 7 means both columns are used (4 bytes for the INT + 3 bytes for the DATE). MySQL jumps to customer 7920's first 2023 entry and reads only 8 rows.
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 7920;+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_cust_date | idx_cust_date | 4 | const | 20 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Only the first column is used (key_len = 4), and that is fine. But:
EXPLAIN SELECT * FROM big_orders WHERE order_date = '2023-01-18';+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ALL | NULL | NULL | NULL | NULL | 941131 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)A full scan. Orders on 18 Jan 2023 are scattered across all 50,000 customers in the index, just like you cannot find everyone named "Priya" quickly in a phone book sorted by surname.
WHERE clause (index on (a, b, c)) | Uses the index? |
|---|---|
| Yes (column a) |
| Yes (a, b) |
| Yes (a, b, c) |
| Yes; the order in WHERE does not matter, only which columns are present |
| Partly: seeks on a; c can only be checked while reading those entries |
| No seek (leftmost column missing) |
| Seeks on a only; after a range on a column, the next columns cannot narrow the seek |
So the column order matters when designing a composite index. A good default: put columns tested with = first, and the column used for a range or for sorting last.
Indexes Help ORDER BY Too
"Latest 3 orders of customer 7920":
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 7920 ORDER BY order_date DESC LIMIT 3;+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_cust_date | idx_cust_date | 4 | const | 20 | 100.00 | Backward index scan |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)No sorting step at all (no Using filesort in Extra). The customer's entries are already sorted by date in the index, so MySQL reads them backwards and stops after 3.
Covering Indexes
Remember that a secondary index lookup normally needs two steps: find the entry in the secondary index, then fetch the full row from the clustered index. If every column the query needs is inside the index, the second step is skipped. Such an index is called a covering index for that query.
In InnoDB every secondary index entry also contains the primary key, so idx_cust_date really holds (customer_id, order_date, order_id):
EXPLAIN SELECT order_id, order_date FROM big_orders WHERE customer_id = 7920;+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_cust_date | idx_cust_date | 4 | const | 20 | 100.00 | Using index |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Using index in Extra means "answered from the index alone". Add amount, which is not in the index, and the note disappears because MySQL must visit the table rows:
EXPLAIN SELECT order_date, amount FROM big_orders WHERE customer_id = 7920;+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_cust_date | idx_cust_date | 4 | const | 20 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)This is one more reason to avoid SELECT *: selecting only the columns you need can turn a query into an index-only query. (Using index condition, which we saw earlier, is different: it means part of the WHERE clause was checked inside the index before fetching rows, an optimisation called index condition pushdown.)
The Cost of Indexes
If indexes are so good, why not index every column? Because every index has costs.
1. Slower writes. Every INSERT, every DELETE, and every UPDATE of an indexed column must also update each index. We copied the same million rows into two tables, one with only a primary key and one with our three indexes (PRIMARY, idx_ref, idx_cust_date):
CREATE TABLE copy_plain ( -- same columns, only the PRIMARY KEY
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
ref_code VARCHAR(20) NOT NULL
);
CREATE TABLE copy_indexed LIKE big_orders; -- LIKE copies the indexes too
INSERT INTO copy_plain SELECT * FROM big_orders;
INSERT INTO copy_indexed SELECT * FROM big_orders;Query OK, 1000000 rows affected (8.10 sec) -- copy_plain
Query OK, 1000000 rows affected (14.01 sec) -- copy_indexed2. Extra storage. After running ANALYZE TABLE copy_plain, copy_indexed; to update the statistics:
SELECT TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024) AS data_mb,
ROUND(INDEX_LENGTH/1024/1024) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'copy%'
ORDER BY TABLE_NAME;+--------------+---------+----------+
| TABLE_NAME | data_mb | index_mb |
+--------------+---------+----------+
| copy_indexed | 57 | 49 |
| copy_plain | 57 | 0 |
+--------------+---------+----------+
2 rows in set (0.00 sec)Two secondary indexes take 49 MB, almost as much as the data itself (DATA_LENGTH includes the clustered primary key index, since that is the table).
3. Indexes on unselective columns can be useless or harmful. Selectivity means how well a value narrows down the rows. status has only 4 distinct values, and 60% of orders are 'Delivered'. Let us index it anyway:
CREATE INDEX idx_status ON big_orders (status);MySQL chose to use this index for status = 'Delivered', and the query got slower:
Query: | Time |
|---|---|
Using the index | 1.75 s |
Full table scan ( | 1.25 s |
Why? For 600,000 matches, the index path does 600,000 separate lookups into the clustered index, jumping around, while a full scan reads the table pages once in order. An index pays off when it narrows the result to a small fraction of the table. (IGNORE INDEX is an index hint; use hints only for experiments like this, not as a habit.)
More Index Features in MySQL 8
Functional Indexes
Since MySQL 8.0.13 you can index an expression. Note the extra parentheses:
CREATE INDEX idx_order_year ON big_orders ((YEAR(order_date)));
EXPLAIN SELECT COUNT(*) FROM big_orders WHERE YEAR(order_date) = 2023;+----+-------------+------------+------------+------+----------------+----------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | big_orders | NULL | ref | idx_order_year | idx_order_year | 2 | const | 410984 | 100.00 | NULL |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)Now YEAR(order_date) = 2023 can seek. This is useful when you cannot change the application's queries (for example LOWER(email) = ...). The query must use exactly the same expression as the index. If you can change the query, a plain range on an ordinary index is usually the simpler choice.
Invisible Indexes
Before dropping an index on a big production table (rebuilding it could take hours), you can hide it from the optimizer and watch whether anything gets slower:
ALTER TABLE big_orders ALTER INDEX idx_status INVISIBLE;
-- ... monitor ... then either
ALTER TABLE big_orders ALTER INDEX idx_status VISIBLE;
-- or
DROP INDEX idx_status ON big_orders;An invisible index is still maintained on every write; it is just not used for reads.
Other Ways to Create and Remove Indexes
-- inside CREATE TABLE
CREATE TABLE login_log (
id INT PRIMARY KEY,
user_id INT NOT NULL,
logged_at DATETIME NOT NULL,
INDEX idx_user_time (user_id, logged_at)
);
-- with ALTER TABLE
ALTER TABLE login_log ADD INDEX idx_time (logged_at);
ALTER TABLE login_log DROP INDEX idx_time;InnoDB indexes are B+ trees. You may also hear of hash indexes, which give very fast exact-match lookups but no ranges or sorting; in MySQL they are available for the MEMORY engine, and InnoDB builds an internal "adaptive hash index" automatically.
Which Columns Should You Index?
Good candidates | Poor candidates |
|---|---|
Columns in frequent | Columns with very few distinct values (status, gender, boolean flags), unless combined with other columns |
Join columns (foreign keys; MySQL indexes these automatically) | Small tables (a few hundred rows are scanned instantly) |
Columns used in | Columns that are updated constantly but rarely searched |
Columns that must be unique (unique index) | Columns already covered as the leftmost part of another index |
The professional approach: find the slow, frequent queries first, look at their EXPLAIN, and add the index that fixes them. Do not add indexes "just in case".
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Function or arithmetic on an indexed column in WHERE | Index ignored, full scan | Rewrite as a condition on the bare column, or use a functional index |
| Full scan | Use prefix search, or a FULLTEXT index for word search |
Unquoted number compared with a string column | Wrong results and no index | Quote string values |
Wrong column order in a composite index | Queries on the second column cannot seek | Equality columns first; follow the leftmost prefix rule |
Indexing every column | Slow writes, wasted space, confused optimizer | Index for real, frequent queries |
Duplicate indexes: |
| Drop the shorter one |
Testing performance on a 10-row table | Everything looks instant; the optimizer may ignore indexes | Test with realistic data volumes |
Trusting EXPLAIN's | It is an estimate from statistics | Use |
Interview Questions
What is an index and why is it used?
A separate sorted data structure (usually a B+ tree) on one or more columns that lets the database locate rows without scanning the whole table. It speeds up searches, joins and sorting at the cost of extra storage and slower writes.
Why do databases use B+ trees for indexes?
B+ trees are balanced and very wide, so lookups need only a few disk page reads (3-4 levels for millions of rows). All data pointers are in the leaves, which are linked in order, making range scans and ORDER BY efficient.
What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of the rows; its leaves contain the rows. A table has only one (in InnoDB, the primary key). A non-clustered (secondary) index is a separate structure whose leaves hold the key and a reference to the row (in InnoDB, the primary key value), so a lookup may need a second step.
What is a composite index and the leftmost prefix rule?
An index on several columns, sorted by the first column, then the second, and so on. It can be used for conditions on a leftmost prefix of its columns: (a), (a, b), (a, b, c), but not for b or c alone.
What is a covering index?
An index that contains all columns a query needs, so the query is answered from the index alone without reading table rows. EXPLAIN shows "Using index".
Why might a query not use an index even though one exists?
A function or calculation on the column, a leading-wildcard LIKE, a type conversion (string column compared with a number), a condition that does not use the leftmost column of a composite index, or low selectivity making a full scan cheaper.
What are the disadvantages of indexes?
Extra disk and memory space, and slower INSERT, UPDATE and DELETE because every index must be maintained. Too many indexes can also make the optimizer's choice harder.
Unique index vs primary key?
Both enforce uniqueness. A table has one primary key, which cannot be NULL and (in InnoDB) is the clustered index. A table can have many unique indexes, and their columns may contain NULLs (multiple NULLs are allowed).
How do you check whether a query uses an index?
Run EXPLAIN on it and look at the key and type columns; type = ALL with key = NULL is a full table scan. EXPLAIN ANALYZE shows actual times and row counts.
Summary
An index is a sorted structure (a B+ tree in InnoDB) that finds rows without a full table scan. On our million-row table it turned a 0.64 s query into one taking well under a millisecond.
PRIMARY KEY, UNIQUE and FOREIGN KEY columns get indexes automatically. In InnoDB the primary key is the clustered index; other indexes store the primary key to reach the row.
CREATE INDEX name ON table (cols),DROP INDEX name ON table,SHOW INDEX FROM table.Use
EXPLAIN:type(const, ref, range good; ALL bad),key,rows,Extra.Indexes are not used for functions on the column, leading-wildcard LIKE, or string columns compared with numbers.
Composite indexes follow the leftmost prefix rule; column order matters. They can also avoid sorting.
A covering index answers a query from the index alone ("Using index").
Indexes cost storage and write speed, and low-selectivity indexes can even slow reads. Index for real, frequent queries.
Next, in Common Table Expressions (WITH), we return to writing queries: we will learn how to break a complex query into readable named steps, and how to write recursive queries that walk a hierarchy like our employee-manager tree.