Tuning a Self-Join sql query
I need some help in making the query below faster. Basically I want to retrieve all values from table1 where col1 has the same value, but col2 has different values.
I have non unique indexes on both col1 and col2. The below query works, but table1 has 40 million rows in, so it takes forever (3 days...)
Doing an explain on the query, it does use the index on col1, but not col2 (due to the not equal cluase I presume)
Any help to speed up/rewrite this query would be greatly appriecated!
SELECT a.col1
, a.col2
FROM table1 a
WHERE EXISTS ( SELECT 'x'
FROM table1 b
WHERE a.col1 = b.col1
AND a.col2 <> b.col2
) ;