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