As part of administering our customer MySQL databases we often want to know the size of databases, tables and indexes. These are a few of the commands we commonly use on MySQL and Percona 5.6. We have shown example output from real databases, but have changed database and table names to protect the innocent.

Database Sizes

If you want to know the size of all databases in gigabytes (GiB):

SELECT table_schema AS "Database Name", ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) AS "Size (GiB)" FROM information_schema.tables GROUP BY table_schema;
+--------------------+------------+
| Database Name      | Size (GiB) |
+--------------------+------------+
| information_schema |        0.0 |
| ocean              |      105.4 |
| mysql              |        0.0 |
| performance_schema |        0.0 |
+--------------------+------------+
4 rows in set (0.05 sec)

If you want to know the size of all databases in megabytes (MiB):

SELECT table_schema AS "Database Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS "Size (MiB)" FROM information_schema.tables GROUP BY table_schema;
+--------------------+------------+
| Database Name      | Size (MiB) |
+--------------------+------------+
| information_schema |        0.0 |
| ocean              |   107938.4 |
| mysql              |        2.3 |
| performance_schema |        0.0 |
+--------------------+------------+
4 rows in set (0.05 sec)

Tables and Index Sizes

The following command finds the size of data and indexes for all tables in the current database. The ordering is by the total size of the data and index: you can adjust the ORDER BY clause to sort by data or index length instead. This command can easily be adjusted to work for a named database by changing WHERE table_schema =.

SELECT table_name AS "Table", round((data_length / 1024 / 1024), 0) "Data (MiB)", round((index_length / 1024 / 1024), 0) "Index (MiB)" FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY (data_length + index_length) DESC;
+------------------------------+------------+-------------+
| Table                        | Data (MiB) | Index (MiB) |
+------------------------------+------------+-------------+
| salmon                       |      14911 |        1395 |
| haddock                      |      14691 |        1186 |
| herring                      |      10004 |        1036 |
| halibut                      |       9124 |        1275 |
...

Total Rows in Database

If you want a quick estimate of the number of rows in each database you can grab the information from the information_schema:

SELECT table_schema AS "Table", SUM(table_rows) "Rows" FROM information_schema.tables GROUP BY table_schema;
+--------------------+-----------+
| Table              | Rows      |
+--------------------+-----------+
| information_schema |      NULL |
| ocean              | 427419770 |
| mysql              |      7020 |
| performance_schema |     74026 |
+--------------------+-----------+
4 rows in set (0.70 sec)

NB. This is fast but isn’t exact; it’s the row count used by the optimiser.