Defragmenting all fragmented MyISAM tables

I just had another look at what I wrote the week before last (you know, being home-sick/on vacation has it’s advantages) and additionally read up on “OPTIMIZE TABLE” again. The comments in the manual mention “SHOW TABLE STATUS“, which gives you a complete list, but it doesn’t allow you to filter certain kinds of things out (like I only wanted to see MyISAM tables in the list, I only wanted database and table).

So I went ahead and looked around in MySQL’s own databases and if you look closely at information_schema, it’s got a list of all databases/tables with an additional pointer whether or not databases are fragmented, the row Data_free. I only found this, because I looked at how the mysqltuner figured whether or not you have fragmented tables.

So, without further ado, here’s the final script I’m gonna torture for the next week:

I know it ain’t completely bullet proof and it sure as hell isn’t neat, but I think it does the job. Also, if you don’t want to paste it, here’s the file download.

One thought to “Defragmenting all fragmented MyISAM tables”

  1. for i in $(mysql -e “show databases;” | sed ‘s/Database//’) ; do for each in $(mysql -e “use $i; show tables;” | sed ‘s/Tables.*//’ 😉 ; do mysql -e “use $i ; optimize table $each” ; done ; done

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.