php - mysql update operation is slow, how to solve it

Question

The database has an items table, and now we need to update the userid field in it.
The statement is UPDATE items SET userid = xxx WHERE userid = 0 limit 1;
Transactions have been enabled for optimization It’s time, but it still feels too slow. It takes 3 minutes to update 30,000 articles.
Try to create a temp table in the morning, save the userid->items array, and then write a php script to continuously query the temp table, and then operate.
But when multiple scripts are executed like this, only one can survive...and the monitoring effect cannot be achieved.
The current idea is to divide the update operation into deletion and insertion.. Trying now
Also, I would like to ask, if you are developing PHP, which message queue is better to use? I can’t find the documentation for the PHP version of rabbitmq. . .

Answer 1

You can use Replace into instead of update, or you can use insert into...on duplicate key update to update in batches.

In addition, a demo of the Rabbitmq PHP version is attached:
https://github.com/yuansir/ra...

Answer 2

Use redis as a queue. This is generally used. Your sql is probably slow because of the limit. Try to take out the IDs of all the data to be updated and then update it. It will be much better