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?