Finding MySQL Table Disk Usage Via SQL
A little snippet that's a deep-geek note-to-self. Darren found this from modifying information on this site and I figured I wanted to keep it around as it looks potentially handy to have. Also once google indexes things I'll be able to find it easily again!
SELECT
table_schema, count(*) TABLES,
concat(round(sum(table_rows)/1000000,2),'M')
rows,concat(round(sum(data_length)/(1024*1024*1024),2),'G')
DATA,concat(round(sum(index_length)/(1024*1024*1024),2),'G')
idx,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G')
total_size,round(sum(index_length)/sum(data_length),2) idxfrac
FROM
information_schema.TABLES group by table_schema;
0 TrackBacks
Listed below are links to blogs that reference this entry: Finding MySQL Table Disk Usage Via SQL.
TrackBack URL for this entry: http://arcterex.net/mt/mt-tb.cgi/4179
Handy enough, but data size != disk space used, especially if your tables are InnoDB. With MyISAM tables, deleted rows aren't freed up until you do an optimize table. With InnoDB, you've got shared table space, and the disk space used can bear very little resemblance to the actual size of your data.
Just thought I'd mention it :)