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

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 |
+----------------------------------------+------------+