Search code examples
phpmysqldatabase-performance

Low priority request in PHP or MySQL

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?

Solution

  • 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).