0

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:

enter image description here

And on the ixml_prd_map:

enter image description here

Adrian
  • 2,576
  • 9
  • 49
  • 97
  • did you try running `EXPLAIN` ? https://dev.mysql.com/doc/refman/5.0/en/explain.html – Maximus2012 Sep 02 '15 at 21:29
  • 1
    Also see if this helps: http://stackoverflow.com/questions/7999833/mysql-explain-query-understanding. You might also want to look at the option of using `EXPLAIN EXTENDED` – Maximus2012 Sep 02 '15 at 21:31

1 Answers1

3

You can modify your query with WHERE EXISTS like below instead of having a IN clause with a subquery.

SELECT * FROM `product` p 
WHERE EXISTS (
SELECT 1 FROM 
`product` 
WHERE `upc` <> '' AND `upc` = p.`upc`
GROUP BY `upc` 
HAVING COUNT(*) > 1) 
ORDER BY `upc`;

Also you would want to have index on upc column.

Perform a normal update join query rather than having a IN subquery

UPDATE `product` p
JOIN `ixml_prd_map` i ON p.`sku` = i.`id_xml_prd` 
AND i.`id_xml` = '8'
SET p.`quantity` = '0';

Also for this UPDATE query to be faster have an index on id_xml_prd and id_xml column ON ixml_prd_map table.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Unbelievable, with index on upc column and with your code it takes only few sec to run this query. There is another query which runs forever, I update my original question. Thank you again, I really appreciate your help. – Adrian Sep 02 '15 at 22:36
  • I am going to try this. There is an index on the suggested columns? On the second picture the second row `XML-ID = id_xml` and `XML-ID-2 = id_xml_prd`. Or is it a different type of index? For the index upc I used: `ALTER TABLE product ADD INDEX ( upc ) ;` – Adrian Sep 03 '15 at 00:26
  • @Adrian, if you already have an index on those columns then that would be enough. Well, I didn't see your posted pic so had no idea. – Rahul Sep 03 '15 at 13:05
  • it's here: http://i.stack.imgur.com/2kSk6.png I hope it is enough. Thank you so much for the help. Before: 30 min, now it runs less than 5 mins. – Adrian Sep 03 '15 at 14:40
  • @Adrian, Welcome, consider accepting the answer if it helped. – Rahul Sep 03 '15 at 15:00