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


Discuss your project
with our experts

No obligation. Limited slots.

Contact Form Business Enquiry

"*" indicates required fields

Please attach any RFP, project specification, or document that you would like to share.
Drop files here or
Max. file size: 5 GB.
    This field is for validation purposes and should be left unchanged.