Posted by Kyle Hankinson January 12, 2023
When working with MySQL databases, it's important to understand the size of the tables in order to optimize performance and storage. In this article, we will discuss how to retrieve the size of all tables in a MySQL database using a simple SQL query.
To retrieve the size of all tables in a MySQL database, we can use the information_schema.TABLES table, which contains information about all tables in the database. The information_schema.TABLES table has two columns, data_length and index_length, that represent the size of the data and index for each table, respectively.
Here is an example of a query that shows the size of all tables in a MySQL database:
SELECT table_name AS 'Table Name', round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_name
;