What are some reasons why a MySQL index would be 12X the size of the table?

Question

So we have a table that has two indexes one on the primary key and one other index on another column on the table. The index size is currently 12X larger than the table itself. What are some reasons why this would happen? We've optimized the table before and it helped but it's growing again.

Thanks!

Answer 1

This size is not uncommon with indexes, especially for large text fields. While it might be indicative of a poor indexing strategy, text is notably more complex to index than numbers, especially if enabling substring searches not anchored to the beginning of the text field.

While a large index might seem slow by its nature, it can (or should be) navigated quite quickly due to its highly structured nature, and thus yield pointers to the correct data faster than an exhaustive search of the data itself.

Assuming they will continue to fit on disk, the real tests of your indexes are satisfactory search, update and addition times, especially as the amount of data scales. If these aspects continue to be adequate, a large index is tolerable.

Answer 2

Factors which may negatively influence the size of an index

• Distribution of data (i.e. if it's uniform or biased)
• Order of data being inserted/deleted/updated (i.e. if it's not random)
• For varchar's, the size and charset of the varchar
• For innodb, using an unnecessarily large datatype for the primary key will affect the contribution of each index as it's implicitly part of it

As you noted, re-writing the index (i.e. optimizing the table) can/will balance the index structure out again.