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