Query Database and Table Size in MySQL

0
409
MySQL Database
MySQL Database

A nice thing about MySQL is that it stores everything inside information_schema, and we can make use of it to calculate database size and table size as necessary when monitoring.

To query database size in MB of all databases inside, execute following command:


SELECT 
    TABLE_SCHEMA "Database Name", 
    SUM(DATA_LENGTH + INDEX_LENGTH ) / 1024 / 1024 "Database Size (MB)"
FROM 
    information_schema.TABLES 
GROUP BY TABLE_SCHEMA;

The result will look like this:


+--------------------+--------------------+
| Database Name      | Database Size (MB) |
+--------------------+--------------------+
| information_schema |         0.00986211 |
| mysql              |         0.92094517 |
+--------------------+--------------------+
2 rows in set (0.01 sec)

Because the size is in bytes, so we have to divide by 1024 twice to get value in Megabyte. You can use ROUND() to round up the number.

Similarly, querying table size in a specific database is following the same syntax, just make a condition for table, like this:


SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "game_db"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

As you see, you can order the result as well.
What if you want to query table size of all databases? Here it is.


SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Just remove the WHERE condition, that’s all you have to do.