Finding your largest tables on MySQL

Handling large Mysql databases day in and day out, sometimes you just want a one liner to point out that table you forgot to set a trim job on, or where all that disk space is going.

It's nice because it shows a nice table view of your data, row count, data, as well as index size.

I use this guy all the time.

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
 FROM   information_schema.TABLES
 ORDER  BY data_length + index_length DESC
 LIMIT  10;


| CONCAT(table_schema, '.', table_name)           | rows    | DATA   | idx    | 
total_size | idxfrac |

| web3.searches_partner_messages           | 15.24M  | 97.94G | 1.96G  | 99.90G     
|    0.02 |
| web3.responses                    | 501.80M | 37.97G | 15.56G | 53.53G     
|    0.41 |
| web3.partner_addons             | 7.35M   | 33.96G | 0.85G  | 34.81G     
|    0.03 |
| web3.part_object_tracker_new | 63.97M  | 7.15G  | 11.95G | 19.10G     
|    1.67 |
| web3.searches_partner_messages_json      | 4.06M   | 18.77G | 0.24G  | 19.02G     
|    0.01 |
| web3.user_activity                       | 56.42M  | 6.97G  | 2.48G  | 9.45G      
|    0.36 |
| web3.part_object_tracker     | 22.52M  | 2.48G  | 4.21G  | 6.69G      
|    1.70 |
| web3.prt_searches                       | 52.54M  | 1.22G  | 1.79G  | 3.01G      
|    1.47 |
| web3.part_searches_tracker           | 16.72M  | 0.98G  | 0.98G  | 1.95G      
|    1.00 |
| web3.searches                            | 32.04M  | 0.64G  | 0.82G  | 1.46G      
|    1.28 |
+-------------------------------------------------+---------+--------+--------+------------+---------+
10 rows in set (0.15 sec)