Wednesday, May 6, 2015

[MySQL] Calculate database size (Part I)

There are two ways to calculate the size of the databases on a MySQL installation: using SQL script and measuring the MySQL stored data files (ibdata).

In this tutorial we will emphasise in the SQL script way.

With the following SQL script we can get the size of the entire database:
SELECT table_schema "Database Name", SUM( data_length + index_length) / 1024 / 1024 "Database Size in MB" FROM information_schema.tables GROUP BY table_schema;
To get the detailed size of each table on a database, execute the following SQL script:
SELECT table_name, table_rows, data_length, index_length, ROUND(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.tables WHERE table_schema = "schema_name";