Webmaster Hub > Optimiser une requête "de position"

Version complète: sur le forum Webmaster Hub : Optimiser une requête "de position"

Webmaster Hub > Création et exploitation de Sites Internet > Les langages du Net > SQL

winclick

jeudi 17 avril 2008 à 08:28

Bonjour,
En regardant les logs apache je m'aperçoit qu'une de mes requêtes est un peu longue à être traité

CODE

SELECT username, points, sexe FROM membres WHERE username!='' AND username!='toto' AND active=1 AND moderateur!=1 AND points<='49' ORDER BY points DESC LIMIT 0, 1;

Elle permet de récupérer le joueur qui est le score précédent par rapport au joueur toto (dans le cas d'un top des joueurs).
La même chose pour connaître le joueur suivant :

CODE

SELECT username, points, sexe FROM membres WHERE username!='' AND username!='toto' AND active=1 AND moderateur!=1 AND points>'240' ORDER BY points LIMIT 0, 1;

Comment faire pour améliorer la rapidité d'execution de cette requête ?
Merci d'avance pour votre aide.

Charlie

iNCiTE Web

jeudi 17 avril 2008 à 08:42

Tu as bien un index sur les champs utilisés dans le where dans ta table ?

raptor

jeudi 17 avril 2008 à 09:13

Pas évident l'index sur ces champs la, moderateur étant a priori l'équivalent d'un bool, active aussi, points est variable, et l'autre champs étant le pseudo il ne servira pas au tri.
Combien d'enregistrement a ta table ?
Quelle est la durée d'exécution de cette requete ?

Kioob

jeudi 17 avril 2008 à 09:54

Au contraire je pense qu'un index sur "points" serait utilise ici.

A vérifier à coup d'explain, mais je ne vois pas ce qui clocherait dans ce cas. (Si ce n'est que l'index sera probablement mis à jour très souvent).

winclick

jeudi 17 avril 2008 à 11:55

Cela contient mes membres donc pas mal d'enregistrements (près de 100 000).
moderateur est de type tinyint(1) et prend une valeur de 0 ou 1.

J'ai des index sur :

CODE

date_inscription
date_derniere_visite
points
sessid
email

Kioob

jeudi 17 avril 2008 à 13:15

Essayes de faire un explain (et fais nous un copier/coller du résultat) :

CODE

EXPLAIN SELECT username, points, sexe FROM membres WHERE username!='' AND username!='toto' AND active=1 AND moderateur!=1 AND points<='49' ORDER BY points DESC LIMIT 0, 1;

Idem, pour être certain de tes indexes :

CODE

show indexes from membres

Et à tout hasard, après le premier copier/coller, lance ça pour voir :

CODE

optimize table membres; EXPLAIN SELECT username, points, sexe FROM membres WHERE username!='' AND username!='toto' AND active=1 AND moderateur!=1 AND points<='49' ORDER BY points DESC LIMIT 0, 1

winclick

jeudi 17 avril 2008 à 15:15

Je vous ai donné les index dans mon précédent post, ils sont corrects.

Voila un explain :

CODE

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE membres range PRIMARY,points PRIMARY 22 NULL 40895 Using where; Using filesort

et après optimisation :

CODE

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE membres range PRIMARY,points PRIMARY 22 NULL 42917 Using where; Using filesort

TheRec

jeudi 17 avril 2008 à 15:31

Bonjour,

CITATION(raptor @ jeudi 17 avril 2008 à 10:13)

Quelle est la durée d'exécution de cette requete ?

Il serait bon de répondre à cela et également de préciser l'environnement utilisé pour l'exécution de cette requête (ressources disponibles, API utilisée pour l'accès au serveur de base de données).

winclick

jeudi 17 avril 2008 à 15:36

La requête est exécutée via php de manière standard : mysql_query().
Traitement en 1.7815 sec.
Traitement en 1.2788 sec.
Traitement en 1.1613 sec
Voila en gros

Kioob

jeudi 17 avril 2008 à 16:51

Si je demandais le "show indexes" c'était pour avoir le détail (indexes multiples, uniques, clé primaire...).

On va donc supposer que la clé primaire est le champ "username" : et dans ce cas MySQL n'utilise pas le "bon" index pour sa requête. Précises lui d'utiliser l'index "points" pour voir ce que cela donne.

Une autre approche pourrait être :

CODE

EXPLAIN
SELECT username, points, sexe
FROM membres
WHERE points = ( select max(points) where points <= 49 and username!='' AND username!='toto' AND active=1 AND moderateur!=1 )
and username!='' AND username!='toto' AND active=1 AND moderateur!=1
ORDER BY points DESC LIMIT 0, 1

Pas certain que ce soit beaucoup mieux, mais au moins ça devrait forcer l'utilisation de l'index sur "points", et fortement limiter le nombre d'enregistrements traités. Quitte à utiliser une vue après pour rendre le code plus clair.

winclick

jeudi 17 avril 2008 à 19:42

CODE

1 PRIMARY membres index_merge PRIMARY,points points,PRIMARY 4,22 NULL 126 Using intersect(points,PRIMARY); Using where
2 SUBQUERY membres range PRIMARY,points PRIMARY 22 NULL 40768 Using where

Traitement en 3.0481 sec.
Traitement en 2.2512 sec
...

Visiblement c'est aussi long, voir plus

Enfin dans tout les cas il n'y a rien qui vous a choqué dans la requête, c'est donc qu'elle est déjà pas construite à la roumaine

(au cas ou).

Kioob

jeudi 17 avril 2008 à 19:48

Erf pas glop, même comme ça il ne veut pas utiliser "points". Il faudrait lui spécifier directement d'utiliser cet index (je te laisse te reporter à la doc pour la syntaxe exacte).

Et as tu essayé de créer un index unique sur username + points ainsi que points + username ? Parce que c'est quand même étrange d'avoir une requête si longue avec si peu de données.

PS : si on devait rechigner, perso je trouve dommage d'être obligé d'ajouter un "username != ''" alors qu'il s'agit à priori de la clé primaire.

Ceci est une version "bas débit" de notre forum. Pour voir la version complète avec plus d'information, la mise en page et les images, veuillez cliquer ici.