[MYSQL] Group by subquery takes 2 minutes to process

[CrossMotion]

I have a simple SQL query with a group by subquery that runs on a table with 3000 records. It takes aboud 2 minutes to run the code in PHPmyadmin. Is there a way to speed this up?

SELECT partnumber, product_id
FROM products
WHERE partnumber IN (
  SELECT partnumber
  FROM products
  GROUP BY partnumber
  HAVING count(*) > 1 )
ORDER BY partnumber;

[Barand]

try

SELECT partnumber, product_id
FROM products
JOIN (
  SELECT partnumber
  FROM products
  GROUP BY partnumber
  HAVING count(*) > 1 ) as counts USING (partnumber)
ORDER BY partnumber;

PS Do you have an index on partnumber?

Edited May 15, 2013 by Barand

[CrossMotion]

Thank you Barand for your input. It tuns out I did not have a index on that field yet. I added it, boosting performance to 15 seconds.

 

Then I tried your query, which runs in 0.0002 sec .

 

Thx again!.

 

 

 

[Barand]

Sounds like a result!.

 

If you run an EXPLAIN on your original query you will, no doubt, see the words DEPENDENT SUBQUERY.

 

That means the query is being called for every row. With a "table subquery" like mine it is is called once.

[CrossMotion]

Hmm so in fact I was running 3004 query’s instead of one. That explains a lot .

 

Thanks for your clear and verry usefull explanation.