MySQL Useful scripts for Database & Tables

In this blogpost, I'm sharing useful scripts for MySQL Databases and Tables.

1) Find the size of Database

SELECT 
	table_schema AS Database_Name
	,ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS SizeInMB
FROM information_schema.tables
GROUP BY table_schema

2) Find the size of Table

SELECT 
	table_name AS TableName
	,ROUND(((data_length + index_length) / 1024 / 1024), 2) AS SizeInMB
FROM information_schema.TABLES
WHERE table_schema = 'Database_NAME'
AND table_name = 'Table_Name'

3) Find Largest Databases size on Server

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 table_schema
ORDER BY SUM(data_length+index_length) 
DESC LIMIT 10;

4) Find total occupied Size of each Storage Engines like: MyISAM, InnoDB, Memory.

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;

That's all in this post. If you liked this blog and interested in knowing more about MySQL, Please Like, Follow, Share & Subscribe to imjhachandan.com