LOW_PRIORITY doesn't work for two reasons.
- It works only with MyISAM engine.
- It would just cause to wait until there are no more normal or
HIGH_PRIORITY read requests. If your server happens to be under constant load, it can even happen, that the update doesn't get executed at all.
What you can do, is, to check with a
SELECT first (I mean rewriting your
UPDATE to a
SELECT), that your query is performant, that it uses indexes to find the rows to update (check with
EXPLAIN) and add appropriate indexes if necessary.
Then when you execute your
UPDATE statements, pack them in a transaction.
The InnoDB engine logs information about currernt transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction it results in writes that, with a larger buffer, would not need to have been made at all. To set the size of the log buffer, adjust the value (or add it, if it's not set yet) in your my.cnf (Linux) or my.ini (Windows)
innodb_log_buffer_size = 8M
The default value is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.
If this isn't enough, you can update rows in batches using
LIMIT and spread the update statements over time, so that the row-locks aren't held for too long.