2 different small query vs 1 query with subquery

Question

I have table like this

name       | personal_number 
-----------------------------------------
Jon        | 222
Alex       | 555
Jon        | 222
Jimmy      | 999

I need get every name, which personal_number repeates in table more than 1, that is result must be:

 Jon        
 Jon        

So, Variant 1):

SELECT  name  FROM mytable WHERE personal_number IN (
        SELECT  personal_number  FROM mytable  GROUP BY personal_number
        HAVING COUNT(*) > 1
)

Variant 2):

SELECT  personal_number  FROM mytable  GROUP BY personal_number
        HAVING COUNT(*) > 1
)

Then, using php, retrieved personal_numbers join as string (soemthing like this '222', '222' ) and run other query

SELECT  name FROM mytable  WHERE personal_number IN( here joined string )

Variant 2 works approximately 10 times faster, than variant 1, this is surprise for me, I was thinking that one query will be faster, but...

(In table is 500 000 rows, column personal_number not indexed)

So, what you mean about cases like this? why variant 2 is many faster than variant 1 ?

Answer 1

It seems that subqueries are very slow as mentioned in this article http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries.

You should try to avoid having subqueries and use joining instead.

Answer 2

First query has heavy subquery. You must avoid this. The best solution for your problem is only one query:

SELECT name FROM mytable GROUP BY personal_number HAVING COUNT(*) > 1;

This query will return you each repeated name only once. If you want to display the name of the duplicate as many times as they met you must use next query:

SELECT name, COUNT(*) AS count FROM mytable GROUP BY personal_number HAVING COUNT(*) > 1;

And then in PHP do something like this:

foreach ($rows as $row) {
  for ($i = 0; $i++; $i < $row['count']) {
    echo $row['name'] . "\n";
  }
}