Discussion :

[winzou]

Bonjour !

J'utilise la représentation intervallaire (http://sql.developpez.com/arborescence/) pour stocker des catégories.
J'ai une clé primaire sur l'id bien sur, et un index unique sur (bg, bd) (bornes gauche et droite)

Seulement MySQL n'utilise pas l'index lorsque je fais un where du type : WHERE bg < 4 AND bd > 10. Or toutes les requetes sur cette table sont de cette forme !

Pour l'instant je n'ai pas beaucoup de catégories donc je ne sens rien, mais j'ai peur pour l'avenir, lorsque la table sera plus remplie !

Il y a-t-il d'autres moyens d'indexer une telle table ? Et pourquoi MySQL n'utilise pas l'index avec mon where ?

Merci beaucoup

[Biglo]

Salut,

Peux-tu donner le code de création de la table, un petit jeu d'essai (INSERT) et une requête exacte où les index ne sont pas utilisés ? J'aimerais regarder ça plus en détails.

Sinon, pour en revenir à ta question. Si MySQL n'utilise pas la clé, c'est peut-être qu'il estime que cela lui est moins coûteux de parcourir toute la table plutôt que d'utiliser son arbre d'index. Si tu penses qu'il se trompe, tu peux toute fois tenter de forcer l'utilisation de l'index avec FORCE INDEX.

[winzou]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE `cat` (
  `cat_id` int(11) unsigned NOT NULL,
  `cat_bg` smallint(6) NOT NULL,
  `cat_bd` smallint(6) NOT NULL,
  `cat_nv` tinyint(2) unsigned NOT NULL,
  `cat_name` varchar(100) NOT NULL,
  PRIMARY KEY  (`cat_id`),
  UNIQUE KEY `cat_bg_bd` (`cat_bg`,`cat_bd`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `cat`
-- 
 
INSERT INTO `cat` (`cat_id`, `cat_bg`, `cat_bd`, `cat_nv`, `cat_name`) VALUES
(1, 1, 56, 0, 'Gay Island'),
(2, 2, 9, 1, 'Private'),
(3, 10, 55, 1, 'Public'),
(4, 3, 4, 2, 'Code'),
(5, 5, 6, 2, 'Design & Graphiks'),
(6, 7, 8, 2, 'Tourisme'),
(7, 51, 52, 2, '2819'),
(8, 49, 50, 2, '1994'),
(9, 47, 48, 2, '3033'),
(10, 45, 46, 2, '4813'),
(11, 43, 44, 2, '9321'),
(12, 41, 42, 2, '4560'),
(13, 39, 40, 2, '3784'),
(14, 37, 38, 2, '890'),
(15, 35, 36, 2, '3721'),
(16, 33, 34, 2, '5774'),
(17, 31, 32, 2, '4999'),
(18, 29, 30, 2, '2796'),
(19, 27, 28, 2, '6285'),
(20, 25, 26, 2, '7170'),
(21, 23, 24, 2, '5184'),
(22, 21, 22, 2, '6823'),
(23, 19, 20, 2, '9816'),
(24, 17, 18, 2, '6950'),
(25, 15, 16, 2, '5130'),
(26, 13, 14, 2, '2759'),
(27, 11, 12, 2, '7316');

Et la requete :

Code :

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

1
2
3
SELECT cat_id, cat_bg, cat_bd, cat_nv, cat_name
FROM cat
WHERE cat_bg > 1 AND cat_bd < 56

J'avais oublié en effet que si la table n'était pas assez remplie mysql n'utilisait pas d'index. C'est pour ca que j'ai rajouté toutes les catégories qui ont un nombre comme nom. Mais rien n'y fait, il n'utilise toujours pas l'index malgré les 30 lignes (qui suffisent d'habitude pour qu'il utilise un index).

J'ai essayé en forcant l'index, il l'utilise bien. Mais bon, s'l ne l'utilise pas tout seul c'est qu'il y a une raison, et j'aimerais bien la comprendre (parce qu'il a souvent raison quand même )

Merci

[Biglo]

Salut,

Pour savoir si l'utilisation d'un index apportera éventuellement un gain de performances, les SGBD calculent le rapport entre la sélectivité (le nombre de lignes qui seront retournées) et le nombre de lignes total de la table. En fonction du pourcentage obtenu, les SGBD décident de faire un parcours complet ou avec utilisation des index. Ce pourcentage varie d'un SGBD à un autre, mais il semblerait que certains spécialistes en BdD estiment qu'en dessous de 30%, l'utilisation est index est préférable.

Dans ton cas, avec cat_bg > 1, MySQL pense qu'il serait plus avantageux de ne pas utiliser l'index. En effet, 26 lignes sur 27 vérifient cette condition ! Par contre, si tu mets cat_bg > 40, l'index sera utilisé car seulement 6 lignes seront retournées : il y a 20 lignes qui n'ont pas besoin d'être analysées.

MySQL (et les autres SGBD dignes de ce nom) font dans 95% le bon choix dans l'utilisation ou non d'un index. Mais il est parfois possible qu'ils se trompent et ce comportement peut être modifié avec FORCE INDEX / IGNORE INDEX.

J'ai pu remarquer que MySQL a plutôt tendance à utiliser des index inutiles, mais il ignore très rarement des index utiles. Donc dans ton cas, je pense qu'on peut lui faire confiance

Bien sûr, pour le choix d'utiliser un index ou non, il y a d'autres critères pris en compte par l'optimiseur. Mais c'est sûrement très complexe

[winzou]

Ah ouais bien joué, j'oubliais la selectivité du where !

Par contre le calcul de cette selectivité semble ne prendre en compte que la 1ere colonne de l'index c'est dommage ! En effet, avec un "cat_bg > 40" il utilise bien l'index, mais avec un "cat_bg > 2 AND cat_bd < 4" (qui est encore plus sélectif que le 1er) il ne l'utilise pas...

Ca dépend de comment mes catégories seront organisées par la suite, mais peut-être qu'un force index sera utile pour palier au mauvais calcul de la sélectivité, tu confirmes ?

Merci en tout cas

[Biglo]

Oui avec "cat_bg > 2 AND cat_bd < 4", MySQL n'utilise pas l'index. C'est en fait assez simple à comprendre. Ton index UNIQUE est basé sur les deux colonnes mais l'index ne peut être utilisé avec cat_bd que s'il est utilisé avec cat_bg. Ce qui est normal puisque l'ordre des colonnes de l'index a de l'importance.

Or, ici MySQL voit qu'avec cat_bg > 2, il y a 25 lignes retournées sur un total de 26. Il décide donc, à juste titre, de ne pas utiliser l'index que tu as créé. S'il le faisait, ça serait peut-être utile pour cat_bd < 4, mais pas pour cat_bg > 2.

Donc, si tu veux que les performances soient améliorées pour ce genre de requêtes, il faut aider MySQL. Mais pas avec un FORCE INDEX. Il faut recréer un autre index sur cat_bd. Ou même mieux : un index sur (cat_bd, cat_bg).

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `cat` (
  `cat_id` int(11) unsigned NOT NULL,
  `cat_bg` smallint(6) NOT NULL,
  `cat_bd` smallint(6) NOT NULL,
  `cat_nv` tinyint(2) unsigned NOT NULL,
  `cat_name` varchar(100) NOT NULL,
  PRIMARY KEY  (`cat_id`),
  UNIQUE KEY `cat_bg_bd` (`cat_bg`,`cat_bd`),
  INDEX `cat_bd_bg` (`cat_bd`, `cat_bg`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Code :

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

1
2
3
4
EXPLAIN SELECT cat_id, cat_bg, cat_bd, cat_nv, cat_name FROM cat WHERE cat_bg > 2 AND cat_bd < 4;
 
possible_keys: cat_bg_bd,cat_bd_bg
          key: cat_bd_bg

Voilà, j'espère que mes explications t'ont été utiles, et qu'elles ne sont pas trop incorrectes

[winzou]

Or, ici MySQL voit qu'avec cat_bg > 2, il y a 25 lignes retournées sur un total de 26. Il décide donc, à juste titre, de ne pas utiliser l'index que tu as créé.

Ben c'est ce que je lui reproche en fait !
De s'arreter à cat_bg > 2 dans son raisonnement. Comme il y a une condition sur cat_bd juste après, il devrait regarder ce que ca donne en le prenant dans les comptes. Après tout c'est bien l'avantage d'un index multicolonne non ? Un index sur deux colonnes n'est pas deux index sur une colonne.

Enfin j'ai rajouté un 2e index, et il l'utilise bien. Je regarderai ce que ca donne avec plus de données et plus de selectivité

[Biglo]

Ben c'est ce que je lui reproche en fait !
De s'arreter à cat_bg > 2 dans son raisonnement. Comme il y a une condition sur cat_bd juste après, il devrait regarder ce que ca donne en le prenant dans les comptes. Après tout c'est bien l'avantage d'un index multicolonne non ? Un index sur deux colonnes n'est pas deux index sur une colonne

Techniquement, MySQL n'a pas le choix. Il ne peut pas utiliser l'index juste pour cat_bd. S'il l'utilise, il doit d'abord prendre en compte cat_bg avant de regarder pour cat_bd.

Il y a un arbre (plus exactement : B-Arbre ou B-tree) par index. Si tu crées un index sur chaque colonne, tu as deux arbres complètement indépendants. Dans ce cas, chacune de tes conditions avec cat_bg et cat_bd est traitée séparemment.

Par contre, si tu crées un index multi-colonnes, tu n'as qu'un seul arbre (certes un peu différent des précédents). Mais cet arbre est construit en fonction de l'ordre des colonnes spécifées. Dans ton cas avec la clé UNIQUE, on peut se dire que MySQL possède un arbre pour cat_bg. A chaque feuille de l'arbre (= pour chaque valeur possible de cat_bg dans la table), MySQL associe un autre arbre, mais qui concerne cette fois les valeurs de cat_bd. Bref, quand une condition concerne cat_bg, MySQL peut parcourir l'arbre sans problème, comme s'il y avait un index normal sur cette colonne. Par contre, si la condition ne concerne que cat_bd, MySQL est quand même obligé de parcourir tout ce qui concerne cat_bg !

Un petit exemple :

Code :

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

1
2
3
SELECT cat_id, cat_bg, cat_bd, cat_nv, cat_name
FROM cat
WHERE cat_bg > 40 AND cat_bd < 56

1) MySQL regarde la partie qui concerne cat_bg dans l'arbre de l'index. Après quelques opérations de parcours, il remarque qu'il existe 6 valeurs qui sont > 40. Le pourcentage de sélectivité est bon, MySQL décide d'utiliser l'index.

2) Pour chacune de ces 6 valeurs de cat_bg, il parcourt la partie qui concerne cat_bd. Il arrive aux feuilles, et obtient les couples qui vérifient la condition entière.

3) A partir des feuilles, il obtient un "pointeur" qui lui permet d'accéder rapidement aux enregistrements dans la table, afin de récupérer les autres données (SELECT ...)

Maintenant si on prend cette requête :

Code :

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

1
2
3
SELECT cat_id, cat_bg, cat_bd, cat_nv, cat_name
FROM cat
WHERE cat_bg > 2 AND cat_bd < 4

Les opérations restent les mêmes. Sauf qu'en 1), MySQL voit lors de son parcours qu'il y a 25 valeurs qui satisfassent la condition "> 1". Et il décide donc d'oublier l'index. S'il ne le faisait pas, il devrait parcourir tout l'arbre pour obtenir ces 25 valeurs. Et pour chacune de ces valeurs, regarder la partie qui concerne cat_bd.

Toi, tu te dis "mais il est bête ce MySQL, s'il regardait la condition cat_bd > 4, il aurait beaucoup moins de travail à faire". Mais le problème, c'est qu'il ne peut pas, avec l'index que tu lui as donné, regarder cat_bd avant cat_bg.

Avec un index sur cat_bg et un autre sur cat_bd, ce problème ne se poserait pas. Mais comme chaque index serait analysé indépendamment, certaines requêtes ne seraient pas autant optimisées qu'avec des index multi-colonnes. C'est pour cette raison que j'estime que deux index multi-colonnes constituent la meilleure solution, en terme de performances bien sûr, car en espace disque, ce n'est pas le cas.

[winzou]

Très bonne explication, je prend merci ^^

[Biglo]

N'oubliez pas le guide en sortant