Hi I have the following table T:

id    1   2    3    4
col   a   b    a    c

I want to do a select that returns the id,col when group by(col) having count(col)>1

One way of doing it is

SELECT id,col FROM T 
   WHERE col IN (SELECT col FROM T GROUP BY(col) HAVING COUNT(col)>1);

The intern select (from the right) returns 'a' and main one (left) will return 1,a and 3,a

The problem is that the where in statement seems to be extremely slow. In my real case, the results from the internal select has many 'col's, something about 70000 and it's taking hours.

Right now it's much faster to do the internal select and the main select getting all ids and upcs and do the intersection locally. MySQL should be able to handle this kind of query efficiently.

Can I substitute the where in for a join or something faster?

Thanks