Why Adding a Simple Index Can Dramatically Increase MySQL Table Size

Keeping only essential indexes is a well‑known best practice, but the cost of adding a new index can sometimes far exceed expectations. The author analyzes a real‑world MySQL table (t1) with three columns (a, b, c) where the primary key is a composite of a and c .

Initial table statistics show a 5 M‑row table occupying about 508 MiB on disk and an Index_length of 0. After running EXPLAIN SELECT * FROM t1 WHERE b=10 , the optimizer chooses a full table scan because column b lacks an index.

Adding a secondary index on b with ALTER TABLE t1 ADD KEY(b); makes the query fast, but the table file grows from 508 MiB to 908 MiB – a 79 % increase despite indexing only a tiny INT column.

The reason is InnoDB’s secondary‑index design: each secondary index entry stores the indexed column(s) **plus** the full primary‑key columns. In this case the secondary index on b also stores a and c , effectively duplicating the entire row.

Using the innodb_ruby tool, the author inspects page‑level statistics before and after the alteration, showing that the secondary index consumes many more pages than the primary index because of the duplicated primary‑key data.

To reduce space, the author rewrites the table with a smaller primary key ( id INT AUTO_INCREMENT ) and retains the secondary index on b . After the change the .ibd file shrinks from 908 MiB to 608 MiB.

Further adding a composite index on the large a and c columns inflates the file to over 1 GiB, illustrating that indexing wide columns can dramatically increase storage.

The key takeaway is that larger primary keys cause secondary indexes to consume proportionally more space; therefore, keep primary keys narrow (avoid UUIDs) and be mindful of index design to control disk usage.