This article will guide you to calculate size of tables and database in MySQL servers though sql queries. MySQL stored all the information related to tables in database in information_schema database. We will use information_schema table to find databases and tables size.
To check single database size in MySQL we may run this query,Please change ‘testdb‘ 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 = ' testdb';
To calculate all databases size 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;
The same way we can also check the database table size,To check the single table size:
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 = "testdb" AND table_name ="table_test";
To check all table size in MySQL 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 = "testdb";
This query will calculate size of all tables in a database in mysql server.