#What an index is
AddedAn index is a second, sorted copy of some of a table's columns, with a pointer back to each full row. Without one, the only way to answer WHERE customer_id = 4242 is to read every row and check it — a full table scan. With one, MySQL can jump straight to the entries for 4242, because they sit next to each other in sorted order.
The mental model is the index at the back of a book. To find every page that mentions "deadlock" you don't read the whole book; you look the word up in the sorted index, which lists page numbers, and turn to those pages. The index costs something: extra pages to print, and every new edition has to update it. Database indexes have the same two costs — disk space, and slower writes, because every INSERT, UPDATE of an indexed column and DELETE must also update every index.
The sample schema from chapter 01 is far too small for indexes to matter (the optimizer happily scans eight rows). So this chapter uses two generated tables in the ch09 database: big_customers (50,000 rows) and big_orders (500,000 rows). Every timing below comes from them.
Now the main example: the same lookup before and after adding an index. EXPLAIN ANALYZE (covered fully in its own topic) runs the query and reports what each step really did and how long it took.
EXPLAIN ANALYZE
SELECT id, status, ordered_at, total FROM big_orders WHERE customer_id = 4242\G
CREATE INDEX idx_customer ON big_orders (customer_id);
EXPLAIN ANALYZE
SELECT id, status, ordered_at, total FROM big_orders WHERE customer_id = 4242\G*************************** 1. row ***************************
EXPLAIN: -> Filter: (big_orders.customer_id = 4242) (cost=51291 rows=49913) (actual time=1.49..55.5 rows=10 loops=1)
-> Table scan on big_orders (cost=51291 rows=499125) (actual time=0.362..47.1 rows=500000 loops=1)
1 row in set (0.056 sec)
Query OK, 0 rows affected (0.808 sec)
Records: 0 Duplicates: 0 Warnings: 0
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on big_orders using idx_customer (customer_id = 4242) (cost=10.5 rows=10) (actual time=0.081..0.0814 rows=10 loops=1)
1 row in set (0.000 sec)- First run, no index: the plan is a Table scan that reads all
rows=500000, with a Filter on top that keeps the 10 rows for customer 4242. It took about 55 ms. Every one of the 500,000 rows was read and compared, just to return 10. - The optimizer's guess under the filter,
rows=49913, is 10 % of the table: with no index and no histogram oncustomer_idit has no idea how many rows match, so it uses a fixed guess. Compare it withactual … rows=10. CREATE INDEXreads the table once, sorts the 500,000(customer_id, id)pairs and writes them as a new B+tree. It took 0.8 s — a one-off cost.- Second run: the plan is now a single Index lookup using
idx_customer. MySQL walks down the tree to the first entry for 4242, reads the 10 adjacent entries, and fetches those 10 rows. Actual time: 0.08 ms — roughly 700 times faster, and the gap grows with the table, because a tree lookup costs a few page reads whether the table has 500 thousand rows or 500 million. - The estimate is now exact (
rows=10) because an index also gives the optimizer statistics about how the values are distributed.
Every index is paid for on every write. Here the same 500,000 rows are copied into a table with no secondary indexes and into one with four:
CREATE TABLE copy_0_idx LIKE big_orders;
ALTER TABLE copy_0_idx DROP INDEX idx_customer;
CREATE TABLE copy_4_idx LIKE copy_0_idx;
ALTER TABLE copy_4_idx
ADD INDEX (customer_id), ADD INDEX (status), ADD INDEX (ordered_at), ADD INDEX (total);
INSERT INTO copy_0_idx SELECT * FROM big_orders;
INSERT INTO copy_4_idx SELECT * FROM big_orders;
ANALYZE TABLE copy_0_idx, copy_4_idx;
SELECT table_name, ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb
FROM information_schema.tables WHERE table_schema = 'ch09' AND table_name LIKE 'copy%';Query OK, 500000 rows affected (0.826 sec)
Records: 500000 Duplicates: 0 Warnings: 0
Query OK, 500000 rows affected (2.270 sec)
Records: 500000 Duplicates: 0 Warnings: 0
+------------+---------+----------+
| TABLE_NAME | data_mb | index_mb |
+------------+---------+----------+
| copy_0_idx | 21.5 | 0.0 |
| copy_4_idx | 21.5 | 34.1 |
+------------+---------+----------+CREATE TABLE … LIKEcopies the structure including indexes, socopy_0_idxdropsidx_customerto start from the primary key only.- Loading 500,000 rows into the bare table took 0.83 s; into the table with four secondary indexes it took 2.27 s — about 2.7 times slower (a second run gave 0.85 s and 2.08 s). Each row now means five B+tree inserts instead of one.
- The four indexes take 34.1 MB, more than the 21.5 MB of data itself. Indexes compete with data for the buffer pool, so an unused index also makes useful pages fall out of memory sooner.
data_lengthhere is the clustered index (the table itself, see Clustered and secondary indexes in InnoDB);index_lengthis the sum of the secondary indexes.
Where indexes pay off, and where they don't:
- Use them for columns in
WHERE,JOIN … ON,ORDER BYandGROUP BYof frequent queries, especially when the filter matches a small fraction of rows. - Think twice on write-heavy tables (logging, event ingestion), on tiny tables (a scan of a few pages is as fast as a lookup), and on columns where almost every row has the same value (see Selectivity and cardinality).
- Primary and unique keys are indexes too:
PRIMARY KEYandUNIQUEalways create one, and InnoDB creates an index for a foreign key if a suitable one doesn't exist.