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.
