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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     lt database name gt

User · Answer

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

User · Answer

If you are using MySql Workbench  its very easy to get all details of Database size  each table size  index size etc    Right Click on Schema Select Schema Inspector option   It Shows all details of Schema size  Select Tables Tab to see size of each table    Table size diplayed in Data Lenght column

User · Answer

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

User · Answer

SUM Data free  may or may not be valid   It depends on the history of innodb file per table   More discussion is found here

[mysql] How to Get True Size of MySQL Database?

Examples related to mysql

Examples related to database

Examples related to size

Examples related to byte

Examples related to storage