Low priority request in PHP or MySQL

Question

I Have a site and a database with 5 million rows, it's working like a charm. However, I'm running a cleanup cronjob each hour, to put the old data to a 'log' table and delete the old data, and in this time the server response is very slow. Is it possible to give that job a lower priority via PHP or MySQL?

Answer 1

I think the heaviest task in your cleanup is the DELETE operation for which you can use LOW_PRIORITY.

DELETE syntax from MySql manual page:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name ...

and the description:

If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).