:::: MENU ::::

Calculate Database/Table size in MySQL

  • Aug 11 / 2016
  • 0
Database

Calculate Database/Table size in MySQL

MySQL is an open-source relational database management system (RDBMS) available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements.

Thanks to some few functions, it’s possible to retrieve important information about database size and/or table size for a MySQL instance.

Size for all databases

SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024
"Database Size (MB)"  FROM information_schema.TABLES GROUP BY table_schema;
+---------------------+--------------------+
| Database Name       | Database Size (MB) |
+---------------------+--------------------+
| db_1                |       113.51562500 |
| db_2                |       172.59375000 |
| db_3                |        48.70312500 |
| db_4                |      1830.53125000 |
| db_5                |       432.89062500 |
| information_schema  |         0.07812500 |
| mysql               |         1.03164101 |
| performance_schema  |         0.00000000 |
+---------------------+--------------------+
8 rows in set (0.41 sec)

Size of tables for a specific database

SELECT table_name "Table Name", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2) "Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "db_4";
+-------------------------------+------------+-----------------+
| Table Name                    | Rows Count | Table Size (MB) |
+-------------------------------+------------+-----------------+
| table_1                       |        245 |            0.05 |
| table_2                       |          1 |            0.03 |
| table_3                       |        537 |            0.11 |
| table_4                       |          1 |            0.05 |
| table_5                       |         57 |            0.05 |
| table_6                       |          0 |            0.05 |
| table_7                       |          2 |            0.05 |
| table_8                       |        584 |            0.13 |
| table_9                       |         48 |            0.03 |
| table_10                      |      12972 |            2.16 |
| table_11                      |        584 |            0.11 |
   [ ... ]
| table_50                      |          0 |            0.05 |
| table_51                      |     119864 |           16.03 |
| table_52                      |     146688 |            9.55 |
| table_53                      |         48 |            0.05 |
| table_54                      |     852591 |          208.30 |
| table_55                      |     138786 |           66.64 |
| table_56                      |         47 |            0.08 |
| table_57                      |        169 |            0.09 |
| table_58                      |          0 |            0.03 |
+-------------------------------+------------+-----------------+
58 rows in set (0.00 sec)

For a complete documentation, please refer to the official documentation on their website https://www.mysql.com

Comments are closed.

Question ? Contact