3

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!

1
  • You may get a more informative answer if you can share with us the data types of the primary and secondary key.
    – kmarsh
    Commented Nov 22, 2010 at 23:18

2 Answers 2

0

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.

1
  • 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
    Commented Nov 23, 2010 at 13:58
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .