Getting a quick summary of table sizes and rows in MySQL

Just a quick post to show you a technique my colleague showed me on how to get the sizes of tables and an approximate number of rows it has, without having to wait long for a select count(*) from table.

Use this command:

        SELECT
             table_schema as `Database`,
             table_name AS `Table`,
             table_rows AS "Quant of Rows",
             round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`
        FROM information_schema.TABLES
        WHERE table_schema = 'ballast'
        ORDER BY (data_length + index_length) DESC;

This will show you a result like this:

-------------------------------------------------------------------------------------------
| Database     Table                           Quant of Rows  Size in GB
-------------------------------------------------------------------------------------------
| ball            TableHosp2015               110654400             11.05
| ball            TableHosp2016               115890383             10.30
| ball            Table2016_temp                87027666               6.49
| ball            Table2014_temp                73765370               5.78
| ball            Table2015                          62064795               4.86
| ball            Distances                               158372               0.02
-------------------------------------------------------------------------------------------