Reporting sizes of databases and tables in MySQL

It's useful to know how much disk space individual tables or entire databases consume. Particularly if resources on the server are limited.

The Following SQL statement will return the size of each database in descending order:

SELECT
  table_schema AS `Database`, 
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `MB`
FROM information_schema.TABLES 
GROUP BY table_schema
ORDER BY `MB` desc 
;

It's output will be in the following format:

+--------------------------+-------+
| Database                 | MB    |
+--------------------------+-------+
| A_DB                     | 14.91 |
| mysql                    |  0.67 |
| information_schema       |  0.01 |
| performance_schema       |  0.00 |
+--------------------------+-------+
4 rows in set (0.06 sec)

Individual table sizes can be checked by using the following SQL statement and specifying the database that should be examined.

SELECT
  table_name AS `Table`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `MB`
FROM information_schema.TABLES
WHERE table_schema = "mysql"
ORDER BY `MB` DESC
;

The output of the table sizes will be as follows (using the mysql database as an example):

+---------------------------+------+
| Table                     | MB   |
+---------------------------+------+
| help_topic                | 0.48 |
| help_keyword              | 0.12 |
| help_relation             | 0.03 |
| proxies_priv              | 0.01 |
...
| help_category             | 0.00 |
| time_zone_transition_type | 0.00 |
| general_log               | 0.00 |
| time_zone_transition      | 0.00 |
| func                      | 0.00 |
| time_zone_name            | 0.00 |
+---------------------------+------+
24 rows in set (0.01 sec)

Last updated: 15/02/2020