MySQL: Script to find total occupied Size of each Storage Engines

In this post, I am sharing a script to find the total occupied size of each Storage Engine of MySQL Server.

MySQL Provides different types of storage engines like: MyISAM, InnoDB, Memory.
I have seen few questions on MySQL Forum like how to migrate MyISAM tables into InnoDB engines and what would be the storage size difference.

Periodically, Database Administrators have to check the occupied size of each different MySQL Storage Engine which helps them to take decisions on internal migration or for any other reason.

1 2 3 4 5 6 7 8 9 10 11

SELECT COUNT(*) AS TotalTableCount ,table_schema ,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS TotalRowCount ,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS TotalTableSize ,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS TotalTableIndex ,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') TotalSize FROM information_schema.TABLES GROUP BY ENGINE ORDER BY SUM(data_length+index_length) DESC LIMIT 10;