MySQL vs PHP: tri des données en utilisants des variables MySQL

Contexte:
Un site propose à ses membres de voter pour élire le membre du mois. Chaque mois les compteurs sont remis à zéro.
Sur une page dédiée le site affiche l’historique des 5 meilleurs membres de chaque mois sur une période d’un an.

On pourrait s’en aucun problème extraire les infos de la base de données  et trouver les 5 meilleurs score de chaque mois avec un script PHP. Mais que va devenir le temps de génération de la page quand le volume des votes atteindra quelques millions de ligne sur un an.

Pour rappel les base de donnée ne servent pas qu’a stocker des données, mais aussi à les trier. Et sur ce dernier point elles sont souvent les mieux placées d’un point de vue performance.

Écrire la requête SQL qui va nous permettre de calculer la note de chaque membre chaque mois est relativement simple:

 
SELECT id_membre, MONTH( date_vote ) AS mois, AVG( note ) AS moyenne
FROM vote
WHERE date_vote BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY mois, id_membre
ORDER BY mois ASC , moyenne DESC;

Tout le problème est de limiter le résultat aux 5 meilleurs ‘moyenne’ de chaque mois.

Faisons abstraction du langage et analysons la marche à suivre.

1. Je dois calculer la moyenne de chaque profil pour chaque mois
2. Pour chaque mois je dois trier les profils par leur moyenne (ordre décroissant sur la moyenne)
3. Je ne garde que les 5 premiers profils par mois.

Le point 1 est assuré par la requête SQL ci dessus.

Le point 2 est le plus délicat, il faut que j’ordonne mes profils. Pour cela je vais écrire une requête qui utilisera le retour de la requête du point 1 comme source d’information. Le but du jeu est d’utiliser une condition IF dans ma requête SQL qui créera pour chaque profile sa position. 1 pour le meilleur, 2 pour le second, 3……..
Pour cela je vais avoir besoin d’incrémenter un compteur qui sera remis à zéro à chaque changement de mois. Il va donc me falloir 2 variables utilisateurs, une pour le compteur et l’autre pour le mois en cours.

 
SET @mois :=0, @rownum :=0;
SELECT IF( mois = @mois , @rownum := @rownum +1, @rownum :=1 ) AS rank, @mois := temp.mois, temp . *
FROM (
    SELECT id_membre, MONTH( date_vote ) AS mois, AVG( note ) AS moyenne
    FROM vote
    WHERE date_vote BETWEEN '2011-01-01' AND '2011-12-31'
    GROUP BY mois, id_membre
    ORDER BY mois ASC , moyenne DESC
) temp;

Quelques explications: La première ligne permet de définir 2 variables utilisateur @mois et @rownum.  Ensuite j’utilise un IF pour créer la colonne rank. Si vous n’est pas famillié avec les IF dans MySQL celui ci se comporte comme une écriture ternaire. Si la condition est vérifiée il exécute la première instruction sinon il exécute la deuxième. La ‘,’ faisant office de séparateur.

On obtient le même résultat qu’avec la premier requête mais agrémenté d’un champs ‘rank’ qui est l’ordre des profils.
Pour le point 3 il suffit de refaire une requete SQL exploitant ce résultat et de limiter le retour au ‘rank’ inférieur ou égale à 5. Ce qui nous donne:

 
SET @mois :=0, @rownum :=0;
SELECT rank, id_membre, mois, average
FROM (
 
    SELECT IF( mois = @mois , @rownum := @rownum +1, @rownum :=1 ) AS rank, @mois := temp.mois, temp . *
    FROM (
 
       SELECT id_membre, MONTH( date_vote ) AS mois, AVG( note ) AS moyenne
       FROM vote
       WHERE date_vote BETWEEN '2011-01-01'AND '2011-12-31'
       GROUP BY mois, id_membre
       ORDER BY mois ASC , moyenne DESC
 
   ) temp
 
) temp2
WHERE temp2.rank <=5;

Et voilà le résultat. C’est quand même plus joli qu’un  gros algo PHP non?
Point de vue performance sur mon serveur de test (asus eeebox),  avec une table de 2 millions de ligne je suis sous la seconde, plutôt honorable.

Attention il ne faut pas oublier de setter les variables sinon le résultat sera inattendu.
Il faut également que les variables soient setter dans la même transaction que la requête ou que tout passe dans le même mysql_query() si vous n’utilisez pas les transactions.

Références:
http://dev.mysql.com/doc/refman/5.0/fr/control-flow-functions.html
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html