13g10n
Contact me
Home

Looking at table sizes in MySQL

I decided to record a useful SQL code that I have used myself more than once. Relatively often there is a need to estimate the size of tables in the current database, and convenient GUIs that immediately come to mind after posing the problem are not always at hand.

In general, we only need to run one simple query, which is more than enough:

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

As a result, we get a list of tables and their sizes in MB, sorted in descending order:

Terminal 
+------------------------------------+-----------+
| Table                              | Size (MB) |
+------------------------------------+-----------+
| user_timeline                      |    193.20 |
| user_profile                       |     98.72 |
| user                               |     32.55 |
...

This is very useful information both for general understanding and for finding areas of potential optimizations.

MySQL