Mysql Database size calculation in MB

These are some small queries to find the total disk space used by the mysql database.

1. Get the Size of all the tables as well are databases.

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;


2. Get the size of specific tables in the database.

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like 'testdb';

Where testdb is the database for which you want to know the details.

3. Get the size of the entire DB

select CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where table_schema like 'testdb';



Another query to display the above result would be

4. display the disk size of all the databases

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;







The 3'rd query would not give you a correct result for large database in AMAZON RDS at the time of writing. I had to use the 4'th query to obtain a approximate value.