Get Database and Table Sizes in MySQL

MySQL offers various metadata about databases and tables. When working with MySQL, monitoring database and table sizes is essential for optimizing performance and managing storage. This tutorial demonstrates how to get database and table sizes in MySQL.

1. Sizes of all databases and tables

The following query retrieves the sizes of all tables across all databases, sorting them in descending order by size:

SELECT TABLE_SCHEMA AS `database`, TABLE_NAME AS `table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size
FROM information_schema.TABLES
ORDER BY size DESC;

The total table size, including data and indexes, is determined using DATA_LENGTH + INDEX_LENGTH and converted to megabytes.

Output example:

+-----------+------------+------+
| database  | table      | size |
+-----------+------------+------+
| test_db   | products   | 5.52 |
| test_db2  | orders     | 4.02 |
| test_db2  | customers  | 2.67 |
| test_db   | categories | 1.84 |
+-----------+------------+------+

2. Sizes of tables for specific database

If you want to check the sizes of tables within a specific database (e.g., test_db), use the following query:

SELECT TABLE_NAME AS `table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test_db'
ORDER BY size DESC;

Output example:

+------------+------+
| table      | size |
+------------+------+
| products   | 5.52 |
| categories | 1.84 |
+------------+------+

3. Total Database Sizes

To get the total size of each database, sum up the sizes of all its tables:

SELECT TABLE_SCHEMA AS `database`,
ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY size DESC;

Output example:

+----------+------+
| database | size |
+----------+------+
| test_db  | 7.36 |
| test_db2 | 6.69 |
+----------+------+