Discussion :

[dorian53]

Bonjour,

Lorsque que je crée un "index" sur une colonne qui a la possibilité d'être NULL, j'ai remarqué qu'il y avait autant d'index que d'enregistrement (et non pas autant que de valeurs différente).

Est-ce normal ?
Est-ce une erreur de conception ?

Merci

[qi130]

Bonjour,

Lorsque que je crée un "index" sur une colonne qui a la possibilité d'être NULL, j'ai remarqué qu'il y avait autant d'index que d'enregistrement (et non pas autant que de valeurs différente).

Est-ce normal ?

Oui, tout à fait, et c'est très bien ainsi !
En effet, lors d'une requète où on rechercherait les "valeurs" NULL (abus de langage car NULL n'est pas une valeur), MySQL pourra utiliser cet index afin de localiser rapidement les lignes correspondantes.

Il faut considérer un index comme une liste d'adressage alternatif au classement naturel d'une table (basé sur sa clé), ce n'est donc pas une sorte de filtre basé sur DISTINCT.

[dorian53]

Salut,

Merci pour ta réponse, mais mon doute existe toujours.

Si j'ai les enregistrements suivants avec un index sur COLONNE_B (qui peut être NULL) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
COLONNE_A                  COLONNE_B
   valeur1                        NULL
   valeur2                        NULL
   valeur3                        1
   valeur4                        1
   valeur5                        2

Dans mon esprit, j'ai 3 index et il y en a réellement 3 si COLONNE_B ne peut pas être mise à NULL ("autant que de valeurs différentes").

Alors que lorsque NULL est autorisé comme dans cet exemple, j'obtiens 5 index (et non 3).


Est-ce normal ?
Ce n'a t-il rien à voir avec NULL ?

[witch]

Personne ?

salut,
je pense que gi130 était assez clair, et je comprends bien ce qu'il a dit..
ben je reformule :p
voilà quand t'as mis un index sur le champs en question, ça se creer autant d'index que des valeurs, bien c'est evident on parle bien de valeur, et pas de null.
gi130 a dit:
"il faut considérer un index comme une liste d'adressage alternatif au classement naturel d'une table (basé sur sa clé), ce n'est donc pas une sorte de filtre basé sur DISTINCT"
ben voilà, le fait qu'il ne trouve pas de valeur pour le champs indexé, ça perturbe, et ça mene à creer d'index à chaque enregistrement dont le champs n'a pas de valeur.
j'èspere que c'est clair là.
bonne courage(à moi aussi)
je cherchais justement sur quelque chose concernant les indexs ...enfin j'ajoute un nouveau thread..
++

[dorian53]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
COLONNE_A                  COLONNE_B
   valeur1                        NULL
   valeur2                        NULL
   valeur3                        1
   valeur4                        1
   valeur5                        2

ben voilà, le fait qu'il ne trouve pas de valeur pour le champs indexé, ça perturbe, et ça mene à creer d'index à chaque enregistrement dont le champs n'a pas de valeur.

Même plus que ça puisque suivant ta logique.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
1er  index NULL
2e   index NULL
3e   index 1
4e   index 2

Alors que dans cet exemple il y a 5 index et non 4.

Donc l'index n'a aucun intérêt ?

[qi130]

Un des avantages des index est de permettre l'optimisation des requètes impliquant la ou les colonnes indexée(s).

Encore une fois, l'index est un moyen alternatif d'accéder aux lignes de la table.

Reprenons ta structure de table. Au lieu des 5 lignes, imaginons qu'il y en ait 5 millions, mais seulement 2 NULL en colonne B sur des lignes aléatoires

1er cas: pas d'index sur COLONNE_B
- pour trouver le nb de NULL en colonne B, le moteur doit scanner l'ensemble des 5 millions de lignes pour déterminer si le contenu de COLONNE_B est NULL ou pas
- pour déterminer les lignes ayant NULL en colonne B, le moteur doit refaire le même travail

2ème cas: index sur COLONNE_B
- pour trouver le nb de NULL: 2 lectures sur l'index suffisent
- pour déterminer les lignes ayant NULL en colonne B:
* 1ère lecture de l'index qui donne l'adresse de la ligne correspondante, cette ligne est lue pour être remonter vers le demandeur
* 2ème lecture de l'index, etc....
Total = 4 lectures


Si un index fonctionnait comme tu le pensais (1 occurrence par valeur différente dans la colonne B), il ne serait jamais utilisable car il serait incapable de fournir le nb de NULL en colonneB (juste capable de dire "il y a au moins 1 NULL), et tout aussi incapable d'en donner la localisation dans la table.

Je te rappelle la notion d'adressage alternatif, ce qui implique la capacité à fournir l'adresse (=localisation): si l'index ne contient qu'une entrée par valeur de colonneB, laquelle on inscrit ? la première ? la dernière ? ça dépend ?

[dorian53]

Très bien merci pour toutes ces explications.

Alors je dois faire erreur dans mon interprétation...
Que signifie le nombre de cardinalités à côté des index (sous phpmyadmin) ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Nom de la clé  	Type  	         Cardinalité
PRIMARY  	PRIMARY  	 2047
i_cat  	        INDEX  	         3

[witch]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
COLONNE_A                  COLONNE_B
   valeur1                        NULL
   valeur2                        NULL
   valeur3                        1
   valeur4                        1
   valeur5                        2

Même plus que ça puisque suivant ta logique.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
1er  index NULL
2e   index NULL
3e   index 1
4e   index 2

Alors que dans cet exemple il y a 5 index et non 4.

Donc l'index n'a aucun intérêt ?

salut,
en fait de quel type d'index parles tu pour qu'on soit d'accord
parce que là j'ai bien utilisé un index de type unique, et j'ai testé aussi le type Btree(le type par defaut), et il ya aussi le type fulltext et spatiale pour les tables de type MyIsAM.. ça donne le même résultat cad pour chaque ligne il ya un index, j'ai pas fais très attention au debut..mais bon là c'est clair,

gi130 a dit:
* 1ère lecture de l'index qui donne l'adresse de la ligne correspondante, cette ligne est lue pour être remonter vers le demandeur
* 2ème lecture de l'index, etc....
Total = 4 lectures

mais pour qu'on soit d'accord, bon je comprends le mecanisme okie, mais on parle du nombre d'index qui se creer au niveau du champs indexé c'est bien ça je pense.....donc pour deux valeurs meme pareilles il faut creer deux index, un pour chaque ligne.
show index from tb_statement
envoie cardinality=e_nombre_de_ligne
ce que je cherchais c'était, est ce que je peux acceder à la valeur d'index qui a été crée, je dis bien la valeur de l'index crée pas la valeur du champs qui a été indexé?

[dorian53]

Je parle d'index simple en bTree (pas unique, ni primary).

Et là même avec vos explications je ne trouve pas de logique par rapport à ce que je vois dans mes cardinalités.

[qi130]

Très bien merci pour toutes ces explications.

Alors je dois faire erreur dans mon interprétation...
Que signifie le nombre de cardinalités à côté des index (sous phpmyadmin) ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Nom de la clé  	Type  	         Cardinalité
PRIMARY  	PRIMARY  	 2047
i_cat  	        INDEX  	         3

Je n'utilise pas ce soft donc, je ne sais pas ce que cela représente...
2047 = nb de lignes ?
pour le 3 ??? la profondeur de l'arbre ?

[dorian53]

2047 = nombre d'enregistrements donc de clés primaires, OK pas de soucis.

D'après moi,
3 = nombre d'index de valeurs différentes pour la colonne "catégorie".
Ca correspond bien c'est une clé étrangère qui prend 1, 2 ou 3.
Mais il suffirait que j'autorise NULL dans cette colonne pour que l'index passe à 2047.

On revient toujours à la même question avec cet exemple
Pourquoi compte t-il différemment selon les cas.

[witch]

Je parle d'index simple en bTree (pas unique, ni primary).

Et là même avec vos explications je ne trouve pas de logique par rapport à ce que je vois dans mes cardinalités.

Salut,
ben moi je ne vois pas ou ça donne autant d'index que de valeurs differentes, j'ai bien testé sur un ptit exemple et ça m'a donné avec ou sans valeur null le même nombre de cardinalité qui est egale au nombre de valeur existantes sur la colonne indexé, qu'elle soit ou pas differentes, qu'elle aie ou pas des valeurs null, cad que le nombre de cardinalité egale au nombre de clé de la table.

notice: je n'expliquais pas...je cherchais simplement à donner mon avis, pour qu'on me corrige si j'ai tort.
++

[dorian53]

Personne ? Il y a pourtant un grand nombre de personne à utiliser MySQL et phpmyadmin.

[berceker united]

C'est pas lié à PhpMyadmin car lui n'affiche ce que Mysql dit.
En fait, une valeur null est simplement indexé. Donc si tu autorises les valeurs null, il va scanner la table pour indexer chaque ligne du champ.

[dorian53]

Salut,

"Chaque ligne du champ" ?

Ca m'embête de ne toujours pas trouver réponse à la question.

[Sivrît]

C'est certainement une valeur renvoyée par un "SHOW INDEXES" :

Cardinality
Le nombre de valeurs uniques dans l'index. C'est une valeur qui est mise à jour avec la commande ANALYZE TABLE ou myisamchk -a. Cardinality est compté en se basant sur des statistiques entières : il n'est pas toujours exacte pour les petites tables.

La traduction est approximative, je conseille d'aller voir la version anglaise. Bref, c'est une approximation du nombre de valeurs différentes indexées que MySQL tient à jour autant que faire se peut. Elle lui sert à jauger la selectivité des différents indexes afin de mettre au point les plans d'éxécution des requêtes.