mysqlcheck with cron to optimize automatically

Over a period of time, mysql tables usually get fragmented. It degrades the performance of mysql-server significantly.

WordPress has many plugins to do this but it’s better to defragment tables manually from command-line using mysqlcheck

Using mysqlcheck

Run the following command by replacing USER & PASS values:

mysqlcheck -Aos -u USER -pPASS

It will show you some output e.g. “note : Table does not support optimize, doing recreate + analyze instead” if you have some InnoDB tables. You can ignore these notes. I really think mysqlcheck should show only errors rather than showing notes.

Automating mysql defragmentation using cron

You can run mysqlcheck using cron automatically.

Open your crontab -e and add following line in it:

0         4      *       *       *       mysqlcheck -Aos -u USER -pPASS > /dev/null 2>&1

Above will run mysqlcheck daily at 4AM.

Optimizing MyISAM tables only

As InnoDB tables do not support optimization, you may be interested in optimizing MySQL tables only.

You can do that using following: 

for i in `mysql -e 'select concat(table_schema,".",table_name) from information_schema.tables where engine="MyISAM"'`; do mysql -e "optimize table $i"; done

More…