MySQL: Get the exact size of a database by SQL query

by Yang Yang on March 5, 2011

phpMyAdmin doesn’t show the exact size in MB when the size of your database exceeds 1GB. It just shows something like 4.2GB, truncating everything out in the 100MB precision. So is it possible to get the database size in MB in exact numbers using MySQL query?

Yes, it is:

SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like 'YOUR_DATABASE_NAME_HERE%' ;

Just change YOUR_DATABASE_NAME_HERE into the name of your database. And it would prints out something like this:

+------------+
| Size       |
+------------+
| 4906.79 MB |
+------------+
1 row in set (0.05 sec)

You can also get the size of a specific table. Read here: http://www.novell.com/communities/node/8706/check-mysql-database-size-using-sql-query

Comments on this entry are closed.

Previous post:

Next post: