Discussion :

[Zachatim]

Bonjour,

J'enregistre des articles dans une table, chaque article peut avoir 4 catégories différentes.

Mes colonnes sont composées comme ceci :
id cat_1 cat_2 cat_3 cat_4

J'ai d'autres colonnes mais pour des raisons de simplicité je n'en parle pas.

J'ai un index multiple nommé cat_1 sur les colonnes cat_1 cat_2 cat_3 cat_4

Je souhaites selectionner tout les articles qui ont le mot clef informatique dans une des catégories.
Lorsque j'exécute la commande suivante :

Code :

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

explain select id from articles where cat_1='informatique' or cat_2='informatique' or cat_3='informatique' or cat_4='informatique'

mysql m'indique :

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | articles | ALL | cat_1 | NULL | NULL | NULL | 2669 | Using where |
+---+---+---+---+---+---+---+---+---+---+

Je pensais que l'utilisation d'un index multiple sur les 4 catégories aurait permis de ne pas lire les 2669 lignes de la tables.

Evidémment si je fais :

Code :

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

explain select id from articles where cat_1='informatique'

Mysql indique lire 1 seule ligne et utilise l'index cat_1 cat_2 cat_3 cat_4


Quelqu'un peut me donner une piste pour que mysql lise moins de lignes sur la 1ère requête ?
Je ne comprend pas mon erreur

[escartefigue]

Bonjour,

Les explications seraient plus simples si vous communiquiez le DDL de vos tables et index.
A priori, votre index porterait sur CAT1+CAT2+CAT3+CAT4
Pour que votre requete puisse éventuellement utiliser un index, il faudrait que vous ayez un index sur CAT1, un autre sur CAT2, un autre sur CAT3 et un autre sur CAT4
Je dis éventuellement, car la présence d'un index ne suiffit pas, il faut aussi que le nombre de valeurs distinctes soit suffisant pour que l'usage de l'index soit pertinent
Par exemple un index sur le code sexe qui ne peut prendre que 2 voire 3 valeurs, n'a aucun intérêt

Attention (1) : vous utilisez un prédicat OR, OR ne peut être sargable que si toutes les composantes le sont (dans votre cas il faut donc les 4 index, un seul manquant et votre requete n'est pas sargable)
SArgAble est un acronyme pour "Search ARGument ABLE" c'est à dire que la requête peut utiliser un ou plusieurs accès indexés pour satisfaire votre filtrage ou votre jointure plutôt que de parcourir séquentiellement la table ou l'index.
Attention (2) : certains auteurs restreignent le sens de SARGABLE à l'accès unique via un index. Dans cette acception, votre requete sera quoiqu'il arrive non SArgAble

Quelques explications sur le sens fonctionnel des 4 colonnes CAT1 à CAT4 seraient les bienvenues, il n'est pas impossible que la modélisation soit aussi en cause, ou alors c'est un hasard d'avoir 4 colonnes avec le même contenu.

[al1_24]

Par ailleurs, si votre modèle avait été mieux réfléchi, vous auriez une table cat_article(id_article, num_cat, lib_cat), ce qui aurait simpifié la requête en :

Code :

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

select id from cat_article where lib_cat = 'informatique'

[escartefigue]

Quelques explications sur le sens fonctionnel des 4 colonnes CAT1 à CAT4 seraient les bienvenues, il n'est pas impossible que la modélisation soit aussi en cause, ou alors c'est un hasard d'avoir 4 colonnes avec le même contenu.

Par ailleurs, si votre modèle avait été mieux réfléchi, vous auriez une table cat_article(id_article, num_cat, lib_cat), ce qui aurait simpifié la requête en :

Code :

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

select id from cat_article where lib_cat = 'informatique'

C'est ce que je soupçonne dans mon extrait ci-dessus, très probable erreur de modélisation, à confirmer par l'interessé

[Artemus24]

Salut Zachatim.

Il y a une double erreur dans ce que vous faites :

1) votre table est mal modélisée. Il vaudrait mieux fusionner vos quatre colonnes catégories en une seule.
Reprenez la solution exposé par Alain du 24 (al1_24).

2) votre select est très mal adapté à l'extraction de ce que vous recherchez.
Il faut d'une part créer autant d'index qu'il y a de colonnes catégories.
Et d'autre part, faire un union comme dans l'exemple ci-après.
J'ai fait un explain et l'on remarque que chaque select utilise l'index qui lui est approprié.

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
create table `test` (
  `id`   integer unsigned auto_increment NOT NULL PRIMARY KEY,
  `cat1` char(10)                        NOT NULL,
  `cat2` char(10)                        NOT NULL,
  `cat3` char(10)                        NOT NULL,
  `cat4` char(10)                        NOT NULL,
  index `idx_cat1` (`cat1`),
  index `idx_cat2` (`cat2`),
  index `idx_cat3` (`cat3`),
  index `idx_cat4` (`cat4`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`cat1`,`cat2`,`cat3`,`cat4`) values
  ('rouge',  'vert',   'jaune',  'bleu'),
  ('bleu',   'marron', 'noir',   'gris'),
  ('marron', 'gris',   'bleu',   'rose'),
  ('rose',   'noir',   'gris',   'vert'),
  ('noir',   'orange', 'vert',   'marron'),
  ('bleu',   'rose',   'marron', 'jaune'),
  ('vert',   'rouge',  'marron', 'orange'),
  ('orange', 'jaune',  'rose',   'rouge'),
  ('gris',   'bleu',   'rouge',  'noir'),
  ('jaune',  'vert',   'orange', 'gris')
--------------
 
--------------
select * from test
--------------
 
+----+--------+--------+--------+--------+
| id | cat1   | cat2   | cat3   | cat4   |
+----+--------+--------+--------+--------+
|  1 | rouge  | vert   | jaune  | bleu   |
|  2 | bleu   | marron | noir   | gris   |
|  3 | marron | gris   | bleu   | rose   |
|  4 | rose   | noir   | gris   | vert   |
|  5 | noir   | orange | vert   | marron |
|  6 | bleu   | rose   | marron | jaune  |
|  7 | vert   | rouge  | marron | orange |
|  8 | orange | jaune  | rose   | rouge  |
|  9 | gris   | bleu   | rouge  | noir   |
| 10 | jaune  | vert   | orange | gris   |
+----+--------+--------+--------+--------+
--------------
explain
      select * from test where cat1 like 'rose'
union select * from test where cat2 like 'rose'
union select * from test where cat3 like 'rose'
union select * from test where cat4 like 'rose'
--------------
 
+------+--------------+----------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|   id | select_type  | table          | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+------+--------------+----------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|    1 | PRIMARY      | test           | NULL       | range | idx_cat1      | idx_cat1 | 10      | NULL |    1 |   100.00 | Using index condition |
|    2 | UNION        | test           | NULL       | range | idx_cat2      | idx_cat2 | 10      | NULL |    1 |   100.00 | Using index condition |
|    3 | UNION        | test           | NULL       | range | idx_cat3      | idx_cat3 | 10      | NULL |    1 |   100.00 | Using index condition |
|    4 | UNION        | test           | NULL       | range | idx_cat4      | idx_cat4 | 10      | NULL |    1 |   100.00 | Using index condition |
| NULL | UNION RESULT | <union1,2,3,4> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | NULL |     NULL | Using temporary       |
+------+--------------+----------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
--------------
select * from test where cat1 like 'rose'
union select * from test where cat2 like 'rose'
union select * from test where cat3 like 'rose'
union select * from test where cat4 like 'rose'
--------------
 
+----+--------+-------+--------+-------+
| id | cat1   | cat2  | cat3   | cat4  |
+----+--------+-------+--------+-------+
|  4 | rose   | noir  | gris   | vert  |
|  6 | bleu   | rose  | marron | jaune |
|  8 | orange | jaune | rose   | rouge |
|  3 | marron | gris  | bleu   | rose  |
+----+--------+-------+--------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Si vous voulez que l'on réponde précisément à votre problème de performance, il faudrait exposer ce que vous chercher à faire en réalité.

@+