How to Get True Size of MySQL Database?

Question

I would like to know how much space does my MySQL database use, in order to select a web host. I found the command SHOW TABLE STATUS LIKE 'table_name' so when I do the query, I get something like this:

Name       | Rows | Avg. Row Length | Data_Length | Index Length
----------   ----   ---------------   -----------   ------------
table_name   400          55            362000        66560

• numbers are rounded.

So do I have 362000 or 400*362000 = 144800000 bytes of data for this table? And what does Index Length mean? Thanks !

Answer 1

SUM(Data_free) may or may not be valid. It depends on the history of innodb_file_per_table. More discussion is found here.

Answer 2

Basically there are two ways: query DB (data length + index length) or check files size. Index length is related to data stored in indexes.

Everything is described here:

http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/

Answer 3

If you want to find the size of all MySQL databases, us this command, it will show their respective sizes in megabytes;

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

If you have large databases, you can use the following command to show the result in gigabytes;

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;

If you want to show the size of only a specific database, for example YOUR_DATABASE_NAME, you could use the following query;

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='YOUR_DATABASE_NAME' GROUP BY table_schema;

Answer 4

If you use phpMyAdmin, it can tell you this information.

Just go to "Databases" (menu on top) and click "Enable Statistics".

You will see something like this:

This will probably lose some accuracy as the sizes go up, but it should be accurate enough for your purposes.