Optimiser une requête "de position"

[winclick]

Bonjour,

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

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 :

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]

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

[raptor]

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]

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]

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 :

date_inscription
date_derniere_visite
points
sessid
email

[Kioob]

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

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 :

show indexes from membres

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

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]

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

Voila un explain :

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 :

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]

Bonjour,

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]

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]

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 :

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.

Modifié 17 Avril 2008 par Kioob

[winclick]

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]

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.