How to get the size of all tables in a MySQL database

If you're using MySQL, at some point you'll want to measure the size of your tables. This can help you identify issues, track table growth over time, estimate disk space requirements, etc. In this guide, I'll show you how to find the size of all tables in a MySQL database.

1

The following query will list all tables and their respective sizes from largest to smallest:

SELECT 
     table_name,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC

Replace "database_name" with the name of your database, or exclude the WHERE clause altogether to get data for all databases.

The output looks something like this:

+----------------------------------------+------------+
| table_name                             | Size in MB |
+----------------------------------------+------------+
| table1                                 |       5.73 |
| table2                                 |       4.59 |
| table3                                 |       2.64 |
| table4                                 |       1.59 |
+----------------------------------------+------------+