Last updated on September 11th, 2020 at 11:50 am
Sometimes you may need to keep track of largest table in MySQL database so that you can optimize it. Here’s an SQL query to find the largest table in MySQL database. By largest, I mean size on disk and not number of records. 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 below to use it.
To get largest table in MySQL database (specific 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" order by data_length+index_lenght desc limit 1;
To get largest table in MySQL database (of all databases) use:
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Table size in MB" FROM information_schema.TABLES order by data_length+index_lenght desc limit 1;
These queries may take time based on number of tables.
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.