CODEWITHSUNDEEP
mysql
Melody
Melody

Reputation: 127

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

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!

Upvotes: 0

Views: 771

Answers (1)

Mike
Mike

Reputation: 182

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.

Upvotes: 2

Related Questions