MySQL: Get size of all databases and tables

This article shows SQL query to get size (in MB) of all MySQL databases. It also shows the query to get the size (in MB) of all the tables present in any particular database.

Get size (in MB) of all MySQL databases

SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

The result will be displayed something like below:

Database Name	Database Size in MB
database-1	0.38486862
database-2	26.86670208
my-db	0.06250000
test	0.09375000

Get size (in MB) of all tables of a particular database

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;

Here, we need to replace “database_name” with the name of the database whose table’s size we are going to find.

Suppose, we have a database named “test” and we want to find the size of all the tables in that database, then the query to do so will be:

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "test" ORDER BY (data_length + index_length) DESC;

The result will be displayed something like below:

Table	Size (MB)
tags	0.02
publishing	0.02
orders	0.02

Hope this helps.
Thanks.