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 开发者_JAVA技巧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?

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

There is such a thing in MYSQL as low-priority-updates - its documented here -> http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_low-priority-updates

** only works for MyISAM, MEMORY, MERGE storage engines ....

One possible solution would be to do the copying of old data on a slaved replication server. That way, the heavy load is entirely on that slave box. Once the copying is finished on slave, you could delete on master.

As explained in the documentation, you could add LOW_PRIORITY to your query. So, your query would become:

$sql = "INSERT LOW_PRIORITY INTO #__bet_oddslog (id, team1, team2, league, sport, time, q1, q2)"; $sql .= " SELECT a.id, a.team1, a.team2, a.league, a.sport, a.time, AVG(b.q1) as q1, "; $sql .= " AVG(b.q2) as q2 FROM #__bet_details as a, #__bet_1x2_best as b WHERE time<'".date('Y-m-d H:i:s', strtotime("now"))."' AND a.id=b.bet_id GROUP BY b.bet_id";

One of the low priority requests in MYSQL is INSERT DELAYED. But it only works on INSERT, not UPDATE or DELETE.

EDIT: I just knew that DELETE also can be delayed!