urne electorale

Vote digg like et optimisation

Comment optimiser mysql pour un classement de contenu par dig et bury

systeme digg like

Date de publication : 2009-04-20 08:41:25

Aujourd'hui je vais aborder un problème que j'ai rencontré lorsque j'ai souhaité ajouter un système de vote à la digg pour des contenus sur un CMS.

Explication du système de vote digg like

Un système de vote à la digg est un des systèmes de votes les plus simples pour savoir si vos visiteurs apprécient ou non vos contenus. Il consiste à poser une question oui-non à vos visiteurs du genre "Avez-vous aimez ce contenu ?" et de leurs proposer deux choix :
- "Oui" assimilable à l'action digg
- "Non" assimilable à l'action bury
Ce système de vote permet de limiter les choix du visiteurs et obtient généralement un taux de transformation plus performant que les systèmes de notations classiques (par étoile ...) car l'action est simple pour l'utilisateur.

Structure de la table de contenu

Présentation faite du système de vote voici maintenant une table de contenu type avec les champs pour stockés les votes dig et bury des utilisateurs :


CREATE TABLE IF NOT EXISTS CONTENTS (
  CON_ID mediumint(8) unsigned NOT NULL auto_increment,
  CON_TITLE varchar(150) NOT NULL,
  CON_TEXT text NOT NULL,
  CON_DIG smallint(6) NOT NULL,
  CON_BURY smallint(6) NOT NULL,
  PRIMARY KEY  (CON_ID),
);

Avec :
- CON_ID : identifiant du contenu
- CON_TITLE : titre du contenu
- CON_TEXT : corps de l'article
- CON_DIG : nombre de votes positifs
- CON_BURY : nombre de votes négatifs
Les champs CON_DIG et CON_BURY sont volontairement des entiers signés, vous comprendrez par la suite.

Requête pour un digg

Voici la requête qui correspond à un vote positif de l'utilisateur pour le contenu 1 :


UPDATE CONTENTS SET CON_DIG=CON_DIG+1 WHERE CON_ID = 1

Requête pour un bury

Et maintenant la requête qui correspond à un vote négatif pour le contenu 1 :


UPDATE CONTENTS SET CON_BURY=CON_BURY+1 WHERE CON_ID = 1

Requête de tri des contenus les plus pertinents

Après avoir peuplé ma table de lorem ipsum je lance alors ma première requête sensée refléter les votes des utilisateurs :


/*Sélection des contenus en les triant selon les votes des utilisateurs*/
SELECT CON_ID FROM CONTENTS ORDER BY CON_DIG DESC, CON_BURY ASC

Je lance un explain pour voir si la requete est optimisée et là horreur : using filesort.
Bon personnellement je m'en doutais un peu sans index il ne faut pas rêver donc je rajoute l'index suivant à la table CONTENTS :


ALTER TABLE CONTENTS ADD INDEX DIG (CON_DIG,CON_BURY);

Je relance la même requête :


SELECT CON_ID FROM CONTENTS ORDER BY CON_DIG DESC, CON_BURY ASC

Et la toujours la même chose : using filesort.

Optimisation de la requête de tri

Aprés quelques instants de réflexion je me dis que mysql n'utilise pas l'index DIG à cause de l'order by ASC et DESC et qu'il n'arrive pas à optimiser la requête car il ne peut pas utiliser deux indexes avec des sens de tris différents (un croissant et l'autre décroissant).

Je décide alors de tester la requête suivante :


EXPLAIN SELECT CON_ID FROM CONTENTS ORDER BY CON_DIG DESC, CON_BURY DESC

Résultat : using index. Hip ! Hip ! Hip ! HouraaaAAAAaa !

C'est bon j'ai trouvé la solution il faut que je stocke les vote bury en négatif. Ma requête de bury se transforme donc en :


UPDATE CONTENTS SET CON_BURY=CON_BURY-1 WHERE CON_ID = 1

Vous comprenez maintenant pourquoi mes colonnes CON_DIG et CON_BURY sont des entiers signés et je peux utiliser la version optimisée le requête de tri :


SELECT CON_ID FROM CONTENTS ORDER BY CON_DIG DESC, CON_BURY DESC

Et enfin pour afficher à mes utilisateurs un nombre positif pour les bury :


SELECT CON_ID,CON_DIG AS DIG,ABS(CON_BURY) AS BURY FROM CONTENTS ORDER BY CON_DIG DESC, CON_BURY DESC

Voilà c'est fini j'espère que cela n'a pas été trop fastidieux à comprendre le cas échéant bon courage ;)

Image : jahovil

 
 

b1n@sp1n