Optimiser sa base de données: utiliser les index.

La première chose à faire après avoir conçu son modèle de base de données est d'utiliser les indexs.
Toute base en environnement de production doit avoir tous ses indexs. 

Qu'est-ce qu'un index ? Prenons un exemple simple: Lorsque vous cherchez le numéro de téléphone de votre ami "Zorg", la première chose que vous faites est d'aller consulter les pages "Z" de votre répertoire. De la, si vous avez classé vos noms par ordre alphabétique, vous n'aurez aucun mal à trouver le numéro de téléphone de Zorg: 0666 !

Un index est un fichier d'enregistrement en plus de votre table de données. Il se compose d'enregistrement comprenant un ou plusieurs champs, de taille fixe (ici, une liste de noms, classés par ordre alphabétique) et les enregistrements associés dans votre table.

Ce qui fait perdre le plus de temps à un serveur de base de données (ici mysql) est d'ouvrir les enregistrements un par un, de chercher la concordance et de passer à l'enregistrement suivant. De plus, plus les enregistrements sont gros, plus ils metterons unitairement de temps a s'ouvrir.
Avec l'index, un tout petit fichier par rapport à votre table, mysql va commencer par le charger ce fichier en mémoire. Ensuite, à la manière de votre répertoire téléphonique, il va chercher les noms, et enfin, quand il aura la liste des enregistrements à ouvrir, il s'adressera à votre grosse table.

J'avais ainsi par le passé une table d'utilisateurs de 30000 personnes, chaquns ayant une 60 aine de colonne.
Executons la requête:

SELECT id FROM UTILISATEURS WHERE NOM='ZORG'

(oui, il y'a vraiment des noms de famille "ZORG" en france).

Temps de recherche: 3.68 secondes => innacceptable !

Mettons maintenant un index:

CREATE INDEX index_nom ON UTILISATEURS (nom);

Et recherchons de nouveau notre ami 'ZORG':

Temps de recherche: 0.00 secondes => bien mieux !

A savoir, les index peuvent porter aussi sur plusieurs colonnes. Imaginons que je cherche un nom sur une région, je peut mettre dans le même index le nom et la région:

CREATE INDEX index_nom_region ON UTILISATEURS (nom, region);

De plus, les index peuvent être crées pour introduire une contrainte. 

Par exemple, je ne veut pas dans ma table utilisateur deux fois le même numéro de sécurité sociale:

CREATE INDEX UNIQUE index_ss ON UTILISATEURS (numerosecu);
 

Avec cet index, non seulement la recherche par numéro de sécu sera bien plus rapide, mais chaque requête essayant d'ajouter un nouvel utilisateur avec le même numéro de sécu échouera.

Tous les champs qui peuvent être recherchés doivent être indexés, c'est la première chose à faire.
Certains champs typiques seront efficaces pour mysql à indexer:

• Les clés primaires de chaque table. En les désignant comme "PRIMARY KEY", elles seront indexées.
• Les clés étrangères: toutes ! Lors d'une jointure, mysql recherchera l'index.
• Les dates et autres datetime: Elles sont souvent un outil de recherche et sont faciles à indexer.
• Les int et autres decimal, numeric, si vous faites des recherches dessus.  Exemple: Il est utile d'indexer le nombre de votes d'un billet si l'on veut afficher les billets classés par vote.
• Les "tags" (champ texte désignant un mot, pouvant servir pour la recherche. Il est pertinent que les tags soient de type char.)
• Les ENUM et SET.

Certains autre ne sont, justement, pas efficace à indexer, et la recherche sur ce type de champ est déconseillée:

• Les varchar, text et autres chars

• Les blobs

Lors d'une recherche, mysql cherchera de lui-même le meilleur index possible. 

Parfois c'est évident, et parfois il se trompera. Vous pouvez lui indiquer quel index utiliser:

SELECT * FROM UTILISATEURS WHERE NOM='ZORG' AND REGION='75' USE INDEX index_nom_region

Dans le cas d'un 'OR', mysql peut aussi avoir du mal a choisir ses indexs, et parfois faire deux requettes avec  un union peut augmenter la rapidité de la requette.

SELECT * FROM UTILISATEURS WHERE REGION='75' AND DATE_CREATION > '01-01-2000';

 Temps: 0.04 secondes.

(SELECT * FROM UTILISATEURS 

WHERE REGION='75' USE INDEX index_region)

UNION

(SELECT * FROM UTILISATEURS
WHERE DATE_CREATION > '01-01-2000' USE INDEX index_dateCreation)

Temps: 0.01 secondes.

Conclusion:

Les index sont la première chose à penser pour améliorer l'optimisation 

d'une base. Il est plus important que tous les autres paramètres réunis.

Toute base en environnement de production, que ce soit en environnement 

b2b ou fort traffic, ne peut pas se passer d'index.

Capacité de réduction du temps de réponse d'une requête grâce aux 

indexs: entre 80% et 98%.