Calculate MySQL Database Sizes Using SQL Query

Calculate MySQL Database Sizes Using SQL Query

Here is a quick and easy way to get the sizes of your databases in MySQL, useful when you don’t have PHPMyAdmin and just want to compare two databases (live and development for example).

Calculate size of all databases:

SELECT table_schema AS ‘Database Name’, CONCAT(SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),2)),’ MB’) AS “Database Size” FROM information_schema.tables GROUP BY table_schema;

Calculate the sizes of all the tables inside a certain database (replace DATABASE_NAME with your database name):

SELECT table_schema AS ‘Database Name’, table_name AS ‘Table Name’, CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),2),’ MB’) AS ‘Size’ FROM information_schema.tables WHERE table_schema = ‘DATABASE_NAME’;