Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

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

2 Answers 2

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

 
discard

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.