Clustered Indexes

Clustered Indexes and Secondary Index Lookups

In InnoDB the table is its primary key B+tree, the clustered index. Without a primary key, InnoDB clusters on the first all-NOT NULL UNIQUE index, or on a hidden 6-byte row id (GEN_CLUST_INDEX; Primary Keys). Every other index is secondary: its entries hold the indexed columns plus the primary key, and reaching the row takes a second descent.

A secondary index lookup: find the primary keys, then fetch each row from the clustered index (values schematic)

A secondary index, and the primary key hidden in its entriesSQL

CREATE INDEX idx_customer ON big_orders (customer_id);
SELECT COUNT(*), SUM(total) INTO @n, @s FROM big_orders WHERE customer_id = 4242;
SELECT stat_name, stat_value, stat_description FROM mysql.innodb_index_stats
WHERE database_name = 'shop' AND table_name = 'big_orders'
  AND index_name = 'idx_customer' AND stat_name LIKE 'n_diff%';
SELECT COUNT(*), SUM(total) INTO @n, @s FROM big_orders FORCE INDEX (idx_customer)
WHERE customer_id <= 5000;

Output

Query OK, 0 rows affected (2.439 sec)
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 1 row affected (0.000 sec)
+--------------+------------+------------------+
| stat_name    | stat_value | stat_description |
+--------------+------------+------------------+
| n_diff_pfx01 |      49961 | customer_id      |
| n_diff_pfx02 |    1000064 | customer_id,id   |
+--------------+------------+------------------+
2 rows in set (0.000 sec)
Query OK, 1 row affected (0.224 sec)

The customer query fell from 0.17 s to under a millisecond. n_diff_pfx02 counts (customer_id, id) pairs, exposing the hidden column: a CHAR(36) UUID key adds 36 bytes or more to every secondary entry. Many matches mean many descents: forced (Hints), the index took 0.22 s for 100,000 orders, no faster than the optimizer's own scan (Covering Indexes). With two million rows and a cold cache, the same test took 8.4 s against 0.55 s.

MENU

Clustered Indexes