11.1. Introduction

Supposons que nous ayons une table comme celle-ci:

CREATE TABLE test1 (
    id integer,
    content varchar
);

et que l'application utilise beaucoup de requêtes de la forme

SELECT content FROM test1 WHERE id = constant;

Sans préparation supplémentaire, le système devrait lire la table test1 en entier, ligne par ligne, pour trouver toutes les lignes qui correspondent. S'il y a beaucoup de lignes dans test1, et que seulement quelques lignes correspondent à la requête (peut-être même zéro ou une seule), alors, clairement, la méthode n'est pas efficace. Mais si on a indiqué au système de maintenir un index sur la colonne id, alors il peut utiliser une manière beaucoup plus efficace pour trouver les lignes recherchées. Par exemple, il pourrait n'avoir à parcourir que quelques niveaux d'un arbre de recherche.

Une approche similaire est utilisée dans la plupart des livres autres que ceux de fiction: les termes et concepts qui sont fréquemment recherchés par les lecteurs sont listés par ordre alphabétique à la fin du livre. Le lecteur qui recherche un mot particulier peut facilement parcourir l'index, puis aller directement à la page ou aux pages indiquée(s). De la même façon que l'auteur doit anticiper les sujets que les lecteurs risquent de rechercher, il est de la responsabilité du programmeur de prévoir quels index seraient avantageux.

La commande suivante permet de créer un index sur la colonne id dont nous parlons:

CREATE INDEX test1_id_index ON test1 (id);

Le nom test1_id_index peut être choisi librement, mais il est conseillé de choisir un nom qui rappelle le but de l'index.

Pour supprimer l'index, utilisez la commande DROP INDEX. Les index peuvent être ajoutés et enlevés des tables à tout moment.

Une fois l'index créé, aucune intervention supplémentaire n'est nécessaire: Le système met à jour l'index lorsque la table est modifiée, et utilise l'index dans les requêtes lorsqu'il pense que c'est plus efficace qu'une lecture complète de la table. Il faut néanmoins lancer la commande ANALYZE régulièrement pour permettre à l'optimiseur de requêtes de prendre les bonnes décisions. Voyez Chapitre 13 pour comprendre quand et pourquoi l'optimiseur décide d'utiliser ou de ne pas utiliser un index.

Les index peuvent aussi bénéficier aux commandes UPDATE et DELETE qui ont des conditions de recherche. Les index peuvent de plus être utilisés dans les jointures. Ainsi, un index défini sur une colonne qui fait partie d'une condition de jointure peut accélérer significativement les requêtes avec jointures.

Lorsqu'un index est créé, le système doit le maintenir synchronisé avec la table. Cela rend plus lourdes les opérations de manipulation de données. C'est pourquoi les index qui ne sont pas essentiels ou qui ne sont pas utilisés du tout doivent être supprimés. Notez qu'une requête ou une commande de manipulation de données ne peut utiliser qu'un index par table au maximum.