Once in a while, at random intervals, our website gets completely paralyzed.
Looking at SHOW FULL PROCESSLIST;, I've noticed that when this happens, there is a specific query that is "Copying to tmp table" for a loooong time (sometimes 350 seconds), and almost all the other queries are "Locked".
The part I don't understand is that 90% of the time, this query runs fine. I see it going through in the process list and it finishes pretty quickly most of the time.
This query is being called by an ajax call on our homepage to display product recommendations based your browsing history (a la amazon).
Just sometimes, randomly (but too often), it gets stuck at "copying to tmp table".
Here is a caught instance of the query that was up 109 seconds when I looked:
SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl, product_product.msrp
FROM product_product, product_xref, product_viewhistory
WHERE
(
(product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id)
OR
(product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id)
)
AND product_product.outofstock='N'
AND product_viewhistory.cookieId = '188af1efad392c2adf82'
AND product_viewhistory.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172)
ORDER BY product_xref.hits DESC
LIMIT 10
Of course the "cookieId" and the list of "productId" changes dynamically depending on the request.
I use php with PDO.
