Researching your MySQL table sizes

I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

SELECT count(*) tables,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES;
+--------+----------+---------+--------+------------+---------+
| tables | rows     | data    | idx    | total_size | idxfrac |
+--------+----------+---------+--------+------------+---------+
|   1538 | 1623.91M | 314.00G | 36.86G | 350.85G    |    0.12 |
+--------+----------+---------+--------+------------+---------+
1 row in set (52.56 sec)

Find the same data using some filter
I often use similar queries to find space used by particular table “type” in sharded environment when multiple tables with same structure and similar name exists:

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_name like "%performance_log%";
+--------+---------+---------+-------+------------+---------+
| tables | rows    | data    | idx   | total_size | idxfrac |
+--------+---------+---------+-------+------------+---------+
|    120 | 370.29M | 163.97G | 0.00G | 163.97G    |    0.00 |
+--------+---------+---------+-------+------------+---------+
1 row in set (0.03 sec)

Find biggest databases

SELECT
        count(*) tables,
        table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,
        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
        round(sum(index_length)/sum(data_length),2) idxfrac
        FROM information_schema.TABLES
        GROUP BY table_schema
        ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+--------+--------------------+-------+-------+-------+------------+---------+
| tables | table_schema       | rows  | data  | idx   | total_size | idxfrac |
+--------+--------------------+-------+-------+-------+------------+---------+
|     48 | cacti              | 0.01M | 0.00G | 0.00G | 0.00G      |    0.72 |
|     17 | mysql              | 0.00M | 0.00G | 0.00G | 0.00G      |    0.18 |
|      4 | pdns               | 0.00M | 0.00G | 0.00G | 0.00G      |    1.00 |
|      2 | test               | 0.00M | 0.00G | 0.00G | 0.00G      |    0.12 |
|     16 | information_schema | NULL  | 0.00G | 0.00G | 0.00G      |    NULL |
+--------+--------------------+-------+-------+-------+------------+---------+
5 rows in set (0.32 sec)

Data Distribution by Storage Engines
You can change this query a bit and get most popular storage engines by number of tables or number of rows instead of data stored.

SELECT engine,
        count(*) tables,
        concat(round(sum(table_rows)/1000000,2),'M') rows,
        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
        round(sum(index_length)/sum(data_length),2) idxfrac
        FROM information_schema.TABLES
        GROUP BY engine
        ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+------------+--------+---------+---------+--------+------------+---------+
| engine     | tables | rows    | data    | idx    | total_size | idxfrac |
+------------+--------+---------+---------+--------+------------+---------+
| MyISAM     |   1243 | 941.06M | 244.09G | 4.37G  | 248.47G    |    0.02 |
| InnoDB     |    280 | 682.82M | 63.91G  | 32.49G | 96.40G     |    0.51 |
| MRG_MyISAM |      1 | 13.66M  | 6.01G   | 0.00G  | 6.01G      |    0.00 |
| MEMORY     |     14 | 0.00M   | 0.00G   | 0.00G  | 0.00G      |    NULL |
+------------+--------+---------+---------+--------+------------+---------+
4 rows in set (14.02 sec)

Trivial but handy.