MySQL: Why is this UPDATE query so incredibly slow?

Question

So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts table, but only update the records which are needed and keep track of which ones.

This is the code I am using to find changes :

SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price

This is the code I am using to update :

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1

When the two pieces of code are run separately they complete in less than a second each. However when combined like below, the query runs for HOURS !

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1
WHERE ap.sku IN (
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
);

Below is the structure

ActiveProducts.Product_id - PrimeKey
ActiveProducts.name - Index
ActiveProducts.sku - Index

XMLImport.Product_id - PrimeKey
XMLImport.name - Index
XMLImport.sku - Index

Server version: 5.5.44-MariaDB MariaDB Server

Tables: InnoDB

Answer 1

Why don't you just build a temp table and use the results to update. This is bad form. It's got to build the Subquery first, then assemble that in the WHERE Clause, then it's going to update every record to find a match.

Instead just INNER JOIN to a temp table that does the first part, or a CTE if you're using SQL Server and can't have a multi-step process.

WITH SkuList AS
(
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
)
UPDATE ActiveProducts ap INNER JOIN SkuList xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1

Answer 2

Okay, so I've gone with this...

TRUNCATE TABLE SkuTemp;

INSERT INTO SkuTemp (sku)
SELECT sku FROM (
  SELECT name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description FROM ActiveProducts
  UNION ALL
  SELECT name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description FROM XMLImport
) tbl
GROUP BY name, type, size, colour, sku, weight, instock, quantity, price, image1, image2, image3, image4, image5, image6, description
HAVING count(*) = 1;

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku 
SET ap.name = xml.name, ap.type = xml.type, ap.size = xml.size, ap.colour = xml.colour, ap.weight = xml.weight, ap.instock = xml.instock, 
ap.quantity = xml.quantity, ap.price = xml.price, ap.image1 = xml.image1, ap.image2 = xml.image2, ap.image3 = xml.image3, ap.image4 = xml.image4, 
ap.image5 = xml.image5, ap.image6 = xml.image6, ap.description = xml.description, ap.updated = 1 
WHERE ap.sku IN (SELECT sku FROM SkuTemp);

Works and executed in under 2 seconds.

Answer 3

DISTINCT and GROUP BY are redundant. Do just the GROUP BY.

IN ( SELECT ... ) is very inefficient.

FROM ( SELECT ... ) can be inefficient because of the lack of an index on the tmp table it enerates for you.

UPDATE ... WHERE sku IN ( SELECT ... ) can (should) be turned into a multi-table UPDATE using a JOIN. That will further help your final suggestion.

You can probably get rid of the tmp table, too, by

UPDATE ap
       JOIN xml
       JOIN ( SELECT ... FROM UNION ... GROUP BY ... DISTINCT ) skus

(This assumes that ap and xml have indexes on sku.)