J'ai beaucoup de théories sur le design d'un forum (que je teste parfois sur vous à votre insu
), particulièrement de son aspect technique. Aujourd'hui, je vais partager avec vous quelques observations sur une des requêtes les plus exécutées par un logiciel de forum : récupérer la liste des sujets ("viewforum", ci-après). Pour les besoins de mes exemples, j'utiliserai le schéma de phpBB 2, mais le problème est commun à la plupart des logiciels de forum. De plus, j'utiliserai librement certains anglicismes (topic, post) parce que leur équivalent français m'irrite 
Alors, cette requête viewforum, qu'est-ce qu'elle a de particulier ?
C'est l'une des pages les plus vues.
Je n'ai pas de stats récentes à ce sujet mais la dernière fois que j'ai vérifié elle représentait plus de 25% des impressions. C'était avant que Firefox (et IE ?) n'utilise systématiquement le cache lors du retour en arrière (bouton Retour) donc ce nombre a dû baisser, mais il est certainement resté conséquent.
Elle est pratiquement incachable
La table de topics est continuellement mise à jour par les nouveaux posts, voire même la lecture des anciens ! (conversion de la charge de lecture en charge d'écriture à cause de la mise à jour du compteur de vues... mais ce sera pour une autre fois) Résultat : vous pouvez oublier le query cache de MySQL.
Elle s'alourdit continuellement
La liste des topics s'allonge de jour en jour, à moins de régulièrement délester ou archiver ses sujets. La conséquence est qu'il faut trier de plus en plus de résultats. De plus, accèder aux plus anciens topics demander de passer (skip) de nombreux résultats.
En parlant de tri justement...
Récupérons la liste des topics du forum 3 pour voir
Code : Tout sélectionner
EXPLAIN
SELECT *
FROM phpbb_topics
WHERE forum_id = 3
AND topic_type IN (0, 1)
ORDER BY topic_type DESC, topic_last_post_id DESC
LIMIT 10;Code : Tout sélectionner
+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | phpbb_topics | ref | topic_type,forum_id | forum_id | 2 | const | 2527 | Using where; Using filesort |
+----+-------------+--------------+------+---------------------+----------+---------+-------+------+-----------------------------+Code : Tout sélectionner
ALTER TABLE phpbb_topics DROP INDEX forum_id, ADD INDEX viewforum (forum_id, topic_type);Code : Tout sélectionner
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | phpbb_topics | range | topic_type,viewforum | viewforum | 3 | NULL | 2308 | Using where; Using filesort |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-----------------------------+Code : Tout sélectionner
ALTER TABLE phpbb_topics DROP INDEX viewforum, ADD INDEX viewforum (forum_id, topic_type, topic_last_post_id);Code : Tout sélectionner
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | phpbb_topics | range | topic_type,viewforum | viewforum | 3 | NULL | 2496 | Using where |
+----+-------------+--------------+-------+----------------------+-----------+---------+------+------+-------------+Code : Tout sélectionner
SELECT *
FROM phpbb_topics
WHERE forum_id = 3
AND topic_type IN (0, 1)
ORDER BY topic_type DESC, topic_last_post_id DESC
LIMIT 10
OFFSET 2970;Code : Tout sélectionner
SELECT *
FROM phpbb_topics
WHERE forum_id = 3
AND topic_type IN (0, 1)
ORDER BY topic_type ASC, topic_last_post_id ASC
LIMIT 10
OFFSET 20;Une autre façon de procéder serait d'abandonner le système de numérotation de page tel qu'il est utilisé sur la plupart des forums. D'ailleurs, sur les forums les plus actifs, ce système montre rapidement ses limites : le temps de lire la première page et cliquer sur le lien vers la page 2, de nouveaux topics ont été postés et vous retombez sur ceux que vous venez juste de lire ! À la place, on pourrait imaginer de naviguer par date. Ainsi, plutôt que de lister les sujets de "la page 2", vous demandez au forum de lister les topics "plus anciens que ceux en cours" (viewforum.php?page=2). Par exemple, "plus ancien que 29-07-2007 14:31:12" (viewforum.php?older=1185712272). Là, plus aucune chance de retomber sur les mêmes sujets. De plus, l'index couvre réduit encore plus les résultats possibles (key_len passe de 3 à 7). En revanche, il existe au moins un défaut à cette solution : un timestamp n'est pas unique, si deux sujets partagent le même timestamp, il se pourrait qu'un des deux se retrouve coincés "entre deux pages" si son jumeau se trouve être le dernier de la page. De plus, il devient impossible de numéroter les pages donc il faut trouver un autre repère pour les utilisateurs pour éviter qu'ils se sentent perdus.
La suite plus tard...
là on parlait vraiment de la structure en elle-même je crois