MySQL “How big are my databases?”
By Dan S. on Apr. 19, 2008.
If you're like many of the MySQL users out there and do not have access to the MySQL command line, or perhaps you do have access to the MySQL command line and just want to know how to determine how big your databases and tables are, this is for you!
The INFORMATION_SCHEMA database contains a wealth of information regarding your database(s), but is only available for MySQL 5.0 and up. One way I like to query the INFORMATION_SCHEMA database will return the Database | Table | Size for each table you've got. Here's what the SQL looks like:
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;
Once you execute this SQL, you will get a nice list of your tables, along with their size, in MB. Feel free to adjust as necessary to retrieve additional data, or in different formats. If you have a format you really like, share it!
Category: MySQL