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

Tyler Tyler (291)
0

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/webdev60 guides

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 |
+----------------------------------------+------------+
Tyler Tyler (291)
0

You may find that you need to execute SQL statements that exist in a .sql file. There are a few different ways to do this depending on what you want to accomplish and how to sql file is written.