Mysql Expliquer Requête de type “TOUS” lorsqu'un index est utilisé

J'ai couru une requête Mysql comme ci-dessous:

EXPLAIN
SELECT *
FROM(
        SELECT *  # Select Number 2
        FROM post
        WHERE   parentid = 13
        ORDER BY time, id
        LIMIT 1, 10
    ) post13_childs
JOIN post post13_childs_childs
ON post13_childs_childs.parentid = post13_childs.id

et le résultat a été:

id |select_type  |table               |type |possible_keys  |key      |key_len  |ref              |rows    |Extra
1  |PRIMARY      |<derived2>          |ALL  | NULL          | NULL    |NULL     |NULL             |10      |
1  |PRIMARY      |post13_childs_childs|ref  |parentid       |parentid |9        |post13_childs.id |10      |Using where
2  |DERIVED      |post                |ALL  |parentid       |parentid |9        |                 |153153  |Using where; Using filesort

Cela signifie qu'il a utilisé l'indice de parentid mais a scanné toutes les lignes en raison de ALL et 153153.
Pourquoi ne pas l'index de l'aide pour ne pas Full Scannig?

Bien que si je lance la dérivée de la requête (Select #2) seul comme ci-dessous:

Explain
SELECT * FROM post  
WHERE parentid=13
ORDER BY time , id
LIMIT 1,10

serait le résultat souhaité:

id |select_type  |table  |type |possible_keys  |key      |key_len  |ref  |rows    |Extra
1  |SIMPLE       |post   |ref  |parentid       |parentid |9        |const|41      |Using where; Using filesort

Edit:

La table post a ces indices:

  1. id (PRIMAIRE)
  2. parentid
  3. temps, id (timeid)

nombre total de lignes --> 141280.
le comte d'enfants de 13 (parentid=13) --> 41
le comte d'enfants de 11523 --> 10119

Quand j'ajoute de l'indice de (parent,time,id), le problème de la première requête pourrait être résolu par la explin de sortie pour 13 --> 40 lignes, type:ref
et pour 11523 --> 19538 lignes, type:ref!!! cela Signifie que tous les enfants de lignes de 11423 est examiné pendant que j'limitée 10 premières lignes.

Voulez-vous contribuer à l'amélioration et l'optimisation de cette requête? Si oui, nous aurions besoin de voir la totalité de la requête (et ses EXPLICATIONS).
Excusez-moi. J'ai édité et écrit les détails maintenant.
Essayez de créer d'index pour (parentid, time, id). Pour de plus amples référence mysqlperformanceblog.com/2006/09/01/...
Vous ne pouvez pas profiter de l'index dans la table dérivée....
Pourquoi? Vraiment? est une référence disponibles .

OriginalL'auteur ahoo | 2013-12-20

  1. 7

    Votre sous-requête:

        SELECT *  # Select Number 2
    FROM post
    WHERE   parentid = 13
    ORDER BY time, id
    LIMIT 1, 10;

    Ce mentionne trois colonnes explicitement, en plus de tout le reste des colonnes, Vous avez trois indices. Voici comment elles peuvent être utilisées:

    • id (PRIMAIRE) -- Cet indice est inutile. Bien que mentionné dans le order by de la clause, c'est la deuxième condition
    • parentid -- Cet indice peut être utilisé pour satisfaire les where clause. Cependant, d'après les données correctes est tiré, il aurait besoin d'être triés de manière explicite.
    • temps, id (timeid) -- Cet indice peut être utilisé pour le tri, avec un grand "MAIS". MySQL peut analyser l'index pour que tout soit dans le bon ordre. Mais il faudra vérifier, ligne par ligne, si la condition sur parentid est rencontré.

    Juste pour introduire pourquoi l'optimisation est dur. Si vous avez une petite quantité de données (disons que la table s'adapte sur une ou deux pages), puis un full table scan suivi par un tri est probablement très bien. Si la plupart des parentid valeurs sont 13, puis le deuxième indice pourrait être un pire cas. Si la table ne rentre pas dans la mémoire, puis la troisième serait incroyablement lent (ce qu'on appelle page raclée).

    Le bon indice pour cette sous-requête est celui qui satisfait le where clause et permet la commande. Cet indice est parentid, time, id. Ce n'est pas un index de couverture (sauf si ce sont toutes les colonnes de la table). Mais il faut réduire le nombre de visites aux lignes réelles de 10 à cause de la limit clause.

    Noter que, pour la requête complète, vous voulez un indice sur parentid. Et, heureusement, un index sur parentid, time, id compte comme un indice de ce genre. Ainsi, vous pouvez le supprimer de l'index. Le time, id indice est probablement pas nécessaire, sauf si vous avez besoin que les autres requêtes.

    Votre requête est également filtrer ces "enfants" qui ont "enfants" eux-mêmes. Il est tout à fait possible qu'aucune ligne ne sera retourné. Avez-vous vraiment l'intention d'un left outer join?

    Comme un dernier commentaire. Je suppose que cette requête est une simplification de votre requête. La requête est en tirant toutes les colonnes de deux tables -- et ces deux tableaux sont les mêmes. Qui est, vous obtiendrez le double des noms de colonnes de tableaux identiques. Vous devriez avoir des alias de colonne afin de mieux définir les colonnes.

    tnx pour la réponse. j'ai ajouté de l'indice de parentid, time, id mais ... (regarde mon Edit de ma Question ci-dessus dont j'ai changé maintenant)

    OriginalL'auteur Gordon Linoff

  2. 0

    Faire une COMMANDE PAR qui n'est pas aidé par un index peut tue régulièrement des performances. Pour la requête interne, je voudrais avoir un index de couverture (parentID, heure, id ) de sorte que les clauses where et ORDER BY pouvez utiliser l'index. Depuis le parentID est aussi à la base de la rejoindre par la suite, il doit être bon d'y aller et d'être assez rapide.

    La table a une paire time,id index.
    non, maintenant, une paire de (temps,id), vous DEVEZ INCLURE L'ID Parent, de sorte que l'indice devrait être (PARENTID, HEURE, ID)
    Quand je l'indice de (parentID, time, id ), la colonne rows d'expliquer le résultat de sortie 19538 (et le type:réf.). J'Attends 10 en raison de LIMIT clause.
    Je suggère la suppression de l'individu parentID et (timeid, id ) pour éviter toute confusion pour le moteur comme un indice de parentID dans la PREMIÈRE POSITION de l'index de faire la même chose que votre stand-alone de l'indice, mais aussi avoir le temps et l'id de deuxième et troisième position à résoudre la clause order by.
    parentID est définie comme une clé étrangère. toutes les clés étrangères est l'indice de trop. et je ne pense pas que pouvez supprimer l'index.

    OriginalL'auteur DRapp