Si vous avez travaillé avec une base de données bien modélisée, vous avez nécessairement travaillé avec des index. Peut-être pas avec tous les types d'index mais au moins avec les index primaires.
Voici un survol des différents types d'index que vous pourriez avoir à utiliser.
L'index le plus fréquemment rencontré est l'index primaire. Sous MySQL, son nom est toujours « PRIMARY ». Il y en a un et un seul par table. Son rôle consiste à identifier chaque enregistrement de façon unique.
On appliquera un index primaire sur la clé primaire de la table (son identifiant).
La valeur de la clé primaire sera utilisée dans la clé étrangère lorsque deux tables sont liées par une contrainte d'intégrité référentielle. C'est pour cette raison qu'il est préférable de prévoir une clé primaire dont la valeur n'aura aucun autre rôle que d'identifier les enregistrement de façon unique et de faire le lien entre deux tables. En effet, si on utilisait un champ avec une valeur textuelle et qu'il fallait, pour une raison ou une autre, changer la valeur de ce champ, il faudrait modifier toutes les tables qui utilisent cette valeur dans une clé étrangère...
Pour cette raison, on appliquera généralement un index primaire sur un champ numérique auto-incrémenté.
L'index unique permet lui aussi d'identifier les enregistrements de façon unique. Contrairement à l'index primaire, il peut être appliqué sur un champ acceptant les valeurs nulles.
L'index unique offre une seconde porte d'entrée pour retrouver une information dans la table. Par exemple, dans une table d'usagers, en plus de la clé primaire qui est un champ numérique auto-incrémenté, on pourra avoir un index unique sur le champ permettant d'enregistrer le nom d'usager utilisé pour l'authentification. Ceci assurera qu'il ne soit pas possible de créer deux usagers avec le même nom d'usager.
Une même table peut avoir plusieurs index uniques.
Un index simple n'effectue aucun contrôle sur les données entrées dans la table. Les données d'un champ indexé peuvent être dupliquées, nulles, etc.
Le rôle de l'index consiste à améliorer les performances des requêtes utilisant une clause WHERE, une clause ORDER BY ou des fonctions d'agrégation comme MIN() et MAX().
Les différences de performances seront négligeables lorsque la table comporte peu d'enregistrements mais deviendront importantes lorsque la quantité de données est considérable.
On devrait toujours créer un index sur les clés étrangères afin d'améliorer les performances impliquant deux tables liées. Dès que vous ajoutez une contrainte d'intégrité référentielle, cet index est créé pour vous. Mais si aucune contrainte n'est créée (ex : si vous travaillez avec une table MyISAM), MySQL ne crée pas automatiquement les index sur les clés étrangères.
Notez que dans phpMyAdmin, un index sur la clé étrangère doit avoir été créé AVANT de pouvoir ajouter une contrainte à l'aide du concepteur.
Lorsqu'une table comporte des champs CHAR, VARCHAR ou TEXT, il est possible d'ajouter un index FULLTEXT pour optimiser les recherches de texte dans ces colonnes. Ce type d'index est supporté par les tables utilisant le moteur MyISAM et, depuis MySQL 5.6, par celles utilisant le moteur InnoDB.
« 8.3 Optimization and Indexes ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
« 12.9 Full-Text Search Functions ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
▼Publicité
