Find Largest Tables in MySQL
If you're trying to find what tables in your MySQL deployment are consuming the most amount of space, you can use the following query to find this information directly from the information schema.
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac, engine FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 25;
You'll get a list of the top 25 tables by total size (index size + data size), how many rows they have, and the engine they are using to be stored. Being able to see what engine is being used is especially helpful when running MySQL Cluster.