Busindre

Mostrar ordenadamente las 10 tablas de mayor tamaño entre todas las bases de datos 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;

• Si se quiere buscar en una sola base de datos se debe agregar: WHERE table_schema = “db_name”
• Si se quiere cambiar el límite de 10 resultados eliminar: DESC LIMIT 10

Ejemplo de resultado pidiendo a MySQL las 10 tablas más grandes del servidor.

+--------------------------------------------+--------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name)      | rows   | DATA  | idx   | total_size | idxfrac |
+--------------------------------------------+--------+-------+-------+------------+---------+
| xxxxxxxxxx.addresses                       | 22.83M | 3.17G | 1.35G | 4.53G      |    0.43 |
| xxxxxxxxxx.orderItems                      | 7.88M  | 1.72G | 0.79G | 2.51G      |    0.46 |
| xxxxxxxxxx.orders                          | 4.06M  | 1.03G | 0.88G | 1.91G      |    0.86 |
| xxxxxxxxxx.orderItemsAddresses             | 23.10M | 0.99G | 0.87G | 1.85G      |    0.88 |
| xxxxxxxxxx.customers                       | 0.98M  | 0.08G | 0.05G | 0.12G      |    0.63 |
| xxxxxxxxxx.complaint                       | 0.35M  | 0.05G | 0.03G | 0.07G      |    0.64 |
| xxxxxxxxxx.complaintCauseComment           | 0.22M  | 0.03G | 0.02G | 0.05G      |    0.48 |
| xxxxxxxxxx.admin_link_inventory_attributes | 0.44M  | 0.02G | 0.02G | 0.04G      |    1.37 |
| xxxxxxxxxx.complaintCause                  | 0.24M  | 0.03G | 0.01G | 0.04G      |    0.47 |
| xxxxxxxxxx.complaintReasonComplaint        | 0.21M  | 0.01G | 0.01G | 0.02G      |    0.95 |
+--------------------------------------------+--------+-------+-------+------------+---------+

Fuente: http://www.percona.com/blog/2008/02/04/finding-out-largest-tables-on-mysql-server/