Optimiser requete sql

[Robinson]

Bonjour,

J'ai une requête qui m'est utile à optimiser.
Il s'agit de trouver le classement d'un membre.

# Query_time: 5  Lock_time: 2  Rows_sent: 1  Rows_examined: 386954
SELECT count( table1.id ) AS nb FROM table1, table2 
			WHERE table1.id = table2.id AND (table1.etat = 2 OR table1.etat = 5) 
			AND (victoires *3 + defaites * ( -3 ) + nuls) > '$points';

Il s'agit de calculer le nombre de personnes étant meilleur que lui.

Un count devrait être rapide mais ce n'est pas le cas malgré des index sur les champs id et etat.

Une requête qui sélectionne TOUS les membres et ensuite une recherche via php du classement dans la boucle while est plus rapide que cette requête (enfin plus rapide, elle prend quand même 3 secondes lol)

Si vous voyez une solution, je suis tout ouï, merci.

 

[Selection A]

Je te suggère de créer une colonne 'points' dans ta table et de rajouter un index sur cette nouvelle colonne

 

[Robinson]

Voui cela aiderai beaucoup il est vrai. ^^

 

[Sacha | Bankexam.fr]

Déjà tu peux faire un count(1) au lieu d'un count(table1.id). C'est une légère optimisation mais c'est déjà ca de pris.

Ensuite victoires / defaites ca vient de quelle table?
C'est quoi la relation entre table1 et table2?
D'ailleurs pourquoi la jointure sur table2? (jimagine que victoires/defaites viennent de table2..)

Je pense que vous ne voulez pas créer de colonne points car le calcul se veut dynamique.

As tu fais un explain de ta requete pour voir si ta base passe bien par les index? Si c'est pas le cas tu peux lui imposer l'utilisation des dits index...

 

[Robinson]

victoires, défaites, nuls, oui table 2.

Et oui je ne voulais pas créer de colonne points pour m'éviter des infos supplémentaires dans la base et de devoir mettre celle-ci à jour souvent.
Mais le jeu en vaut sûrement la chandelle au niveau des SELECT.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table2 ALL PRIMARY NULL NULL NULL 194357 Using where
1 SIMPLE table1 eq_ref PRIMARY,etat PRIMARY 3 bdd.table2.id 1 Using where

Hum visiblement, l'index de la table 2 n'est pas utilisé, non ? En utilisant FORCE INDEX, le résultat est le même pourtant :/

 

[Sacha | Bankexam.fr]

Quelle est la volumétrie de la table1 qui est en état 2 ou 5?

Ta foreign key de la table 2 est sur son id? Si oui je comprend pas la relation entre tes 2 tables... (1 -> n non?) (c'est à dire que pour 1 enregistrement de la table 1, tu peux avoir n enregistrements dans la table 2).

Tu peux essayer de la réécrire comme suit voir si tu gagnes en perf.
SELECT count(1) AS nb
FROM table2
WHERE table2.id in (SELECT table1.id from annales table1 where table1.etat in (2, 5))
AND (calcul *3 ) >points

Ensuite dans ta database, quel est ton parametre de cache?
Fais un show variables et donne nous la valeur de ton query cache size.

 

[Robinson]

Oh mais oui, c'est vrai, je viens de passer à mysql5, je peux enfin faire des sous requêtes

Les tables 1 et 2 n'ont qu'un seul enregistrement commun, d'ailleurs, je pourrai les rassembler... mais cela créerai une table avec près de 30 champs, ce qui commence à être bordélique. (et pour les rassembler et changer toutes les requêtes des pages, je vais y passer un sacré moment même si cela devrait être très bénéfique)
Au départ, il pouvait y avoir plusieurs champs communs mais j'ai abandonné l'idée en route et n'ai pas revu cela.



PS : c'est quoi ton "from annales table1" ? ^^

 

[Robinson]

Oula, je viens de tester et c'est pire...

# Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 194542

(en fait non j'ai retesté l'autre et cela fait pareil ^^)

 

[Sacha | Bankexam.fr]

Alors mes maigres connaissances en sql n'auront pas suffit !

Personnellement je pense que pour une relation 1:1 entre 2 tables il ne faut pas créer de table d'extension à moins d'avoir déjà plus de 100 colonnes dans la 1ere et n'avoir que des informations rarement utilisées dans la 2nde...

Fais un test sur une autre base, prend ta volumétrie de prod, copie toute la table 2 dans la table 1, et rejoues ta requete, tu verras si tu y gagnes (je pense que oui).

Enfin vérifies que tu as un query_cache_size suffisant, tu y gagneras grandement sur des requetes qui ont déjà été jouées une fois ! (SHOW VARIABLES en sql)

 

[Robinson]

Du côté du cache, pas de problème, il est même trop large, (seulement 10% utilisé)

Rassembler le tout en une table... ça me fout un peu les boules vu le travail que ça va demander ^^

Ceux sont les deux tables le plus souvent utilisées et pas toujours "jointes".

Mais je me garde cette solution sous le coude pour des optimisations plus radicales futures.