How to optimize an update query for multiple rows using MySQL and PHP

Question

I have a table that has around 80.000 records. It has 4 columns:

|   id   |  code  |  size |  qty |  
+--------+--------+-------+------+
|    1   |  4735  |   M   |   5  |
|    2   |  8452  |   L   |   2  |
    ...
| 81456  |  9145  |   XS  |   13 |

The code column is unique.

I have to update the qty twice a day.

For that i'm using this query:

UPDATE stock SET qty = CASE id 
                         WHEN 1 THEN 10
                         WHEN 2 THEN 8
                         ...
                         WHEN 2500 THEN 20
                       END
              WHERE id IN (1,2,...,2500);

I am splitting the query to update 2500 stocks at a time using PHP.

Here is (in seconds) how much it takes for each 2500 stocks to update:

[0]7.11
[1]11.30
[2]19.86
[3]27.01
[4]36.25
[5]44.21
[6]51.44
[7]61.03
[8]71.53
[9]81.14
[10]89.12
[11]99.99
[12]111.46
[13]121.86
[14]131.19
[15]136.94
[END]137

As you can see it takes between 5 - 9 seconds to update 2500 products which i think is quiet a lot.

What can i change to speed things up?

Thank you!

have you tried just generating a separate UPDATE statement for each row? — ADyson, Apr 29 '20 at 15:15
no it s actually quite normal, and how often do you want to do this — nbk, Apr 29 '20 at 15:33
Does this answer your question? [Bulk update mysql with where statement](https://stackoverflow.com/questions/35726910/bulk-update-mysql-with-where-statement) — emrhzc, Apr 29 '20 at 15:35
@ADyson yes, it takes around 800 seconds using a different query for each record. — Melody, Apr 29 '20 at 15:35
your query needs max 3,6 milliseconds per update,tow that is very fast — nbk, Apr 29 '20 at 15:37
@emrhzc thank you but i can't use `on duplicate update` because there are some records that i don't need from the xml from which i'm taking the stock — Melody, Apr 29 '20 at 15:37
what does that mean "some records that i don't need from the xml from which i'm taking the stock" ? sounds another task php needs to do just like preparing those values — emrhzc, Apr 29 '20 at 15:40
other than that it's pretty identical in terms of functionality — emrhzc, Apr 29 '20 at 15:41
@emrhzc Insert means that it will first try to insert a record. I'm taking the data for my stocks from an XML file generated by a third party. But that XML file containes products (codes) that i don't sell. Therefore using `Insert...ON DUPLICATE UPDATE` won't do the work for me here. — Melody, Apr 29 '20 at 15:42
it will not insert if the index already exists, that's the trick — emrhzc, Apr 30 '20 at 03:48

Mike · Accepted Answer · 2020-04-29T15:47:29.720

2

Because the times seem to be getting longer the further along you get, I'd expect you need an index on the id field, as it looks suspiciously like it's doing a full table scan. You can create the index something like this

CREATE INDEX my_first_index ON table(id);

(I am having to add this as an answer because I can't make comments, I know it is more of a comment!!)

** EDIT **

I re-read and see your issue is bigger. I still think there is a chance that putting an index on id would fix it but a better solution would be to have a new table for the id to quantity mappings, lets call it qty_mapping

|   id   |  qty |  
+--------+------+
|    1   |   10 |
|    2   |   8  |
    ...
| 2500   |   20 |

make sure to index id and then you can change your update to

update stock set qty = (select qm.qty from qty_mapping qm where qm.id = stock.id)

It should be able to update the whole 80,000 records in next to no time.

edited Apr 29 '20 at 15:47

answered Apr 29 '20 at 15:33

Mike

182
1
3
15

can you please be more explicit about what `qty_mapping` is? Thank you! – Melody Apr 29 '20 at 15:50
Hi @Melody - If you look at your code, you are doing a massive case statement near the top to set the quantity based on the id. If you look at my answer, you'll see I've put the pairs you show in your example into my example of the database output. If you put each of these many thousads of values pairs into the database, you don't need the massive case statement, the sub query does it for you. – Mike Apr 29 '20 at 15:58
Wow! Ok so i tried this and here is the result: after creating the `qty_mapping` table and indexing both tables `code` column the total update time was reduced from `137 seconds` to `3 seconds`. Thank you so much! – Melody Apr 29 '20 at 17:18

How to optimize an update query for multiple rows using MySQL and PHP

1 Answers1