Comprendre les INDEX

[Just]

Bonjour,

Je souhaiterais être sûr de bien comprendre les INDEX et donc de les utiliser correctement.

Après avoir parcouru quelques articles/documentations je ne trouve pas de réelle réponse à cette question : quelle est la différence entre créer un index pour trois champs (donc regroupé dans le même index) et en créer un pour chacun ?

Merci d'avance pour vos lumières !

[qi130]

Les index existent pour améliorer les temps d'exécution des requêtes.

Au delà des index nécessaires "par construction" (i.e PK, FK, etc.), et dont la déclaration est parfois prise en charge par le SGBD lors de la définition de la PK par exemple; les index ne sont (ne devraient être) pertinents qu'à la lumière des requêtes "applicatives" qu'ils sont sensés servir.

A de nombreuses reprises sur ce forum et ailleurs, on rabâche cette notion de pertinence afin de faire prendre conscience qu'indexer une colonne qui n'entre pas dans un where/join/order/group ne sert à rien d'autre que ralentir le SGBD.

Dans ton cas, l'index sur 3 colonnes est pertinent si ces 3 colonnes, en dehors du cas où elles constituent une clé, sont utilisées en même temps et selon le même critère de constitution de l'index.
A la marge, le même index pourra être utilisé pour une requête s'appuyant sur sa 1ère colonne (ou les 2 premières).

Ainsi, si l'index est crée avec le critère ASC pour ses 3 colonnes (C1, C2, C3), une requête spécifiant ORDER BY C3 DESC ne l'utilisera pas (enfin normalement )
Il sera alors pertinent de créer un index spécifique sur C3 DESC.

[Just]

Merci qi130, voilà qui me renseigne déjà bien.

Peux tu préciser "et selon le même critère de constitution de l'index" s'il te plait ?
Un exemple ?

[CinePhil]

Si l'index est défini sur les colonnes (C1, C2, C3) et que le WHERE ou la condition de jointure de la requête porte sur C2 et/ou C3 mais pas C1, cet index ne sera pas utilisé.

Il suffit de se faire un exemple pour comprendre pourquoi :
C1, C2, C3
1, 1, 1
1, 2, 12
1, 54, 6
1, 54, 25
2, 3, 1
2, 5, 8
2, 6, 3
3, 5, 7
3, 6, 2

La colonne C1 est bien dans l'ordre mais la colonne C2 n'est dans l'ordre que par rapport à C1 et la colonne C3 n'est dans l'ordre que par rapport au couple (C1, C2).
Un index sur le triplet (C1, C2, C3) n'est pas équivalent à trois index sur chacune des colonnes.

L'index sur le triplet n'est pertinent que :
- si le triplet constitue la clé primaire de la table ;
- si les jointures et/ou les restrictions (WHERE) des requêtes portent sur l'ensemble des 3 colonnes.

Pour savoir quoi indexer, voir l'article de SQLPro.