In this article, we will learn How to Check MySQL Database and Table Size?
MySQL is a Relational Database Management System, widely used as a database system for Linux systems. This article will help you to calculate the size of tables and databases in MySQL or MariaDB servers through SQL queries. MySQL stored all the information related to tables in a database in the information_schema database. We will use the information_schema table to find tables and databases size.
Check Single Database Size in MySQL
This query will calculate the size of the single database in the MySQL server. Please change ‘mydb‘ with your actual database name.
SELECT table_schema "Database Name", SUM( data_length + index_length)/1024/1024
"Database Size (MB)" FROM information_schema.TABLES where table_schema = 'mydb';
+---------------+--------------------+
| Database Name | Database Size (MB) |
+---------------+--------------------+
| mydb | 0.15625000 |
+---------------+--------------------+
1 row in set (0.04 sec)
Check All Databases Size in MySQL
This query will calculate the size of all databases in MySQL server.
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) |
+--------------------+--------------------+
| demodb | 0.15625000 |
| information_schema | 0.00976563 |
| mydb | 0.15625000 |
| mysql | 0.81098557 |
| performance_schema | 0.00000000 |
+--------------------+--------------------+
5 rows in set (0.01 sec)
Check Single Table Size in MySQL Database
This query will calculate the size of a single table in a database in MySQL server. Please change ‘mydb‘ with your actual database name and ‘table_one‘ with your actual table name.
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 = "mydb" AND table_name ="table_one";
+---------------------+------------+-----------------+
| Table Name | Rows Count | Table Size (MB) |
+---------------------+------------+-----------------+
| archive_one | 8 | 0.09 |
+---------------------+------------+-----------------+
1 row in set (0.00 sec)
Check All Tables Size in MySQL Database
This query will calculate the size of all tables in a database in MySQL server. Please change ‘mydb‘ with your actual database name. It will also list the number of rows in each table.
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 = "mydb";
+----------------------+------------+-----------------+
| Table Name | Rows Count | Table Size (MB) |
+----------------------+------------+-----------------+
| table_one | 8 | 0.09 |
| table_two | 0 | 0.02 |
| table_three | 0 | 0.02 |
| table_four | 174 | 0.03 |
+----------------------+------------+-----------------+
4 rows in set (0.00 sec)
