Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
You may get a more informative answer if you can share with us the data types of the primary and secondary key. – kmarsh Nov 22 '10 at 23:18

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.

share|improve this answer
The primary key is a big int and the secondary index is a big int. We ran optimize table on that table and now it's the proper size (slighly smaller than the table) – Jon Nov 23 '10 at 13:58

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.