Find Biggest MySQL Tables

Firstly, if you are not using SSL for all your sites, you should step it up! NameCheap's SSL certificates start at only $9. There's no excuse!
Firstly, if you have not tried Digital Ocean's SSD Virtual Servers for only $5/mo, I highly recommend them!

I found a great snippet for this from Peter Zaitsev. I modified it to show the size in MB instead of GB (Sorry my databases aren’t that awesome!). Peter’s code lists the biggest tables from all databases; I prefer to list tables just for one database so I added a WHERE.

SELECT	concat(table_schema,'.',table_name),
	concat(round(table_rows/1000000,2),'M') rows,
	concat(round(data_length/(1024*1024),2),'MB') DATA,
	concat(round(index_length/(1024*1024),2),'MB') idx,
	concat(round((data_length+index_length)/(1024*1024),2),'MB') total_size,
	round(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
WHERE table_schema = 'my_database_name'
ORDER BY data_length+index_length DESC LIMIT 10;

About this entry

Good Reads