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

John John (304)
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 |
+----------------------------------------+------------+
John John (304)
0

In this guide I'm going to describe how to get started with PHP and MySQL.