MySQL Index

Last updated: October 19, 2026Oct 19, 2026 6 min read

An index is a separate, sorted data structure that helps MySQL find rows quickly, like the index at the back of a book. Without one, MySQL must read every row of a table to answer WHERE city = 'Kochi'. With one, it jumps straight to the matching rows. Indexes are the single most important tool for query performance.

The examples use a customers table with 5,000 generated rows, so you can see real differences in the query plans.

1. Why Indexes Matter

SELECT COUNT(*) AS customers FROM customers;

Result: 1 row

Without an index on city, MySQL scans the whole table (type = ALL) to find one city:

EXPLAIN SELECT id, first_name FROM customers WHERE city = 'Kochi';

Result: 1 row

CREATE INDEX idx_city ON customers (city);
ANALYZE TABLE customers;   -- refresh statistics
EXPLAIN SELECT id, first_name FROM customers WHERE city = 'Kochi';

Result: 1 row

With the index (type = ref), the estimated rows to read drop from about 5,000 to the few hundred customers who actually live in Kochi.

2. How a B-Tree Index Works

• Values are kept sorted in a balanced tree of 16 KB pages.
• A lookup reads only a few pages (usually 3–4 levels, even for millions of rows).
• Because the leaves are sorted and linked, ranges (BETWEEN, >, LIKE 'Ko%') and ORDER BY are fast too.
• B-tree is the default index type for InnoDB.

3. Clustered and Secondary Indexes

4. Index Types in MySQL

5. Composite Indexes and the Leftmost-Prefix Rule

A composite index on (city, last_name) is sorted by city, then by last name within each city, like a phone book sorted by city then surname. It can be used when a query uses the leftmost columns:

CREATE INDEX idx_city_last ON customers (city, last_name);
ANALYZE TABLE customers;

EXPLAIN SELECT id FROM customers WHERE city = 'Pune' AND last_name = 'Rao';

Both columns used

EXPLAIN SELECT id FROM customers WHERE last_name = 'Rao';

First column missing: skip scan

Without the first column, MySQL can only skip scan: it jumps through the index once per distinct city (12 here) looking for Rao. That works when the first column has few distinct values, but a separate index starting with last_name would be far better for frequent searches by surname.

6. Covering Indexes

If an index contains every column a query needs, MySQL answers from the index alone and never touches the table rows. EXPLAIN shows Using index:

DROP INDEX idx_city ON customers;   -- redundant: (city, last_name) starts with city
EXPLAIN SELECT city, last_name FROM customers WHERE city = 'Goa';

Result: 1 row

We dropped idx_city because idx_city_last already starts with city and serves the same lookups.

7. When an Index Can't Be Used

• A function or calculation on the column: WHERE YEAR(signup_on) = 2024.
• A leading wildcard: WHERE email LIKE '%@mail.example'.
• Comparing with a different type, e.g. a number against a VARCHAR column.
• Low selectivity: if most rows match, a full scan is cheaper and the optimizer picks it.

CREATE INDEX idx_signup ON customers (signup_on);

EXPLAIN SELECT id FROM customers WHERE YEAR(signup_on) = 2024;
EXPLAIN SELECT id FROM customers WHERE signup_on >= '2024-01-01' AND signup_on < '2025-01-01';

Function on the column: every index entry is read

Range on the bare column: only the 2024 range is read

8. The Cost of Indexes

• Writes get slower: every INSERT, UPDATE and DELETE must update each index.
• Disk and memory: indexes take space and compete for the buffer pool.
• Redundant indexes waste both: (city) is redundant if (city, last_name) exists.

SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024) AS size_kb
FROM mysql.innodb_index_stats
WHERE database_name = 'crm' AND table_name = 'customers' AND stat_name = 'size'
ORDER BY index_name;

Result: 3 rows

9. Practical Guidelines

• Index columns used in WHERE, JOIN … ON and ORDER BY of frequent queries.
• Index every foreign key column (InnoDB does this automatically).
• Prefer composite indexes that match your real queries over many single-column ones.
• Check with EXPLAIN before and after; measure, don't guess.
• Remove unused indexes; sys.schema_unused_indexes lists them on a running server.

10. Summary

• An index is a sorted B-tree that lets MySQL find rows without scanning the table.
• InnoDB's primary key is the clustered index; secondary indexes point to it.
• Composite indexes follow the leftmost-prefix rule; covering indexes avoid table lookups.
• Keep indexed columns bare in conditions.
• Indexes speed reads but cost writes and space, so create the ones your queries need.