[MYSQL] - Order By sur deux colonnes ralenti la requête

[Neoxy]

Bonjour,

J'ai une requête exécutée sur une table avec 150 000 enregistrement qui me pose un soucis.. En effet, celle ci prend 0,50 secondes à s'exécuter... Et je trouve que cela n'est pas très optimisé...

Alors en fouillant et en tripotant un peu ma requête, j'ai remarqué que la clause ORDER By était assez gourmande en temps d'exécution surtout quand ci effectue un tri sur deux champs....

En effectuant des tests, lorsque je ne laisse qu'un champs dans l'order by, la requete s'execute alors en 0,0008 secondes (bien plus rapide)...

Cependant, j'ai besoin d'effectuer un tri sur deux champs et je suis serieusement coincé...

Es ce qu'il existe une méthode, ou une solution de substitution afin d'effectuer un tri sur mes deux champs de façon à ce que ma requête soit plus rapide???

J'ai revu tous les champs de mes tables pour les optimiser, j'ai placé des indexs la ou il faut...

J'ai par ailleurs tripoter les variables system comme le sort_buffer afin d'optimiser, mais rien de concluant...

Avez vous eu un retour d'expérience sur des ralentissements de requêtes SQL qui prenaient du temps lorsque l'order by triais deux colonnes ???

Cordialement,

Modifié 7 Juin 2010 par Neoxy

[Sarc]

Bonjour,

As-tu prévu de mettre un index sur les deux colonnes ? Comme ça, elles seront triées à la création des données et non à la lecture... Ce qui rend la création un poil plus complexe (mais c'est une complexité en log(n) plutôt qu'en 1) et la lecture beaucoup plus rapide (log(n) plutôt que n² de mémoire...).

Bref, un lien ici :
/>http://dev.mysql.com/doc/refman/5.0/fr/multiple-column-indexes.html

Avec ça, ça devrait pulser, si tout va bien.

[jcaron]

Même réponse, mettre un index sur les deux colonnes utilisées (un seul index sur les deux, hein, pas un index sur chaque). Il faut aussi que l'ordre de tri (ASC/DESC) soit le même en général.

Ceci dit, ta requête, elle ne retourne pas les 150 000 lignes je suppose? La partie importante c'est surtout les index qui correspondent aux clauses WHERE, donc tu peux avoir besoi d'avoir ces colonnes-là + les colonnes de tri dans ton index.

Ce serait évidemment plus facile si tu nous indiquais ta requête et ton schéma (et le résultat d'un explain).

Jacques.

[Neoxy]

Hello,

J'avais complètement oublié qu'on pouvait poser un index sur plusieurs colonnes, alors j'ai effectué quelques tests... et les résultats sont assez bizarres, voici ma requète :

select id, ref, titre, descr, ville, prio, activ
FROM table
INNER JOIN table2 ON (table.id = table2.annonce) 
LEFT JOIN table3 ON (table3.id = table.id and table3.lang = "fr") 
where table.activ=1  and table2.zone =43  
[b]order by  prio desc,  last_update desc[/b]  
limit 0, 10

Ce matin, j'avais un index sur prio, et un autre index sur last_update, du coup, lorsque j'enlevais last_update desc, ou prio desc, la vitesse de ma requète était bien bien plus rapide contre 0,50 secondes lorsqu'il y a les deux

Du coup, je viens de supprimer mes deux index, et j'ai créé un nouvel index sur les 2 colonnes prio et last_update... Et la requête est maintenant beaucoup plus lente (plusieurs secondes d'execution...)

Voici un explain :

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE table index PRIMARY tri_ann 9 NULL 151300 Using where

1 SIMPLE table2 eq_ref PRIMARY PRIMARY 6 BASE.table.id,const 1 Using where; Using index

1 SIMPLE table3 eq_ref PRIMARY PRIMARY 6 BASE.table.id,const 1

Ce qui est vraiment bizarre, c'est que lorsque je change where table2.zone =43 par where table2.zone =2, la vitesse d'execution varie énormément...

Donc, j'avoue que je suis bien bien perdu !!!

[jcaron]

Ca aiderait avec le schéma, on ne sait même pas dans quelle table sont prio et last_update... Avec une idée de la tailles des 3 tables, et de la "sélectivité" des tables/critères (par exemple savoir combien il y a de lignes dans table2 pour une valeur de table2.zone) ce serait encore mieux.

Jacques.

[Neoxy]

Ca aiderait avec le schéma, on ne sait même pas dans quelle table sont prio et last_update... Avec une idée de la tailles des 3 tables, et de la "sélectivité" des tables/critères (par exemple savoir combien il y a de lignes dans table2 pour une valeur de table2.zone) ce serait encore mieux.

Jacques.

Tous les champs sélectionnés proviennent de la table1

La table 2 contient 450 000 enregistrements et contient 2 colonnes (id, zone)...

La table 3 contient 750 000 enregistrements et contient 3 colonnes (id, lang, texte)...

Les écarts de vitesses se ressentent réellement en tripotant l'order by, sachant que prio est un tinyint(2) et last_update est un datetime...

Modifié 7 Juin 2010 par Neoxy

[jcaron]

Pour une valeur donnée de table2.zone, il y a combien de lignes dans table et table2?

Tu peux essayer:

- index sur table2(zone) + index sur table(activ,id,prio,last_update)

- index sur table2(zone) + index sur table(id,activ,prio,last_update)

- index sur table2(id) + index sur table(active,prio,last_update)

Dans tous les cas je pense qu'il te faut un index sur table3(id,lang) (ou lang,id qui sera probablement un poil plus rapide)

Jacques.

[Neoxy]

Pour une valeur donnée de table2.zone, il y a combien de lignes dans table et table2?

Tu peux essayer:

- index sur table2(zone) + index sur table(activ,id,prio,last_update)

- index sur table2(zone) + index sur table(id,activ,prio,last_update)

- index sur table2(id) + index sur table(active,prio,last_update)

Dans tous les cas je pense qu'il te faut un index sur table3(id,lang) (ou lang,id qui sera probablement un poil plus rapide)

Jacques.

Il y a entre 1 et 4 enregistrements dans la table2 pour un enregistrement de la table1

Il y a entre 1 et 6 enregistrements dans la table3 pour un enregistrement de la table1

En ce qui concerne les indexs, je les ai bien placé la ou il faut... De plus, pour id ou autres, ce sont déjà des clés primaires... Je pense qu'on ne peu pas créer d'indexes sur une clé primaire non ? (en tout cas, phpymadmin n'est pas content quand je crée un indexe sur un champs qui est déjà index ou clé primaire)...

Merci jacques pour ta réactivité et tes réponses ..

[Neoxy]

Erf,

A force de supprimer et de recréer les indexes, mes requêtes s'executent maintenant en plusieurs secondes...

Je suis très embeté... D'ou cela peut-il venir ?

[jcaron]

Tu as essayé les 3 combinaisons d'index? Que donnent les explain dans les 3 cas?

En ligne de commande en tous cas aucun souci pour créer un index sur deux colonnes dont l'une est la clef primaire.

Jacques.

(messages croisés)

Si c'est très lent maintenant, c'est que tu as du supprimer un index vraiment utile, et ça devrait se voir dans l'explain...

Jacques.

[Neoxy]

Dans tous les cas je pense qu'il te faut un index sur table3(id,lang) (ou lang,id qui sera probablement un poil plus rapide)

Jacques.

Mais si j'ai déja une clé primaire sur ces deux champs, ça devrait être suffisant, non ?

[jcaron]

Oui, une clef primaire implique un index. Si tu as une clef primaire sur (id,lang) ou (lang,id) ça suffit. Le deuxième devrait être un poil plus rapide dans le cadre de cette requête-là (question d'agencement de l'arbre).

Jacques.

[Neoxy]

Hello Jacques,

Ca va enfin beaucoup beaucoup mieux, mes requêtes s'exécutent en 0,01 secondes, c'est beaucoup mieux

Du coup, j'ai revu les indexes et j'ai suivis ce conseil : - index sur table2(Zone) + index sur table(active,prio,last_update)

Je vais jouer encore avec les indexes pour voir si je peux encore améliorer les perfs

Merci encore beaucoup !!!

[Neoxy]

Hello,

J'ai pu remarqué que l'ordre des colonnes dans la déclaration des clés primaires ou indexes pouvaient avoir des répercussions dans les performances, en l'occurrence, ils ne se comportent pas de la même façon...

Par exemple : primary(id, zone) va être différent de primary(zone, id)...

Pareil pour index(prio, last_update) se comporte différement de index(last_update, prio)

En fonction de quoi devons nous choisir l'ordre des colonnes impliqués dans des clés primaires ou indexes...???

[jcaron]

La combinaison que tu indiques n'est pas l'une des 3 que j'indiquais moi :-/

Et oui, c'est parfaitement normal que l'ordre des colonnes influe sur le résultat. Imagine un annuaire téléphonique classé par nom puis par prénom, et un autre classé par prénom puis par nom. Cherche tous les abonnés dont le nom est DUPONT. Lequel tu préfères?

Le but est donc de voir comment tu penses qu'il est idéal qu'il exécute ta requête, et de lui donner les outils pour le faire.

Là, en gros, il y a deux grandes options:

- on commence par chercher les lignes de table2 qui correspondent à la zone donnée, puis on va chercher dans table les lignes correspondantes, on trie, et on limite, puis dans table3 les lignes correspondantes

- on lit table dans l'ordre voulu, et on va vérifier pour chaque ligne qu'il y a une ligne dans table2 qui nous plait (avec la bonne zone), et la ligne de table3 qui va avec, jusqu'à ce qu'on en aie assez

Les deux premières combinaisons d'index que je donnais correspondent à la première option, la 3e à la deuxième option.

Jacques.

[Neoxy]

Yep,

Ben pendant que je fesais mes tests, je suis finalement revenu sur cette solution : index sur table2(zone) + index sur table(activ,id,prio,last_update)

Les performances sont excellentes, je n'avais jamais pensé qu'on pouvais gérer les indexes de cette façon !!

Du coup, je suis à fond sur mes tests...

Et merci encore