making bbPress (and WordPress) work better!

Examine the size of your MySQL data & indexes

Do you have an active server with several different programs installed that use MySQL?
Maybe even multiple wordPress and bbPress installations?

Here’s a really handy query you can throw into phpMyAdmin or otherwise to see the largest tables, their number of rows, data vs index size and the ratio of data vs index:

SELECT CONCAT(table_schema, '.', table_name) db_table,
       CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024  ), 1), 'M') size,
       CONCAT(ROUND(data_length / ( 1024*1024 ), 1), 'M') data,
       CONCAT(ROUND(index_length / ( 1024*1024 ), 1), 'M') indx,
       ROUND(index_length / data_length, 2) ratio 
FROM   information_schema.TABLES 
ORDER  BY data_length + index_length DESC LIMIT  30;

If you want to see a summary by database and not tables, try this:

SELECT count(*) tables,  table_schema dbase,
 concat(round(sum(table_rows) / 1000000, 2), 'M') rows,
 concat(round(sum(data_length + index_length) / (1024 * 1024 ), 1),'M') size,
 concat(round(sum(data_length) / (1024*1024 ), 1), 'M') data,
 concat(round(sum(index_length) / (1024*1024), 1), 'M') indx,
 round(sum(index_length) / sum(data_length), 2) ratio 
FROM information_schema.TABLES 
GROUP BY table_schema ORDER BY sum(data_length + index_length) DESC;

Thanks to: http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/ and http://www.mysqlperformanceblog.com/2008/03/17/researching-your-mysql-table-sizes/ (with a few tweaks by me)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s