Edit Question - KnowledgeHub

Title Body

I've got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this: SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1 This way, I will get all rows with relevant_field occuring more than once. This query takes milliseconds to execute. Now, I wanted to inspect each of the duplicates, so I thought I could SELECT each row in some_table with a relevant_field in the above query, so I did like this: SELECT * FROM some_table WHERE relevant_field IN ( SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1 ) This turns out to be extreeeemely slow for some reason (it takes minutes). What exactly is going on here to make it that slow? relevant_field is indexed. Eventually I tried creating a view "temp_view" from the first query (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1) , and then making my second query like this instead: SELECT * FROM some_table WHERE relevant_field IN ( SELECT relevant_field FROM temp_view ) And that works just fine. MySQL does this in some milliseconds. Any SQL experts here who can explain what's going on?

Tags (comma-separated) Cancel