Thursday 12 December 2019

How to get the sizes of the tables in mysql database?

How to get the sizes of the tables in mysql database?


Question: How to get the sizes of the tables in mysql database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output
How to get the sizes of the tables in mysql database



Question: Get record counts for all tables along with size in MySQL database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)", SUM(TABLE_ROWS) AS "Total Record(S)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output

Get record counts for all tables in MySQL database