Skip to content
Jun 7 / jocke

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

leave a comment
  1. Bart / Jan 14 2013

    Great info. Very helpful. Thanks

  2. jayden / Jan 29 2015

    just what i was looking for – thanx :)

  3. jayden / Jan 29 2015

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

  4. jayden / Jan 29 2015

    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 Comment