How to increase the speed MySQL query

Question

I have a MySGL query:

UPDATE Feeds
SET Quantity = (SELECT Inventory.Quantity
                     FROM Inventory
                     WHERE Feeds.SKU = Inventory.SKU)
WHERE EXISTS (SELECT Inventory.Quantity
              FROM Inventory
              WHERE Feeds.SKU = Inventory.SKU);

It's working well, but very slow. The database is MySQL inodb, rows is around 50,000, to run the execution takes about half of hour. How can I decrease the execution time? Thanks in advance.

What sort of indexes do you have on those tables? You really haven't given enough information here. This also looks murderously slow due to the aggressive use of subqueries. Are you sure you can't do this as a more straight-forward multi-table UPDATE? — tadman
– tadman, Commented Nov 7, 2014 at 18:48
Do you have indexes on Feeds.SKU and/or Inventory.SKU? Might an UPDATE Feeds JOIN Inventory be better here? Try: UPDATE Feeds JOIN Inventory USING(SKU) SET Feeds.Quantity = Inventory.Quantity;. The JOIN should take care of the WHERE EXISTS, since it won't match rows that don't have matching SKUs. I didn't test this, but I think that's right. — gen_Eric
– gen_Eric, Commented Nov 7, 2014 at 18:49
As a general answer, check out EXPLAIN: sitepoint.com/using-explain-to-write-better-mysql-queries dev.mysql.com/doc/refman/5.0/en/using-explain.html If it's not too hard, try this: sqlfiddle.com, else post the result of DESCRIBE Inventory and DESCRIBE Feeds — Curtis Mattoon
– Curtis Mattoon, Commented Nov 7, 2014 at 18:49

Community · Accepted Answer · 2017-05-23 11:49:39Z

4

MySQL can do updates with join logic. Assuming that your SKU columns are at least indexed (unique?), this should perform very well:

UPDATE Feeds AS f
JOIN Inventory AS i
    ON i.SKU = f.SKU
SET f.Quantity = i.Quantity

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Nov 7, 2014 at 18:57

Sonny

8,3667 gold badges65 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to increase the speed MySQL query

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related