There is a need to update the database, affecting 5-10 thousand records. If in a cycle to send on one request, it takes about 80 seconds (for 5 thousand entries). If you send a bunch of update in one request, it takes more than 150 seconds to process.

It is necessary to speed up the whole thing, ideally up to 2-5 seconds. Does anyone have any ideas on this?

PS

bd mysql

jap php

sample request

update product set price=7 where sku = '123';

UPD In my stupidity, I forgot about indexes. Added an index to sku time decreased to 1 second with a penny.

But still, maybe there are some other options to speed up this action?

  • Delete + Insert? - Alexey Anufriev pm
  • one
    if there are many requests, wrap the transaction. - KoVadim
  • 2
    Create temporary table t (...); insert into t (id, fld) values ​​(..), (..) ...; update tab join t on tab.sku = t.id set tab.price = fld; did not check. - Yura Ivanov

1 answer 1

What type of table? It is desirable for such a thing to do innoDB .

Tell us more about the task specification. On what basis do you update the data? If it is possible to combine strings by some attribute, then this should be done and used in the WHERE your query.