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