My favorite MySQL queries for table space analysis

mysql_table_space.sql

/*
    USE IT WITH CAUTION (could overload and impact your production server...)
    Feel free to add your own suggestion
    
    Made for a MySQL 5.5 database
*/
-- Biggest db (full schema)
SELECT 
	TABLE_SCHEMA,
	SUM(ROUND(DATA_LENGTH/1024/1024,2)) as data_length_mb, 
	SUM(ROUND(INDEX_LENGTH/1024/1024,2)) as index_length_mb, 
	SUM(ROUND(DATA_FREE/ 1024/1024,2)) as data_free_mb, 
	SUM(ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2)) AS table_length_mb,
	SUM(data_free) / (SUM(index_length) + SUM(data_length)) as frag_ratio  
FROM information_schema.tables  
WHERE 
	ENGINE = 'innodb'
GROUP BY TABLE_SCHEMA WITH ROLLUP;

-- Biggest tables
SELECT  
	TABLE_SCHEMA,
	TABLE_NAME,
	ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb, 
	ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb, 
	ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb, 
	ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
	data_free / (index_length + data_length) as frag_ratio,
	ROW_FORMAT  
FROM information_schema.tables  
WHERE ENGINE = 'innodb'
ORDER BY table_length_mb DESC
LIMIT 20;

-- Biggest indexes
SELECT  
	TABLE_SCHEMA,
	TABLE_NAME,
	ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb, 
	ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb, 
	ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb, 
	ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
	data_free / (index_length + data_length) as frag_ratio,
	ROW_FORMAT 
FROM information_schema.tables  
WHERE ENGINE = 'innodb'
ORDER BY index_length_mb DESC
LIMIT 20;

-- Indexes bigger than data set
SELECT  
	TABLE_SCHEMA,
	TABLE_NAME,
	ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb, 
	ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb, 
	ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb, 
	ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
	data_free / (index_length + data_length) as frag_ratio,
	ROW_FORMAT
FROM information_schema.tables  
WHERE 
	ENGINE = 'innodb'
	AND INDEX_LENGTH > DATA_LENGTH
ORDER BY index_length_mb DESC