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

Tyler Tyler (291)

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.

Posted in these interests:
h/mysql9 guides
h/webdev59 guides

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

     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 |