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
So do I have 362000 or 400*362000 = 144800000 bytes of data for this table? And what does Index Length mean? Thanks !
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;
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>"
None of the answers include the overhead size and the metadata sizes of tables.
Here is a more accurate estimation of the "disk space" allocated by a database.
SELECT ROUND((SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150, 2) AS MegaBytes 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:
This will probably lose some accuracy as the sizes go up, but it should be accurate enough for your purposes.
MySQL Utilities by Oracle have a command called mysqldiskusage that displays the disk usage of every database: https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldiskusage.html
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;
SUM(Data_free)
may or may not be valid. It depends on the history of innodb_file_per_table
. More discussion is found here.
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;
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/
Source: Stackoverflow.com