MySQL UPDATE statement went from 6s to 1h+

I think I have messed something up…
This is another query which used to take about 6 seconds to run, but even with the same data it’s taken over an hour and is still running…

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 DISTINCT 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
  ORDER BY name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description
);

Also worth noting that both sku fields are Index’d

The status of the process is currently… “Copying to tmp table”

What storage engines are in use for each table?

All are InnoDB

Try wrapping it up in a transaction

Been going a few minutes no and doesn’t look to be any different…

SELECT DISTINCT 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
  ORDER BY name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description

If with both queries wrapped up in a transaction, if you run that part first and then run the second part

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 ()

providing that part with a comma separated list generated from the output of the first, does it make any difference to the overall execution time?

Still an issue I’m afraid. I have removed a few fields in an attempt to speed it up but there is no difference in performance. I had to kill the mysql process after an hour :frowning:

When I run this…

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
);

… it takes a split second and lists 13 products which have changed.

Finally…

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

That updated all 23,000 products in under 1 second.

Yet when I stick the two together like this…

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
);

It just creates a never ending process… I am lost completely

this is problematic in several ways

you’re selecting distinct sku but grouping by a lot of columns… that’s inefficient right there

i kind of lose you there because i’m not sure what you’re trying to accomplish

the ORDER BY is definitely not needed

Yeah I’ve removed that now :smile:

SELECT DISTINCT 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

I am running this to find changes and recording the sku’s into a temporary table, which I do the update’s on the back of.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.