MySQL Forums
Forum List  »  Newbie

Re: Query to find duplicates across multiple columns & time out issues
Posted by: Rick James
Date: July 05, 2012 07:25PM

> SELECT * FROM SomeTable WHERE Address IN (SELECT Address FROM SomeTable GROUP BY Address HAVING count(Address) > 1 )

"IN ( SELECT ... )" is poorly optimized by MySQL's optimizer. Turn it into a JOIN:

SELECT  b.*
    FROM  SomeTable b
    JOIN  
      ( SELECT  Address
            FROM  SomeTable
            GROUP BY  Address
            HAVING  count(Address) > 1
      ) a
        ON b.Address = a.Address 

Options: ReplyQuote


Subject
Written By
Posted
Re: Query to find duplicates across multiple columns & time out issues
July 05, 2012 07:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.