I don't know that this is a good question or not, but I have this query in one of my module:
SELECT * FROM `product` WHERE upc IN (
SELECT `upc` FROM `product` WHERE `upc` <> '' GROUP BY `upc` HAVING COUNT(*) > 1)
ORDER BY `upc`
the product table is quite big and this query takes about 20 mins to be executed.
I am not a big MySQL expert, but it is possible to speed up this query?
My second heavy query is an UPDATE query:
UPDATE `product` SET `quantity` = '0' WHERE `sku` IN ( SELECT `id_xml_prd` FROM `ixml_prd_map` WHERE `id_xml` = '8' );
Current indexes on the product table:
And on the ixml_prd_map: