Rewrite the query into this
SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id /* list a unique sometable field here*/
HAVING COUNT(*) > 1
I think st2.relevant_field
must be in the select, because otherwise the having
clause will give an error, but I'm not 100% sure
Never use IN
with a subquery; this is notoriously slow.
Only ever use IN
with a fixed list of values.
More tips
SELECT *
only select
the fields that you really need.relevant_field
to speed up the equi-join.group by
on the primary key. General solution for 90% of your IN (select
queries
Use this code
SELECT * FROM sometable a WHERE EXISTS (
SELECT 1 FROM sometable b
WHERE a.relevant_field = b.relevant_field
GROUP BY b.relevant_field
HAVING count(*) > 1)