Optimisation SQL 300k lignes

Bonjour à tous !

Voilà, j'ai crée récemment un site de statistiques pour un jeu.
Ce site récupère toutes les 24h les informations sur les joueurs en les historisant dans une table

Chaque jour à 00:00 après avoir récupéré ces stats, je fais, grâce à la table historique, le calcul de l'évolution en points du joueur.

Je fais donc :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SELECT joueur_hist_today.id, joueur_hist_today.pseudo, (joueur_hist_today.score - joueur_hist_yest.score) as evolution_score from joueur_hist joueur_hist_today inner join joueur_hist joueur_hist_yest on joueur_hist_today.id = joueur_hist_yest.id and date(joueur_hist_today.date) = date(joueur_hist_yest.date) + INTERVAL 1 DAY and date(joueur_hist_today.date) = CURRENT_DATE order by evolution_score desc limit 3

---

On a donc un calcul... Et cette table fait 300.000 lignes maintenant.
La requête met 19s et prend environ une seconde chaque jour.

Donc je me suis dit... il y a t'il moyen d'optimiser cette requête ( les INDEX sont normalement déjà en place )

Merci d'avance :)

Quels sont les index posés ?
Logiquement il faudrait poser un index sur joueur_hist (date, id).
Mais pour cela, il faudrait aussi que date soit au bon format ... or manifestement là il ne doit pas l'être, puisqu'il y a des transformations dessus - utilisation de "date()" qui doit faire un arrondi ? Je suggère donc d'avoir directement la "date" au bon format. À ce moment là, la requête se ré-écrit :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT joueur_hist_today.id, joueur_hist_today.pseudo, (joueur_hist_today.score - joueur_hist_yest.score) as evolution_score from joueur_hist joueur_hist_today inner join joueur_hist joueur_hist_yest on joueur_hist_today.id = joueur_hist_yest.id and joueur_hist_yest .date = CURRENT_DATE - INTERVAL 1 DAY and joueur_hist_today.date = CURRENT_DATE order by evolution_score desc limit 3

---

Ce qui permet de faire une jointure sur les id entre le scan de l'index (jour J) et le scan de l'index (jour J-1).

Merci :)

Alors pour la date, elle était en fait dans le bon format, quand je m'étais renseigné sur la fonction CURRENT_DATE j'avais vu un exemple avec ces fonctions dates donc j'ai fait de même et comme cela marchait, j'ai laissé comme ça. Mais comme en fait, la date est déjà formatée j'ai pu enlever ces fonctions.

Pour les index oui je m'étais renseigné sur quels index poser, et j'avais bien choisi ceux ci pour cette requête.

J'ai donc fait ces modifications et je gagne 3s sur ma requête.

Par contre, que veux tu dire par :"Ce qui permet de faire une jointure sur les id entre le scan de l'index (jour J) et le scan de l'index (jour J-1)." ?

Vu que pour moi c'est ce qui est déjà en place

Sinon, pour le temps que met la requête, l'opération que je fais dedans est la principale cause, mais je suppose que l'on n'y peux pas grand chose ?

Quel SGBD ?
Y a t'il seulement un score par joueur et par jour ?

Si oui en fonction du SGBD je verrais plutôt l'utilisation des fonctions de fenêtrage ou une sous-requête dans le SELECT plutôt qu'une auto-jointure.

Concernant les index Rei Ichido parlait d'1 index sur les 2 colonnes (et pas 1 index pour chaque colonne) est ce vraiment l'existant ?