How To find out largest 10 tables in Mysql database

mysql> 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 |
+-------------------------------------------+-------+-------+-------+------------+---------+
| test.sg_postmeta                     | 0.38M | 0.03G | 0.02G | 0.05G      |    0.62 |
| primary_prod.log_url_info                | 0.34M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_visitor_info            | 0.24M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_url                     | 0.33M | 0.01G | 0.01G | 0.03G      |    0.97 |
| primary_prod.log_visitor                 | 0.26M | 0.02G | 0.00G | 0.02G      |    0.00 |
| test.sg_posts                       | 0.05M | 0.01G | 0.01G | 0.01G      |    0.81 |
| primary_prod.report_event                | 0.05M | 0.00G | 0.01G | 0.01G      |    3.01 |
| primary_prod.report_viewed_product_index | 0.03M | 0.00G | 0.01G | 0.01G      |    2.88 |
| crm.vtiger_profile2field                  | 0.03M | 0.00G | 0.00G | 0.01G      |    3.00 |
| crm.vtiger_role2picklist                  | 0.01M | 0.00G | 0.00G | 0.00G      |    0.57 |
+-------------------------------------------+-------+-------+-------+------------+---------+
10 rows in set (1.07 sec)

No comments:

Post a Comment