Ask MySQL for one customer's orders from a 50,000-row table that has no suitable index, and it reads all 50,000 rows to find the ten that match. An index is a separate, sorted structure that lets MySQL jump straight to those ten. This page builds such a table, runs EXPLAIN before and after adding indexes, and uses the output to show how each index type works.
Build a table big enough to notice
SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
city VARCHAR(20) NOT NULL,
status VARCHAR(10) NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO orders (customer_id, city, status, order_date, amount)
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)
SELECT 1 + (n * 7919) % 5000,
ELT(1 + n % 5, 'Pune','Jaipur','Nagpur','Bhopal','Vizag'),
ELT(1 + n % 4, 'placed','shipped','delivered','cancelled'),
DATE '2025-01-01' + INTERVAL (n % 640) DAY,
100 + (n * 37) % 9000
FROM seq;
ANALYZE TABLE orders;
That gives 50,000 orders spread across 5,000 customers, 5 cities and about 21 months. The rows figures in EXPLAIN are estimates, so yours may differ slightly.
The primary key is already an index
In InnoDB the table is a B-tree sorted by the primary key (a "clustered index"), so WHERE order_id = 42 is fast from the start. Every other index is a secondary index. It stores the indexed columns plus the primary key, and MySQL uses that key to fetch the rest of the row.
Before: a full table scan
EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 1234;
| type | possible_keys | key | rows | filtered | Extra |
|---|---|---|---|---|---|
| ALL | NULL | NULL | 50020 | 10.00 | Using where |
type = ALL with key = NULL means every row is read. (Only the useful columns of the EXPLAIN output are shown here and below.)
After: a single-column index
CREATE INDEX idx_customer ON orders (customer_id);
EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 1234;
| type | possible_keys | key | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| ref | idx_customer | idx_customer | const | 10 | 100.00 | NULL |
The estimate dropped from 50,020 rows to 10. type = ref means "look up matching rows through the index". The gap between the two grows with the table: a scan gets slower in line with the row count, while a B-tree lookup only adds a level now and then.
Composite indexes and the leftmost-prefix rule
CREATE INDEX idx_city_date ON orders (city, order_date);
EXPLAIN SELECT COUNT(*) FROM orders WHERE city = 'Pune' AND order_date >= '2026-01-01';
| type | key | key_len | rows | Extra |
|---|---|---|---|---|
| range | idx_city_date | 85 | 4290 | Using where; Using index |
A composite index is sorted by city first, then by order_date within each city, like a phone book sorted by surname and then first name. An equality on the first column plus a range on the second is the ideal case. "Using index" means the query was answered from the index alone, without touching the table rows. That is a covering index.
Now filter on the second column only:
EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date >= '2026-08-01';
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_city_date | 16671 | Using where; Using index for skip scan |
Normally an index cannot be used without its leftmost column. Here MySQL 8.0's skip scan works around it, because city has only five distinct values: it effectively runs one range scan per city. With thousands of distinct leading values, skip scan stops being worthwhile. Do not design around it. Put the column you filter by equality first.
Wrapping the column in a function hides it from the index
EXPLAIN SELECT order_id FROM orders WHERE YEAR(order_date) = 2026 AND city = 'Pune';
| type | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|
| ref | idx_city_date | 82 | const | 17998 | Using where; Using index |
key_len fell from 85 to 82 bytes: only the city part of the index is used, and every Pune row is checked against YEAR(). Writing the condition as order_date >= '2026-01-01' AND order_date < '2027-01-01' brings back the range on the date part. More patterns like this are in how to optimise MySQL queries.
Checking what exists
SHOW INDEX FROM orders;
| Key_name | Seq_in_index | Column_name | Cardinality | Index_type |
|---|---|---|---|---|
| PRIMARY | 1 | order_id | 50020 | BTREE |
| idx_customer | 1 | customer_id | 5050 | BTREE |
| idx_city_date | 1 | city | 5 | BTREE |
| idx_city_date | 2 | order_date | 640 | BTREE |
Cardinality is the estimated number of distinct values. On its own, an index on city (5 values) would rarely help, because each value matches a fifth of the table. Indexes pay off on selective columns, or as the leading part of a composite index.
Other index types
| Type | Created with | Use it for |
|---|---|---|
| Unique | CREATE UNIQUE INDEX / UNIQUE constraint | Lookups that must also reject duplicates (email, SKU) |
| Full-text | FULLTEXT KEY ft (body) | Word search in text with MATCH ... AGAINST; see the full-text search tutorial |
| Prefix | INDEX (url(100)) | Long VARCHAR/TEXT columns |
| Functional | INDEX ((LOWER(email))) | Queries that must filter on an expression (8.0.13+) |
| Spatial | SPATIAL INDEX | Geometry columns |
InnoDB also maintains an internal "adaptive hash index" on its own. You do not create hash indexes on InnoDB tables yourself.
The cost side
- Every
INSERT,UPDATEof an indexed column, andDELETEmust update each index. A table with ten indexes writes noticeably slower than one with three. - Indexes take disk space and buffer pool memory.
- Duplicate indexes add cost with no benefit. An index on
(city)is redundant if(city, order_date)exists. - Before dropping an index you suspect is unused, make it invisible (
ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;) and watch for slow queries.
To understand the physical layout in more depth, read clustered vs non-clustered indexes. For reading every column of a plan, see the EXPLAIN statement guide.