Mysql – How to optimise very simple query with subquery that only needs to execute once

countMySQLperformancequery-performancesubquery

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?

Best Answer

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
Related Question