Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ?

I know I can select the index_length from information_schema.tables, but this gives the total size of all indexes in the table.

Let's suppose I have a table with PK index, several FK indexes and unique constraints, etc. Can I find out the size of one of those indexes only? Let's say, I'd like to know the size of the PK index only.

share|improve this question

migrated from stackoverflow.com Feb 6 '12 at 19:18

This question came from our site for professional and enthusiast programmers.

    
innodb or myiasm? –  BCoates Mar 7 '12 at 21:48
    
InnoDB. I've also update the original question with this, thanks. –  Timur Mar 8 '12 at 8:56
add comment

1 Answer 1

up vote 3 down vote accepted

There appears to be no way to get the answer out of mysql directly; however you can do a pretty good job of estimating it:

For innodb, PRIMARY index is part of the data itself and shows up in data_length. If a table has no other indexes index_length is 0.

For every row in your table, every non-PRIMARY (UNIQUE or INDEX) index stores

  • 6 bytes overhead
  • every column indexed
  • every column in PRIMARY not already in the index
  • 1 bit per possible NULL column, rounded up to the byte

The size of a column in an index is the space the actual data takes up plus 1 or 2 bytes of length per VARCHAR/TEXT/BLOB. NULLs take no space.

There is an additional overhead of about 1.4x to 2.8x for the B-tree pages and non-full leaf pages. In my databases it looks to be around 2x usually.

FKs should not have any space overhead beyond the index they require.

mysql docs - source

share|improve this answer
    
Thanks, this is great! This is very detailed indeed. –  Timur Mar 9 '12 at 8:58
add comment

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.