MySQL, indexes et rapidité

[Xenos]

Salut à tous

Suite à quelques essais sur une BDD MySQL (MyISAM) contenant des champs de type "BIT(1)", je me suis aperçu que l'ajout d'un index peut ralentir une requête SELECT.
En cherchant, l'explication se trouve dans cet article sur la cardinalité des index (en).

Schématiquement, j'en ai compris qu'un index accélère une requête de sélection si la cardinalité de cet index est élevée. La cardinalité représente le nombre de valeurs différentes pour cet index. Ainsi, une clef primaire (index unique) a une cardinalité égale au nombre de lignes de la table, alors qu'un index sur un champ BIT(1) aura une cardinalité de 2 (car seules les valeurs 0 et 1 sont présentent dans ce champs).

Or, plus la cardinalité est élevée, plus le SGDB fait des "élagages" précis et rapides. Quand la cardinalité est faible (2 par exemple), le temps de charger l'index dépasse le temps que cet index fait gagner, et la requête perd en performances.

Ainsi, une requête SELECT dont la clause WHERE porte sur un champ "BIT(1)" est ralentie si un index est ajouté sur ce champ.
Toutefois, une petite exception pour le cas où plus de 99% des valeurs de ce champ sont "0" et que l'on ne souhaite que les valeurs "1" (ou inversement): l'ajout d'un index peut apporter un léger gain. Mais comme cette considération dépend du contenu du champ, je préfère la prendre avec des pincettes, voire l'ignorer.


En conclusion de l'article, j'en déduis que les index ne doivent être utilisés que sur des champs dont la cardinalité (nombre de valeurs prises par ce(s) champ(s)) est élevée.
Si vous avez d'autres ressources sur la bonne utilisation des index MySQL (essayons de ne pas mélanger avec les SQL, PostGre et Mongo dans le même topic), je vous propose de les rajouter en réponse; idem pour vos possibles remarques

[Sephi-Chan]

Ce n'est pas inutile de le savoir. D'après mes observations, les cas les plus répandus d'utilisation d'index nous permettent de sortir de la situation où l'index ralentit la requête :

• L'index sur une clé primaire numérique auto-incrémentée, la cardinalité augmente donc avec chaque nouvelle ligne. L'index reste donc intéressant.
  
• L'index sur une clé étrangère, là aussi on pointe vers un autre index. Donc à moins d'avoir un très faible nombre de valeurs liées possible, ça reste intéressant.
  
• L'index sur une date, là aussi on devrait rarement avoir des dates similaires.
  

Bien sûr, mes observations dépendent totalement du type d'application. Mais je pense sincèrement que ce phénomène est négligeable.
D'autant que "plus lent" ne signifie pas forcément "beaucoup plus lent".

[Xenos]

Sur MySQL, l'auto-incrément requiert un index (primaire, unique ou simple), donc même si l'index ralentissait, on n'aurait pas le choix (#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key ).

Pour les dates, je suis d'accord, en revanche pour les clefs étrangères, je le suis moins: leur cardinalité peut être faible. Par exemple, un index sur l'identifiant d'une table d'énumération avec 4 ou 5 valeurs n'apporte rien. L'index sur la colonne qui y fait référence n'apportera rien non plus s'il y a là encore 4 ou 5 valeurs différentes (et s'il n'y en a que 2, l'index aura même tendance à ralentir).
Mais je recommanderai quand même de mettre un Unique/Primary index pour souligner l'unicité des identifiants dans la table d'énumération, quitte à ignorer l'index dans les requêtes (en).
Sinon, oui, si la clef étrangère est du type "id d'un autre objet", avec là une grande cardinalité et un grand nombre d'objets vers lesquels faire pointer la clef, l'index devient intéressant.


"Plus lent", dans mon cas, signifie "5% de temps en plus (cas utilisé: sélectionner les lignes ayant BIT(1)=0 dans une table de 500k lignes).
Dans le cas de l'article, on parle quand même d'un facteur x100 quand les indexes sont mal ajoutés O.o Là, ce n'est plus négligeable...

[Sephi-Chan]

Oui, j'avais oublié cette contrainte dans MySQL. Ça règle le premier point.

C'est vrai qu'en SQL, les ordres de grandeurs sont parfois énorme lors des optimisations. C'est amusant à constater.