Pulling duplicate entry in a column

[hatching]

I want to search for duplicate entries from a db with around 10,000 and want to do so with less stress on the db. Is this the most sufficient way to pull duplicate entries from a column?

SELECT distinct a.email, a.id
FROM `base` a JOIN `base` b 
ON (a.email=b.email AND a.id <> b.id )
ORDER BY email ASC

[Sxooter]

hatching wrote:

I want to search for duplicate entries from a db with around 10,000 and want to do so with less stress on the db. Is this the most sufficient way to pull duplicate entries from a column?

SELECT distinct a.email, a.id
FROM `base` a JOIN `base` b 
ON (a.email=b.email AND a.id <> b.id )
ORDER BY email ASC

depends on if email is indexed, how many matches it will hit, and a lot of possibilities.

You can change the a.id<>b.id to a.id > b.id btw. That way you won't get duplicates ids of your duplicates.

[hatching]

No my email column isn't indexed.

[laserlight]

No my email column isn't indexed.

It is usually a good idea to index a join column so that joins can be computed faster.