MySQL - SELECT WHERE field IN (subquery) - Extrêmement lent pourquoi?

Question

J'ai quelques doublons dans une base de données que je veux inspecter, donc ce que j'ai fait pour voir les doublons, c'est ceci:

SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1

De cette façon, j'obtiendrai toutes les lignes avec relevant_field apparaissant plus d'une fois. Cette requête prend des millisecondes à s'exécuter.

Maintenant, je voulais inspecter chacun des doublons, donc j'ai pensé que je pourrais SÉLECTIONNER chaque ligne dans some_table avec un relevant_field dans la requête ci-dessus, donc j'ai fait comme ceci:

SELECT *
FROM some_table 
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)

Cela s'avère être extrêmement lent pour une raison quelconque (cela prend des minutes). Qu'est-ce qui se passe exactement ici pour que cela soit aussi lent? relevant_field est indexé.

Finalement, j'ai essayé de créer une vue "temp_view" à partir de la première requête (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1), et ensuite en faisant ma deuxième requête de cette façon à la place:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM temp_view
)

Et ça marche très bien. MySQL le fait en quelques millisecondes.

Des experts en SQL ici qui peuvent expliquer ce qui se passe?

Answer 1

La sous-requête est exécutée pour chaque ligne car il s'agit d'une requête corrélée. On peut transformer une requête corrélée en une requête non corrélée en sélectionnant tout de la sous-requête, comme ceci :

SELECT * FROM
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) AS subquery

La requête finale ressemblerait à ceci :

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT * FROM
    (
        SELECT relevant_field
        FROM some_table
        GROUP BY relevant_field
        HAVING COUNT(*) > 1
    ) AS subquery
)

Answer 2

Réécrivez la requête comme suit

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

_Je pense que st2.relevant_field doit être dans la sélection, sinon la clause having va générer une erreur, mais je ne suis pas sûr à 100%_

Ne jamais utiliser IN avec une sous-requête ; c'est notoirement lent.
Utilisez toujours IN avec une liste fixe de valeurs.

Plus de conseils

1. Si vous voulez rendre vos requêtes plus rapides, ne faites pas un SELECT * sélectionnez seulement les champs dont vous avez vraiment besoin.
2. Assurez-vous d'avoir un index sur relevant_field pour accélérer la jointure.
3. Assurez-vous de faire un group by sur la clé primaire.
4. Si vous êtes sur InnoDB et que vous ne sélectionnez que des champs indexés (et que les choses ne sont pas trop complexes) alors MySQL résoudra votre requête en utilisant uniquement les indexes, accélérant considérablement les choses.

Solution générale pour 90% de vos requêtes IN (select

Utilisez ce code

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

Answer 3

SÉLECTIONNER st1.*
DEPUIS some_table st1
inner join 
(
    SÉLECTIONNER relevant_field
    DEPUIS some_table
    GROUP BY relevant_field
    AYANT COUNT(*) > 1
)st2 ON st2.relevant_field = st1.relevant_field;

J'ai essayé votre requête sur l'une de mes bases de données, et j'ai également essayé de la réécrire comme une jointure à une sous-requête.

Cela a fonctionné beaucoup plus rapidement, essayez-le !

Answer 4

Sous-requêtes vs jointures

http://www.scribd.com/doc/2546837/Nouvelles-Optimisations-De-Sous-requête-Dans-MySQL-6

Answer 5

Essayez ceci

SELECT t1.*
FROM 
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;