1

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.

vorsta
  • 21
  • 1
  • 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 Nov 07 '14 at 18:48
  • 1
    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 Nov 07 '14 at 18:49
  • 3
    As a general answer, check out EXPLAIN: http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/ http://dev.mysql.com/doc/refman/5.0/en/using-explain.html If it's not too hard, try this: http://sqlfiddle.com/, else post the result of `DESCRIBE Inventory` and `DESCRIBE Feeds` – Curtis Mattoon Nov 07 '14 at 18:49
  • Atleast put description of feeds & inventory tables – Ashish Nov 07 '14 at 18:50

1 Answers1

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
Community
  • 1
  • 1
Sonny
  • 8,204
  • 7
  • 63
  • 134