Mysql – How do we determine data_length + index_length as database size in MySQL

database-sizeMySQL

I am calculating my InnoDB database's size with the following syntax:

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES
GROUP BY table_schema;

As per MySQL documentation, data_length column gives the clustered index length in pages. But I have not created an index on that table. How to calculate exact total space, used space and free space for a table?

Best Answer

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via your query, take a look at :

select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES

file_size (which is the logical file size on disk) and allocated_size (which is space allocated for this file and can be significantly smaller) more details here : https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/

Related Question