Monthly Archives: February 2014

How to calculate a specific InnoDB index size ?

Posted on February 4, 2014 by aadant

MySQL provides commands to see the overall index size versus the data size. One of them is “show table status” :

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4186170
 Avg_row_length: 34
    Data_length: 143310848
Max_data_length: 0
   Index_length: 146030592
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2014-02-04 15:40:54
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show table status like 't'\G

*************************** 1. row ***************************

Name: t

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 4186170

Avg_row_length: 34

Data_length: 143310848

Max_data_length: 0

Index_length: 146030592

Data_free: 6291456

Auto_increment: NULL

Create_time: 2014-02-04 15:40:54

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

So here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates : Data_length: 143310848, … Continue reading →

Posted in tip | Leave a comment

Monthly Archives: February 2014

How to calculate a specific InnoDB index size ?

Recent Posts

Recent Comments

Archives

Categories

Meta