How big is my MySQL database?

1) To see how much disk space is the database is using:

mysql> SELECT SUM(data_length+index_length) FROM information_schema.tables
WHERE engine <> "MEMORY";
+-------------------------------+
| SUM(data_length+index_length) |
+-------------------------------+
|                    4151912352 |
+-------------------------------+
1 row in set (8.53 sec)

mysql>

2) To see all of the databases:

mysql> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+---------------+
| Database           | Size (MB)     |
+--------------------+---------------+
| financedb          |    0.09375000 |
| information_schema |    0.00976563 |
| mysql              |    5.63141537 |
| performance_schema |    0.00000000 |
| webappdb           | 1650.34472656 |
| devdb              |    0.25000000 |
+--------------------+---------------+
6 rows in set (0.54 sec)

mysql> 

3) To see how many tables there are in the database:

mysql> SELECT COUNT(*) FROM information_schema.tables WHERE engine <>
"MEMORY";
+----------+
| COUNT(*) |
+----------+
|      471 |
+----------+
1 row in set (0.04 sec)

mysql> SELECT COUNT(*) FROM information_schema.tables WHERE engine <> "MEMORY";

3) To see how large the tables are:

mysql> SELECT table_name,(data_length+index_length) tablesize FROM
information_schema.tables ORDER BY tablesize;
+-------------------------------------------------+-----------+
| table_name                                      | tablesize |
+-------------------------------------------------+-----------+
| field_data_field_sig_address                    | 219889664 |
| field_revision_field_sig_city                   | 226279424 |
| field_revision_field_sig_address                | 231538688 |
| field_revision_body                             | 703217664 |
+-------------------------------------------------+-----------+
481 rows in set (6.13 sec)

mysql>
Share: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Twitter
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • StumbleUpon

Leave a Reply

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