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.

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?

share|improve this question
2  
Please don't cross-post. Now that you posted the question here, you can delete the identical SO one. –  ypercube Sep 11 '13 at 12:46
add comment

1 Answer

You should query the following:

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='tinyint',1,0)
        ))))))
    ) / POWER(1024,@PowerOfTwo) column_length
FROM
(
    SELECT
        AAA.index_name,AAA.column_name,
        BBB.data_type,BBB.character_maximum_length
        FROM
        (
            SELECT table_schema,table_name,index_name,column_name
            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;
share|improve this answer
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.