How to get MySQL table size for tables in database?

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";