Discussion :

[perenorel]

Je me pose des questions existentielles sur l'utilité d'indexer certains types de colonnes , même après avoir lu cet article :
http://sqlpro.developpez.com/cours/quoi-indexer/

Je vous les donne en vrac et j'attends vos points de vue...

1) est-il utile de mettre un index sur un champ varchar si l'on sait qu'il n'y aura aucun doublon dans la colonne concernée ?

2) est-il utile de mettre un index sur des champs de type ENUM ou SET ?

3) est-il utile de mettre un index sur un champ TINYINT(1) qui ne prend que 1 ou 0 comme valeur ?

4) est-il utile de mettre un index sur un champ de types TIME ou DATETIME sachant qu'il est impossible d'avoir deux valeurs identiques (même date, même heure à la seconde près) pour cette colonne ?

5) est-il utile de mettre un index sur un champ numérique représentant une FK dont on sait qu'elle ne prendra que quelques valeurs distinctes (moins de 5 par ex) ?

Je me place dans un contexte MySQL 5 et avec des index classiques, bien entendu.

Merci par avance pour vos réponses.

AV

[SQLpro]

Visiblement vous avez mal lu mon article et confondez ce qu'est un index et ce qu'est une contrainte d'unicité. L'un n'ayant AUCUN rapport avec l'autre !

Un index est une structure de données spécialement organisée pour accélérer certaines recherches.


Une contrainte de clef primaire ou une contrainte d'unicité garantie qu'aucune valeur ne sera en double. Il s'agit là de deux contraintes différente. L'une assurant la clef de la relation, l'autre l'unicité des données (mais peut être non renseignée).

La plupart des SGBDR placent des index sous ce types de contraintes. C'est le seul lien (incertain) qui existe entre les deux.
On peut dire que l'index est une problématique PHYSIQUE (ais-je besoin de performances ?) et l'autre une problématique logique (dois-je assurer l'unicité ?).

A +

[Antoun]

1) oui, s'il doit être utilisé en recherche ou en jointure

2) ça dépend combien il y a de valeurs possibles...

3) imaginons une colonne Sexe avec 1 = masculin et 0 = féminin ; sur une population classique avec environ 50/50 de sex ratio, la sélectivité de ton index sera d'environ 50%, ce qui est beaucoup trop élevé (en réglage standard, MySQL n'essaie même pas les index de sélectivité supérieure à 30%). Si on prend maintenant une population très déséquilibrée, par exemple l'Armée avec un sex ratio de 95/05 (j'invente), l'index permettra de trouver plus rapidement les femmes (et sera ignoré lors desrecherches sur les hommes).

4) oui, cf 1)

5) MySQL crée de toute façon un index des deux côtés de la FK...

[perenorel]

Visiblement vous avez mal lu mon article et confondez ce qu'est un index et ce qu'est une contrainte d'unicité. L'un n'ayant AUCUN rapport avec l'autre !

Absolument pas... j'ai du mal m'exprimer dans mes questions.
Je comprends très bien la différence entre les deux et mes questions ne concernent pas les contraintes d'unicité, même si j'ai parlé de doublons. Je me place bien du côté performance et utilité des index.

@antoun :
1) si j'ai une requête qui fait WHERE nom = "toto" et que je suis certain qu'il n'y aura jamais deux fois "toto" dans mon champ "nom", l'index peut tout de même être utile ? Dans ce cas, ne serait-ce pas plutôt un index UNIQUE ?

2) A partir de combien de valeurs cela devient utile ?

3) Ok, j'avais lu cet exemple, mais parfois il est difficile de connaître la proportion à l'avance... dans ce cas, est-ce que la création de l'index à chaque coup, sans se soucier de cette proportion pourrait être pénalisante ?

4) Ok, j'ai lu que cela pouvait être utile si on fait une requête between entre deux time ou date time, mais dans le cas d'un WHERE simple, est-ce vraiment intéressant ?

5) Je me suis mal exprimé, je me plaçais dans un contexte MyIsam en fait, sans véritable FK avec contrainte d'unicité. Dans ce cas l'index est-il quand même créé automatiquement ou bien vaut-il mieux l'ajouter de toutes façons, même si elle ne prend que quelques valeurs ?

[Antoun]

@antoun :
1) si j'ai une requête qui fait WHERE nom = "toto" et que je suis certain qu'il n'y aura jamais deux fois "toto" dans mon champ "nom", l'index peut tout de même être utile ?

La réponse est la même que pour ta question 3). Ce qui fait l'efficacité d'un index, c'est d'abord sa sélectivité. Si tu as un seul "toto" parmi 3 personnes, ça veut dire une sélectivité de 33%, donc pas terrible (je fais abstraction du fait que pour une table si petite, il n'y a de toute façon pas besoin d'index).

Si tu as un seul "toto" parmi 10 000 personnes, ton index a une sélectivité de 1/10 000 ; il évitera au SGBD de parcourir les 10 000 lignes de ta table des personnes.

Dans ce cas, ne serait-ce pas plutôt un index UNIQUE ?

Oui, bien sûr.

2) A partir de combien de valeurs cela devient utile ?

c'est toujours une question de sélectivité... L'optimiseur de MySQL n'utilise de toute façon pas les index au-dessus de 30% de sélectivité. Donc s'il y a deux ou trois valeurs bien réparties, l'index sera inutile. S'il y a 50% d'hommes, 49,9% de femmes, et 0,1% d'hermaphrodites, l'index sera très utile pour trouver les hermaphrodites...

Pour le SET, c'est à peu près la même chose, sauf que ce qui va être indexé c'est la combinaison des éléments du SET. Donc avec un SET('a', 'b', 'c'), on a 2^3 = 8 possibilités, soit une sélectivité de 12,5 % si les huit combinaisons sont équiprobables.

3) Ok, j'avais lu cet exemple, mais parfois il est difficile de connaître la proportion à l'avance... dans ce cas, est-ce que la création de l'index à chaque coup, sans se soucier de cette proportion pourrait être pénalisante ?

"pénalisant" ne veut rien dire si on ne précise pas ce qu'on veut optimiser... un index est toujours pénalisant pour l'espace disque et pour les modifications de données. Pour un SELECT, on s'en remet normalement à l'optimiseur qui décide de passer par l'index ou non...

Avec un optimiseur parfait, l'index ne serait donc jamais pénalisant pour le SELECT, puisqu'il serait ignoré dans tous les cas où il n'est pas rentable. Du coup, j'aurais tendance à te répondre oui, on crée l'index à chaque fois (sauf si on a des problèmes d'espace disque, si la performance des mises à jour est critique, si l'index est manifestement inutile), non pas parce que l'optimiseur est parfait, mais sans doute parce qu'il connaît son boulot mieux que moi.

4) Ok, j'ai lu que cela pouvait être utile si on fait une requête between entre deux time ou date time, mais dans le cas d'un WHERE simple, est-ce vraiment intéressant ?

Même réponse. L'index est d'autant plus intéressant qu'on a une bonne sélectivité, et la meilleure sélectivité est atteinte quand les valeurs sont uniques.

Ceci dit, je ne vois pas en quoi une granularité à la seconde te garantirais l'unicité... c'est une dangereuse ignorance de la loi de Murphy.

5) Je me suis mal exprimé, je me plaçais dans un contexte MyIsam en fait, sans véritable FK avec contrainte d'unicité. Dans ce cas l'index est-il quand même créé automatiquement ou bien vaut-il mieux l'ajouter de toutes façons, même si elle ne prend que quelques valeurs ?

Non, sur MyISAM aucun index n'est créé... Sur les "quelques valeurs", voire mes réponses sur la sélectivité...

[SQLpro]

5) MySQL crée de toute façon un index des deux côtés de la FK...

Là tu t'avance un peu !!!!!!!!!!

"
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.
"

Lit bien : referencing table et non referenced table

personnellement je n'ai jamais vu un SGBDR créer des index sur les clefs étrangères !!!!

A +

[Antoun]

Lit bien : referencing table et non referenced table

ach ! je m'étais stupidement laissé abuser par la phrase précédente ("InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast") et je n'avais pas vu le changement d'objet...

personnellement je n'ai jamais vu un SGBDR créer des index sur les clefs étrangères !!!!

Certes, mais tu seras d'accord avec moi pour dire que MySQL n'est pas un parangon d'orthodoxie