使用单个查询更新多个记录以提高性能

Question

I am having an update query inside a loop which get executed thousands of times.

All goes well but problem is that it takes lot of time for all updates.

If I concatenate/combine all update queries in a single string like this:

$update_query = "";
while(condition)
{
    $update_query = $update_query  . " update table set ... ; ";
}

Then execute $update_query single time, will it be helpful?

Or what else I can do for better performance? Any hint or suggestion?

Answer 1

You can use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table (id, value) VALUES (1, 'value1'), (2, 'value2')
ON DUPLICATE KEY UPDATE
value = VALUES(value)