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’;

Post a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Blog is powered by WordPress and designed by Chico Web Design