Skip to content

Check MySQL database sizes

If you want to find out the size that MySQL databases use, you can issue the following query to list all the databases, with their respective sizes in megabytes;

mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| database           | size in MB     |
+--------------------+----------------+
| test1              | 14651.58056259 | 
| test2              |  1262.16237235 | 
| test3              | 32151.64254152 | 
| information_schema |     0.00390625 | 
+--------------------+----------------+
4 rows in set (0.02 sec)

If you have large databases (as the example above), you probably want to show the result in gigabytes;

mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------------+
| database           | size in GB      |
+--------------------+-----------------+
| test1              | 14.308184143156 | 
| test2              |  1.232580441748 | 
| test3              | 31.398088419453 | 
| information_schema |  0.000003814697 | 
+--------------------+-----------------+
4 rows in set (0.06 sec)

And you can apply WHERE-parameters as in a normal SELECT. So, if you want to show the size of only a specific database, for example ‘test3’, you could use the following query;

mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='test3' GROUP BY table_schema;
+------------+-----------------+
| database   | size in GB      |
+------------+-----------------+
| test3      | 31.398088419453 | 
+------------+-----------------+
1 row in set (0.03 sec)

4 Comments

  1. Bart Bart

    Great info. Very helpful. Thanks

  2. jayden jayden

    just what i was looking for – thanx :)

  3. jayden jayden

    do you know, how to show the creation date of the dbs instead of size, too? thx

  4. jayden jayden

    got it, just change the line you provided like the following:

    SELECT table_schema “database”, CREATE_TIME FROM information_schema.TABLES GROUP BY table_schema;

Leave a Reply

Your email address will not be published. Required fields are marked *