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
-------------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------------
No comments:
Post a Comment