« Congrats Fozbaca! | Home | UFies.org (and TDIClub) Having Issues »

August 20, 2008

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;

By Arcterex on 1:41 PM | 1 Comment

1 Comment

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 :)

James | August 20, 2008 4:02 PM