Search code examples
mysqlinnodbinformation-schema

How to find data length and index length of innodb tables in mysql 5.6 if innodb per file is not enabled

In mysql 5.6, We have innodb per file is disabled. I just want to know size of tables data and index . Please let me know how to get accurate data.

Solution

  • The same way you get data length and index length if innodb_file_per_table is enabled:

    SELECT data_length, index_length
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='InnoDB';

    Or if you want the total:

    SELECT SUM(data_length+index_length) AS total
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='InnoDB';

    The data length and index length are not different if you enable/disable innodb_file_per_table.

    What is counted differently is the data_free. If you have disabled innodb_file_per_table, the data_free of the whole tablespace will be reported for each row in INFORMATION_SCHEMA.TABLES. So you should not SUM() the data_free, because that will give you a misleading result. It will count the same data_free many times, equal to the number of InnoDB tables in the tablespace.