Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING COUNT(products_model) > 1)

For whatever reason, it would seem that the subquery is being run for each product. I only need it to be run once really - and would have thought that mysql would intelligently realise this. Otherwise I would have written it like

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products AS a WHERE (SELECT COUNT(products_model) FROM products AS b WHERE a.products_model = b.products_model GROUP BY products_model) > 1

But bizzarely - the second query is actually faster by a factor of precisely 3 (31 seconds as opposed to 1 minute 33 seconds), despite being a "foreach" way of thinking rather than "set theory" way of thinking. I thought the latter was supposed to be much quicker? Clearly I have written the first query incorrectly - but how?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Have you tried with a join?

SELECT a.products_id, a.products_model, a.products_iban_no
     , a.products_allocation_number
FROM products a
JOIN (
    SELECT products_model
    FROM products
    GROUP BY products_model
    HAVING COUNT(products_model) > 1
) as b
    on a.products_model = b.products_model
share|improve this answer
    
This works great, thank you. Is there any particular reason it is quicker or is it an implementation thing? –  David Jordan Jun 6 at 12:32
    
Implementation thing, in theory the DBMS should be able to process your statement just as good as mine, in practice joins often works better than in predicates (especially true for mysql). –  Lennart Jun 6 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.