Une règle d'or lorsque l'on conçoit une requête MySQL : ne ramener
que ce dont on a besoin. Plus le nombre de données à ramener sera
faible, plus ce sera rapide (à requête équivalente bien entendu).
Prenons l'exemple d'une structure de table destinée à stocker des
informations sur les membres d'un site, cette table s'appellera
« membres » :

Les champs :

La fermeture de la connexion :

La clause LIMIT :

Imaginez un livre. Plus le livre contient de pages, plus vous allez
mettre du temps à rechercher l'information désirée. C'est pour cela
qu'il existe en général une table des matières au début ou à la fin du
livre. Cette table vous permet d'accéder très rapidement sans feuilleter
le livre à l'information que vous recherchez. Un index en MySQL
fonctionne sur ce principe, à savoir qu'il va vous permettre d'accélérer
nettement (une requête 10 fois plus rapide n'est pas un cas
exceptionnel dans le cas d'un index bien utilisé) vos requêtes de
sélection. Il faut cependant être conscient qu'un index ralentit les
requêtes d'insertion ou de mise à jour (dans une moindre mesure) de
donnés, il faut donc les utiliser avec parcimonie. Indexer tous les
champs d'une table MySQL est stupide. D'une manière générale, il faut
indexer les champs apparaissant dans une clause WHERE de la requête.
Nous allons rajouter un champ date_inscription à notre
table de membres, puis nous utiliserons une requête type pour récupérer
les membres étant inscrits depuis moins d'une semaine. Voici la nouvelle
structure de la table :

Une requête type consistant à récupérer les membres inscrits depuis
moins de 7 jours consiste à faire ceci :

<?php

 mysql_connect(‘hote',‘user',‘passe') OR die(‘Erreur de connexion à la base');

 mysql_select_db(‘base') OR die(‘Selection de la base impossible');

 $requete=mysql_query(‘SELECT pseudo FROM membres WHERE date_inscription >'.time()-7*3600*24);

 mysql_close();

 while($r=mysql_fetch_array($requete))

 {

echo $r['pseudo'],‘<br />';

 }

?>


Nous voyons donc que le champ « date_inscription » sera utilisé par
MySQL pour savoir si le pseudo du membre doit être retourné ou non. On
constate donc qu'il serait judicieux de placer un index sur le champ
« date_inscription » pour pouvoir accélérer la recherche des pseudos
concernés. Pour ajouter un index dans PHPMyAdmin, on clique sur la
petite icône représentant un éclair, en face du champ
« date_inscription ». L'index est maintenant ajouté et la requête est
optimisée.

Pensez également aux index multi colonnes : Si vous avez une requête
de la forme 

SELECT pseudo FROM membres WHERE date_inscription > X AND id_membre BETWEEN  40 AND 60

Il est judicieux de créer dans ce cas un index sur deux colonnes
(date_inscription et id_membre). Pour ce faire, dans PHPMyadmin,
regardez la zone « créer une clé sur … colonnes ». Mettez « 2″ dans le
nombre de colonnes et cliquez sur Exécuter. Vous avez accès à ceci :

Dans la zone « Type de clé » choisissez « Index ». Choisissez ensuite
comme premier champ « id_membre » et ensuite « date_inscription », puis
cliquez sur « sauvegarder ». Ne vous occupez pas de la colonne « taille » de
de PHPMyadmin, cette colonne ne sert (en gros) que pour les index sur
les champs de caractères (VARCHAR entre autres). L'avantage est double
pour les champs de ce type : Si vous mettez « 2″ dans la zone « taille »,
l'index indexera les deux premiers caractères de chaque mot. Il serait
peu recommandé de mettre la longueur maximale du champ dans cette zone
car vous perdrez en performances et en espace disque.

Il est important de souligner que les index sur les champs de type
numériques sont plus rapides à traiter par MySQL. Ainsi, il est peu
recommandé d'utiliser dans les clauses WHERE les champs de type chaînes
de caractères. Pour sélectionner des informations sur un membre, on
n'utilisera donc pas son pseudo. La requête suivante n'est pas optimisée
(même si un index est placé sur le champ pseudo) :

SELECT nom,prenom FROM membres WHERE pseudo = ‘webmaster'

On préfèrera la requête suivante :

SELECT nom,prenom FROM membres WHERE id_membre = X

D'une part, vous gagnerez en performances, mais d'autre part vous
gagnerez en espace disque car on a été obligé d'indexer le champ
id_membre (vu que c'est un champ AUTOINCREMENTE, il doit être
obligatoirement accompagné d'un index). Donc au lieu d'avoir deux index
(l'un sur le champ id_membre et l'autre sur le champ pseudo) nous n'en
avons qu'un, ce qui cumule les avantages que ce soit en terme de
performances (en écriture ou en lecture) ainsi qu'en espace disque
(indexer un champ de type « chaîne » prend plus de place).

Pour des données peu sensibles (compteur de connectés, système
personnel de sessions utilisant MySQL, etc…) vous pouvez utiliser les
tables de type HEAP (ou MEMORY selon les versions de MySQL). Ce type de
table est très particulier puisque les données sont stockées dans la
mémoire vive. Il n'y a ainsi plus d'accès au disque dur pour récupérer
les données ce qui augmente nettement les performances.

Attention cependant, ce type de tables n'est pas à utiliser pour
stocker des données sensibles, car en cas de redémarrage de MySQL ou/et
de plantage du serveur, les données seront perdues.

Cette clause rajoutée aux requêtes de type SELECT va vous permettre
d'auditer les performances de vos requêtes. Lorsque vous rajoutez le mot
EXPLAIN devant votre requête de type SELECT (dans PHPMyadmin) vous
allez pouvoir avoir quelque chose qui ressemble à ceci (avec des valeurs
pouvant varier bien entendu) :

On constate qu'il y a plusieurs colonnes résultant de cette action.
Voici une explication des colonnes retournées :

• Id : le numéro de la requête SELECT dans la
  requête globale.
• Select_type (quelques valeurs que peut prendre
  cette colonne) :
  • SIMPLE : SELECT simple (sans UNION ni sous-
    requêtes).
  • SUBQUERY : Premier SELECT d'une sous-
    requête.
• Table : Table utilisée.
• Type : type de jointure utilisée. Voici
  quelques types de jointures, du plus rapide au plus lent :
  • system : Cas rare, lorsque la table
    comporte une seule ligne (jointure de type CONST).
  • const : la table possède au plus une seule
    ligne répondant à la valeur demandée qui sera lue dès le début de la
    requête. Ce type de jointure est très rapide.
  • eq_ref : C'est le meilleur type de jointure
    possible. Ce type est utilisé lorsque toutes les parties d'un index
    sont utilisées par la jointure. L'index doit être de type UNIQUE ou
    PRIMARY KEY.
  • ref : Si MySQL ne peut pas retourner une
    seule ligne en fonction de la jointure, toutes les lignes ayant des
    valeurs correspondantes seront lues par MySQL.
  • ref_or_null : Comme le type ref, mais un
    coût supplémentaire pour les recherches ayant des valeurs NULL comme cas
    possible. Il est d'ailleurs recommandé de mettre vos champs en NOT NULL
    pour accélérer les traitements.
  • range : seules les lignes dans un
    intervalle donné seront lues dans l'index.
  • index : même chose que ALL, seul l'index
    est utilisé ce qui peut accélérer légèrement les performances étant
    donné que la taille du fichier d'index est généralement plus faible que
    celle des données.
  • ALL : à bannir, une analyse complète de la
    table est faite.
• Possible_keys : Cette colonne indique quels index
  MySQL va pouvoir choisir pour trouver les lignes correspondant à la
  requête.
• Key : Cette colonne indique l'index que MySQL
  aura finalement utilisé pour trouver les lignes correspondant à la
  requête.
• Key_len : Cette colonne indique la taille de la
  clé utilisée par MySQL.
• Ref : Cette colonne indique quelle colonne ou
  quelles constantes sont utilisées pour sélectionner les lignes de la
  table
• Rows : Estimation du nombre de lignes à parcourir
  avant d'obtenir le résultat. Plus cette valeur est faible, meilleures
  seront les performances.
• Extra : Cette colonne contient quelques
  informations supplémentaires très utiles sur la procédure de résolution
  de la requête. Voici quelques informations utiles :
  • Using filesort : MySQL va avoir besoin
    d'effectuer un second passage pour trier les lignes. Sur un petit nombre
    de lignes retournées ça peut convenir, mais essayez d'éviter d'obtenir
    des Usign filesort avec 1000 enregistrements retournés par exemple…
  • Using Index : MySQL n'ouvre pas la table et
    lit directement les informations dans l'index. C'est très rapide et
    devrait être présent le plus possible dans vos requêtes.
  • Using Temporary : MySQL va créer une table
    temporaire pour contenir le résultat. Cela se passe généralement en cas
    d'utilisation d'une clause ORDER BY sur une colonne différente de celle
    utilisée pour la clause GROUP BY. Cela nuit aux performances.
  • Using Where : Une clause Where sera utilisée
    pour limiter le nombre de lignes retournées.

De nombreux forums (payants ou gratuits) tel que PHPBB ou encore IPB,
Simple Machines Forum, etc… sont très peu optimisés ce qui fait que
dès que les forums en question deviennent fréquentés (avec un nombre de
messages conséquent) le temps de génération de chaque page augmente très
rapidement en parallèle. Ces forums ne supportent donc pas une forte
charge qui pourrait leur être imposée. Face à ce problème, il n'y a
généralement (après avoir désactivé toutes les fonctionnalités inutiles
gourmandes proposées) pas d'autre solution que de changer le serveur,
car reprogrammer entièrement de tels forums est une pure perte de temps
(par où commencer ? il y a tellement de travail…). Le problème de ces
forums est qu'ils utilisent pour sélectionner une tranche de topics (ou
de messages) la clause LIMIT dans leurs requêtes MySQL (mal conçues). La
solution ultime consiste à concevoir soi-même son propre forum. Bien
sûr cela demande du temps et de bonnes connaissances en optimisation PHP
et MySQL (sous peine de refaire les mêmes erreurs). Il existe cependant
de bons forums gratuits open source. Je peux vous en citer un : Punbb.
Ce forum est léger, personnalisable et disponible en français.

Une bonne solution pour réaliser son forum personnel est d'utiliser
les requêtes de type BETWEEN. Il faudra généralement pour cela rajouter
un ou deux champs supplémentaires (deux dans la table des topics et un
dans la table des messages) si vous avez des sous catégories. Sinon un
seul champ par table suffira. Ce champ servira de « marqueur de position »
et c'est sur ce champ que sera fait le BETWEEN, ce qui fait que seuls
les topics de la page désirée seront scannés, le forum tiendra ainsi la
charge même avec plusieurs milliers de topics/messages.