2 petites requêtes différentes vs 1 requête avec sous-requête

Question

J'ai un tableau comme celui-ci

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

J'ai besoin de récupérer tous les noms dont le numéro personnel se répète plus de 1 fois dans la table, c'est à dire que le résultat doit être :

 Jon        
 Jon        

Donc, variante 1) :

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

Variante 2) :

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

Ensuite, à l'aide de php, j'ai récupéré la jointure des numéros personnels sous forme de chaîne de caractères (quelque chose comme ceci '222', '222' ) et exécuter d'autres requêtes

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

La variante 2 fonctionne environ 10 fois plus vite que la variante 1, c'est une surprise pour moi, je pensais qu'une seule requête serait plus rapide, mais...

(Dans un tableau de 500 000 lignes, la colonne personal_number non indexé)

Pourquoi la variante 2 est-elle beaucoup plus rapide que la variante 1 ?

Answer 1

Il semble que les sous-requêtes soient très lentes, comme le mentionne cet article http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries .

Il est préférable d'éviter les sous-requêtes et d'utiliser la jointure.

Answer 2

La première requête comporte de nombreuses sous-requêtes. Vous devez l'éviter. La meilleure solution à votre problème est de n'utiliser qu'une seule requête :

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

Cette requête ne vous renverra chaque nom répété qu'une seule fois. Si vous souhaitez afficher le nom du doublon autant de fois qu'il s'est rencontré, vous devez utiliser la requête suivante :

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

Puis, en PHP, faites quelque chose comme ceci :

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

Answer 3

Cela devrait être plus rapide :

SELECT  name  FROM mytable join (
        SELECT  personal_number  FROM mytable  GROUP BY personal_number
        HAVING COUNT(*) > 1
)a using (personel_number)

Edit : Si c'est plus rapide que la variante 1, cela signifie qu'à la variante 1 mysql reproduit la table interne pour chaque enregistrement encore et encore.

Answer 4

Comme l'indexation n'est pas effectuée, le 1 est lent, car il doit faire correspondre des numéros personnels à des numéros personnels sélectionnés. Si l'indexation est effectuée, elle prend moins de temps que précédemment. La variante 2 est une requête directe, elle est donc plus rapide.