The Site With the Lamp

November 3, 2004

I decided to find out how many duplicate accounts exists in one of our databases and made a startling discovery about mysql sub queries.

For many years PHP scripters (and other programmers for that matter) have been using a nested loop in place of subqqueries in mysql. The enlightened decided not to bother with mysql at all and to rely on postgres instead. Not very long ago mysql finally decided to implement subqueries but it is still not all tha t prevalent because many ISP
still support older versions of mysql which does not support subqueries, foreign keys and many other features.

To get back to my problem, I reconed that if anyone had signed up using the same email twice that would be a duplicate account. This was on a Rad User Manager database that had around 1600 users at the time. I used the following query to identify duplicates.

select a.userId, b.userName, b.userPassword, a.userEmail, a.userValidated

This query took an appalling 24 seconds to execute on my 1.8GHz athlon with 512MB of memory. It was far too long to produce a resultset that's just 96x2 rows. Of course there may be other ways of doing it but I wasn't too keen to spend time on i did the same with a good old fashion PHP nested loop. I had the shock of my life, the query took
less than a second. So much for the much feted sub query support.