Last updated on September 11th, 2020 at 11:55 am
While working with MySQL, you may need to get MySQL table size for a specific table in your database. There are no direct variables that store this information. You can use the following SQL query to get it. A MySQL table size consists of 2 parts:
1. data_length – size of the table records
2. index_length – size of the table index
These are system variables which stores the information as number of bytes. The query below adds the 2 variables for the table. It converts the resultant number of bytes into mega bytes (MB) for easier understanding. Just replace $DB_NAME and $TABLE_NAME below to use it.
To get MySQL table size of one table in a database use:
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Table size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";
$DB_NAME – Name of your MySQL database
$TABLE_NAME – Name of MySQL table
To get MySQL table size of multiple tables in a database use:
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Table size in MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME" AND table_name in ("$TABLE_NAME1","$TABLE_NAME2","$TABLE_NAME3");
$TABLE_NAME1, $TABLE_NAME2, etc – Names of MySQL tables
To get MySQL table size of all tables in a database use:
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "MB" FROM information_schema.TABLES WHERE table_schema = "$DB_NAME";
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.