How to Get True Size of MySQL Database?

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 !

From S. Prakash, found at the MySQL forum:

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

Or in a single line for easier copy-pasting:

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

You can get the size of your Mysql database by running the following command in Mysql client

SELECT  sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2))  as "Size in GB"
FROM information_schema.TABLES
WHERE table_schema = "<database_name>"

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:

phpMyAdmin screenshot

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

if you want to find it in MB do this

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;

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/

How to Get True Size of MySQL Database?

Related

Recent Posts