How to optimise very simple query with subquery that only needs to execute once
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) &gt; 1
) as b
    on a.products_model = b.products_model