Find the size of each index in a MySQL table

Question

I am trying to figure out size of each index of a table.

SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table. However if a table has multiple indices (e.g. in an employee table) then emp_id, ename and deptno are 3 different indices for which I want sizes separately.

emp_id : xx Kb  
ename  : yy Kb  
deptno : zz Kb   

How can I get these?

Answer 1

You should query the following:

• INFORMATION_SCHEMA.STATISTICS
• INFORMATION_SCHEMA.COLUMNS

If the table was called mydb.mytable, just run the following:

SET @PowerOfTwo = 0;
SET @GivenDB = 'mydb';
SET @GivenTB = 'mytable';
SELECT COUNT(1) INTO @MyRowCount FROM mydb.mytable;
SELECT
    index_name,SUM(column_length * @MyRowCount) indexentry_length
FROM
(
    SELECT
        index_name,column_name,
        IFNULL(character_maximum_length,
        IF(data_type='double',8,
        IF(data_type='bigint',8,
        IF(data_type='float',4,
        IF(data_type='int',4,
        IF(data_type='mediumint',3,
        IF(data_type='smallint',2,
        IF(data_type='datetime',4,
        IF(data_type='date',3,
        IF(data_type='tinyint',1,1)
        ))))))))
    ) / POWER(1024,@PowerOfTwo) column_length
FROM
(
    SELECT
        AAA.index_name,AAA.column_name,
        BBB.data_type,coalesce(AAA.sub_part,BBB.character_maximum_length) AS character_maximum_length
        FROM
        (
            SELECT table_schema,table_name,index_name,column_name,sub_part
            FROM information_schema.statistics
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) AAA INNER JOIN
        (
            SELECT
                table_schema,table_name,column_name,
                character_maximum_length,data_type
            FROM information_schema.columns
            WHERE table_schema = @GivenDB AND table_name = @GivenTB
        ) BBB USING (table_schema,table_name,column_name)
    ) AA
) A GROUP BY index_name;

Give it a try !!!

CAVEAT #1

Please note the first line

SET @PowerOfTwo = 0;

Here is how the setting affects the output

0 : Bytes
1 : KiloBytes
2 : MegaBytes
3 : GigaBytes
4 : TeraBytes

CAVEAT #2

This does not take BTREE overhead and fragmentation into account.

Here is another post from someone else : Find out MySQL index size for a concrete index

That answer suggests a fudge factor of 1.4 to 2.8.

Therefore, whatever answer my queries produce, multiple it by 1.4 or 2.8 to factor in BTREE nodes and possible fragmentation. You should defragment the table before running my queries.

If your table is MyISAM:

OPTIMIZE TABLE mydb.mytable;

If your table is InnoDB:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;