SQL Optimization, Nested Query

Question

What would be better way to implement such view so that the query doesn't take too long.

select * from table
where ID in (
SELECT ID FROM table
GROUP BY ID
HAVING COUNT(ID) > 1
)

Our server will need to run this every 10 mins. I thought of Indexing ID but wasn't sure if that would be the right way to go.

Are you using MySQL or SQL Server? You've tagged both. I'm assuming the former since that's where the performance problems seem t be... — ta.speot.is
– ta.speot.is, Commented Aug 12, 2013 at 22:27
As a rule of thumb, JOIN performs better as it is explained below. I wanted to add that you definitively need an index on ID. — Sylvain Leroux
– Sylvain Leroux, Commented Aug 12, 2013 at 22:33

Alex Gordon · Accepted Answer · 2013-08-12 22:39:17Z

2

select t.* from table t
join
(
SELECT ID FROM table
GROUP BY ID
HAVING COUNT(ID) > 1
) a
on a.id=t.id

answered Aug 12, 2013 at 22:25

Alex Gordon

61.5k307 gold badges710 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks everyone! With our DBA gone and me left in charge, you all came to the rescue!