How to calculate the size of a MySQL database

Many times we need to calculate the size of a MySQL database through code for purpose like database backup or to check how fast the database is growing for a certain application. The database size can be easily ascertained using phpmyadmin or other desktop tools, but that requires you to manually launch the tool and check. Below we will see how to do the same through a query.

Database sizes are stored in the MySQL ‘INFORMATION_SCHEMA’ database within the ‘TABLES’ table. Information pertaining to all the databases on the MySQL server is stored in this database. Two specific columns in the ‘TABLES’ table allows you to calculate the database size – ‘DATA_LENGTH’ and ‘INDEX_LENGTH’. One such query is given below.

# Here we are calculating size for a database named 'wordpress'
 
SELECT  SUM(((DATA_LENGTH + INDEX_LENGTH)/1024/1024)) AS "MB"
        FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA = "wordpress";
# This query will return something like the following.
 
+------------+
| MB         |
+------------+
| 0.97319508 |
+------------+

If you want to get size in Kilobytes than you will need to remove one of the 1024 divisors.

# Here we are calculating size for a database named 'wordpress'
 
SELECT  SUM(((DATA_LENGTH + INDEX_LENGTH)/1024)) AS "KB"
        FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA = "wordpress";

To calculate sizes for all the database on your server use the following.

SELECT TABLE_SCHEMA as 'Database', 
       SUM(((DATA_LENGTH + INDEX_LENGTH)/1024)) AS "KB"
       FROM INFORMATION_SCHEMA.TABLES
       GROUP BY table_schema