Trouver le meilleur index

**laraki.fissel** · 20/02/2017, 23h56

Bonjour
voici le context: j'ai un serveur MariaDB 10.1, deux tables MyISAM, chacune contient environ 2 millions de lignes. les prédicats de jointure disposent du même type (varchar) et même collation, les prédicats where sont tous de types varchar.
un index est créé pour chaque prédicat, un autre index composé est créé pour la jointure (num_matricule,code_client).
MariaDB n'arrive pas à utiliser un seul index, il préfère de scaner toute la table.

la requete est la suivante :

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
SELECT 
  COUNT(
    `bv`.`i0_`.`enreg`
  ) AS `sclr_0` 
FROM `bv`.`paper` `i0_` 
LEFT JOIN `bvr`.`indiv` `iin` 
  ON `i0_`.`num_matricule` = `iin`.`num_matricule`  
  AND `i0_`.`code_client` = `iin`.`code_client`
  AND `i0_`.`num_matricule` IS NOT NULL
WHERE (
    (
      (
        (
          `i0_`.`code_document` IN ('D751001','D751010','D751020','D751000','D801000','D801001')
        ) 
        AND (
          (
            `i0_`.`code_client` <= '852146'
          ) 
          OR (
            `iin`.`code_client` <= '852146'
          )
        )
      ) 
      OR (
        (
          `i0_`.`code_document` IN ('G100035','G100040','G100030','G100025','G100010')
        ) 
        AND (
          `i0_`.`code_client` <= '852146'
        )
      )
    ) 
    AND (
      `i0_`.`status` = 'OK'
    ) 
    AND (
      `i0_`.`code_document` IN ('G100035','G100040','G100045','G100030','G100025')
    )
  )

merci pour votre aide.

**vttman** · 21/02/2017, 10h10

Bonjour,
Je peux me tromper bien sûr ... ensuite je ne réponds pas directement à la question posée

Mais la requête précédente semble se résumer
à celle ci (à voir ensuite l'utilité du LEFT JOIN ?)
=>

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
  COUNT(
    `bv`.`i0_`.`enreg`
  ) AS `sclr_0` 
FROM `bv`.`paper` `i0_` 
LEFT JOIN `bvr`.`indiv` `iin` 
  ON `i0_`.`num_matricule` = `iin`.`num_matricule`  
  AND `i0_`.`code_client` = `iin`.`code_client`
WHERE 
`i0_`.`num_matricule` IS NOT NULL
AND `i0_`.`code_document` IN ('G100035','G100040','G100030','G100025')
AND `i0_`.`code_client` <= '852146'
AND `i0_`.`status` = 'OK'

En effet pour illustrer mes coupes sauvages, supposons que

`i0_`.`code_document` = 'D751001'

=>

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
WHERE (
    (
      (
        (
          `i0_`.`code_document` IN ('D751001','D751010','D751020','D751000','D801000','D801001') VRAI
        ) 
        AND (
          (
            `i0_`.`code_client` <= '852146'           ) 
          OR (
            `iin`.`code_client` <= '852146'
          ) SUPPOSONS VRAI
        )
      ) 
      OR (
        (
          `i0_`.`code_document` IN ('G100035','G100040','G100030','G100025','G100010') FALSE
        ) 
        AND (
          `i0_`.`code_client` <= '852146'
        )
      )
    ) 
    AND (
      `i0_`.`status` = 'OK' SUPPOSONS VRAI
    ) 
    AND (
      `i0_`.`code_document` IN ('G100035','G100040','G100045','G100030','G100025') FALSE
    )
  )

=>

WHERE (
(
Résumé du premier pavé : VRAI
AND (
`i0_`.`status` = 'OK' SUPPOSONS VRAI
)
AND (
`i0_`.`code_document` IN ('G100035','G100040','G100045','G100030','G100025') FALSE
)
)

donc `i0_`.`code_document` = 'D751001' ne serait jamais pris ?

Bon si je me suis perdu dans les () ou que je dis des âneries, tant pis ... je n'ai pas la base sous la main pour passer des requêtes ...

**laraki.fissel** · 21/02/2017, 10h58

Bonjour vttman
Merci d’avoir réagi à ma demande,
Effectivement ton raisonnement est vrai, sauf que c’est une requete générée automatiquement, du coup on peut trouver facilement dans la première condition des code qui commencent par G, voici un exemple :

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
SELECT 
  COUNT(
    `bv`.`i0_`.`enreg`
  ) AS `sclr_0` 
FROM `bv`.`paper` `i0_` 
LEFT JOIN `bvr`.`indiv` `iin` 
  ON `i0_`.`num_matricule` = `iin`.`num_matricule`  
  AND `i0_`.`code_client` = `iin`.`code_client`
  AND `i0_`.`num_matricule` IS NOT NULL
WHERE (
    (
      (
        (
          `i0_`.`code_document` IN ('D751001','D751010','D751020','D751000','D801000','D801001','G100045','G350010','G651170')
        ) 
        AND (
          (
            `i0_`.`code_client` <= '852146'
          ) 
          OR (
            `iin`.`code_client` <= '852146'
          )
        )
      ) 
      OR (
        (
          `i0_`.`code_document` IN ('G100035','G100040','G100030','G100025','G100010')
        ) 
        AND (
          `i0_`.`code_client` <= '852146'
        )
      )
    ) 
 
 
    AND (
      `i0_`.`status` = 'OK' vrai
    ) 
    AND (
      `i0_`.`code_document` IN ('G100035','G100040','G100045','G100030','G100025')
    )
  )

**Artemus24** · 21/02/2017, 13h27

Salut laraki.fissel.

J'ai un peu regardé votre requête et je comprends pourquoi vous n'obtenez pas la performance que vous recherchez. Voici quelques remarques :

1) il est inutile de mettre partout l'apostrophe oblique (`). Faites le que sur les mots réservés !

2) la clause "in" ne permet pas d'avoir un index associé à la colonne "code_document". Il faut transformer le "in" en :

Code :

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

1
2
3
where code_document = 'D751000' 
  and code_document = ...
  and code_document = 'G100045'

3) le test "code_client <= '852146'" est une très mauvaise idée.
D'abord, c'est une chaîne de caractères que vous manipulez comme un nombre.
Ensuite, le test d'inégalité ne permet pas de se restreindre à l'usage d'un index.

3) la colonne "status" est mal utilisé dans ce que vous désirez faire.
Créer une chaîne de caractères pour mettre "OK" ou "KO", n'est pas une bonne idée.
Il serait plus judicieux de définir la colonne en tant que booléen :

Code :

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

status boolean not null,

Cette déclarative est équivalente à un :

Code :

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

status tinyint unsigned not null,

Vous pouvez initialiser cette colonne par '1' pour "OK" et par '0' pour "KO".

La restriction de cette colonne n'étant pas significative, l'index ne vous sert à rien.

4) hormis la remarque de vttman, votre requête est mal foutue !
Et par voie de conséquence, votre base de données l'est aussi.

Je constate que la table "paper" est une table associative mal structurée.
Deux parents, dont l'un est le document et l'autre l'identifiant du client.
Ainsi le numéro matricule qui se rapporte au code client n'a aucune raison d'être là.
Ou alors, vous avez trois parents, le document, l'identifiant client et le matricule.

Ce qui signifie qu'il faut mieux manipuler des identifiants techniques de type numérique plus que des chaînes de caractères.
Autrement dit, vous devez créer trois tables mères :
--> table document avec la colonne "code_document" de type char(07).
--> table client avec la colonne "code_client" de type char(06).
--> table matricule avec la colonne "code_matricule" de type char(06).
Il est inutile de mettre varchar car la longueur de ces colonnes sont fixes.

La table paper devient donc une table associative avec trois clef étrangères :
--> id_document qui pointe sur la clef primaire de la table document.
--> id_client qui pointe sur la clef primaire de la table client.
--> id_matricule qui pointe sur la clef primaire de la table matricule.
Pour ces trois clefs étrangères et les trois clef primaires, vous devez définir comme type "id integer unsigned not null auto_increment primary key,Et bien sûr, pour faire la comparaison entre la table "paper" de la base "bv" et "indiv" de la base "bvr", ces deux tables doivent avec la même structure.

5) en l'état actuel de votre requête et de ce que l'on peut imaginer de vos bases de données, il n'est pas possible de faire autrement qu'un balayage de la totalité des lignes pour chaque table.

Cela se nomme un "Using join buffer (block nested loop)".
Autrement dit, pour une ligne de la table 1 (nombre de lignes N1), il fait un balayage de la totalité de la table 2 (nombre de lignes N2).
Soit une cardinalité N1 * N2 !

@+

**Artemus24** · 21/02/2017, 14h15

Re bonjour.

Il y a aussi un problème dans votre requête.

Seul les codes document : 'G100025', 'G100030', 'G100035', 'G100040' seront sélectionnées. Tout le reste sera rejeté !
Donc votre requête peut se simplfier en

Code :

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

1
2
3
4
5
6
7
8
9
10
    SELECT  COUNT( bv.i0_.enreg) AS sclr_0 
      FROM  bv.paper  as i0_
 
INNER JOIN  bvr.indiv as iin 
        ON  iin.num_matricule = i0_.num_matricule
       AND  iin.code_client   = i0_.code_client
 
     WHERE  i0_.status         = 'OK'
       AND  i0_.code_client   <= '852146'
       AND  i0_.code_document IN ('G100025','G100030','G100035','G100040');

Voici ce que je propose comme solution :

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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `bv`
--------------
 
--------------
CREATE DATABASE `bv`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `paper`
--------------
 
--------------
CREATE TABLE `paper`
( `id`             integer unsigned not null auto_increment primary key,
  `status`         tinyint unsigned not null,
  `code_client`       char(06)      not null,
  `code_document`     char(07)      not null,
  `num_matricule`     char(06)      not null,
  `enreg`          varchar(255)     not null,
  index `idx_1` (`code_document`)
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `paper` (`code_document`,`code_client`,`num_matricule`,`status`,`enreg`) values
  ('D751000','850001','000001',1,'enreg'),
  ('xxxxxxx','850013','000013',0,'enreg'),
  ('xxxxxxx','850014','000014',1,'enreg'),
  ('D751001','850002','000002',1,'enreg'),
  ('xxxxxxx','850015','000015',0,'enreg'),
  ('xxxxxxx','850016','000016',1,'enreg'),
  ('D751010','850003','000003',1,'enreg'),
  ('xxxxxxx','850017','000017',0,'enreg'),
  ('xxxxxxx','850018','000018',1,'enreg'),
  ('D751020','850004','000004',1,'enreg'),
  ('ooooooo','850019','000019',0,'enreg'),
  ('ooooooo','850020','000020',1,'enreg'),
  ('D801000','850005','000005',1,'enreg'),
  ('ooooooo','850021','000021',0,'enreg'),
  ('ooooooo','850022','000022',1,'enreg'),
  ('D801001','850006','000006',1,'enreg'),
  ('ooooooo','850023','000023',0,'enreg'),
  ('ooooooo','850024','000024',1,'enreg'),
  ('G100010','850007','000007',1,'enreg'),
  ('ooooooo','850025','000025',0,'enreg'),
  ('ooooooo','850026','000026',1,'enreg'),
  ('G100025','850008','000008',1,'enreg'),
  ('ooooooo','850027','000027',0,'enreg'),
  ('ooooooo','850028','000028',0,'enreg'),
  ('G100030','850009','000009',1,'enreg'),
  ('ooooooo','850029','000029',0,'enreg'),
  ('ooooooo','850030','000030',0,'enreg'),
  ('G100035','850010','000010',1,'enreg'),
  ('ooooooo','850031','000031',0,'enreg'),
  ('ooooooo','850032','000032',0,'enreg'),
  ('G100040','850011','000011',1,'enreg'),
  ('ooooooo','850033','000033',0,'enreg'),
  ('ooooooo','850034','000034',0,'enreg'),
  ('G100045','850012','000012',1,'enreg')
--------------
 
--------------
select * from `paper`
--------------
 
+----+--------+-------------+---------------+---------------+-------+
| id | status | code_client | code_document | num_matricule | enreg |
+----+--------+-------------+---------------+---------------+-------+
|  1 |      1 | 850001      | D751000       | 000001        | enreg |
|  2 |      0 | 850013      | xxxxxxx       | 000013        | enreg |
|  3 |      1 | 850014      | xxxxxxx       | 000014        | enreg |
|  4 |      1 | 850002      | D751001       | 000002        | enreg |
|  5 |      0 | 850015      | xxxxxxx       | 000015        | enreg |
|  6 |      1 | 850016      | xxxxxxx       | 000016        | enreg |
|  7 |      1 | 850003      | D751010       | 000003        | enreg |
|  8 |      0 | 850017      | xxxxxxx       | 000017        | enreg |
|  9 |      1 | 850018      | xxxxxxx       | 000018        | enreg |
| 10 |      1 | 850004      | D751020       | 000004        | enreg |
| 11 |      0 | 850019      | ooooooo       | 000019        | enreg |
| 12 |      1 | 850020      | ooooooo       | 000020        | enreg |
| 13 |      1 | 850005      | D801000       | 000005        | enreg |
| 14 |      0 | 850021      | ooooooo       | 000021        | enreg |
| 15 |      1 | 850022      | ooooooo       | 000022        | enreg |
| 16 |      1 | 850006      | D801001       | 000006        | enreg |
| 17 |      0 | 850023      | ooooooo       | 000023        | enreg |
| 18 |      1 | 850024      | ooooooo       | 000024        | enreg |
| 19 |      1 | 850007      | G100010       | 000007        | enreg |
| 20 |      0 | 850025      | ooooooo       | 000025        | enreg |
| 21 |      1 | 850026      | ooooooo       | 000026        | enreg |
| 22 |      1 | 850008      | G100025       | 000008        | enreg |
| 23 |      0 | 850027      | ooooooo       | 000027        | enreg |
| 24 |      0 | 850028      | ooooooo       | 000028        | enreg |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |
| 26 |      0 | 850029      | ooooooo       | 000029        | enreg |
| 27 |      0 | 850030      | ooooooo       | 000030        | enreg |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |
| 29 |      0 | 850031      | ooooooo       | 000031        | enreg |
| 30 |      0 | 850032      | ooooooo       | 000032        | enreg |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |
| 32 |      0 | 850033      | ooooooo       | 000033        | enreg |
| 33 |      0 | 850034      | ooooooo       | 000034        | enreg |
| 34 |      1 | 850012      | G100045       | 000012        | enreg |
+----+--------+-------------+---------------+---------------+-------+
--------------
DROP DATABASE IF EXISTS `bvr`
--------------
 
--------------
CREATE DATABASE `bvr`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `indiv`
--------------
 
--------------
CREATE TABLE `indiv`
( `id`             integer unsigned not null auto_increment primary key,
  `code_client`    char(06)         not null,
  `num_matricule`  char(06)         not null,
  `status`         tinyint unsigned not null,
  index `idx_2` (`code_client`,`num_matricule`)
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `indiv` (`code_client`,`num_matricule`,`status`) values
  ('850001','000001',1),
  ('850002','000002',1),
  ('850003','000003',1),
  ('850004','000004',1),
  ('850005','000005',1),
  ('850006','000006',1),
  ('850007','000007',1),
  ('850008','000008',1),
  ('850009','000009',1),
  ('850010','000010',1),
  ('850011','000011',1),
  ('850012','000012',1)
--------------
 
--------------
select * from `indiv`
--------------
 
+----+-------------+---------------+--------+
| id | code_client | num_matricule | status |
+----+-------------+---------------+--------+
|  1 | 850001      | 000001        |      1 |
|  2 | 850002      | 000002        |      1 |
|  3 | 850003      | 000003        |      1 |
|  4 | 850004      | 000004        |      1 |
|  5 | 850005      | 000005        |      1 |
|  6 | 850006      | 000006        |      1 |
|  7 | 850007      | 000007        |      1 |
|  8 | 850008      | 000008        |      1 |
|  9 | 850009      | 000009        |      1 |
| 10 | 850010      | 000010        |      1 |
| 11 | 850011      | 000011        |      1 |
| 12 | 850012      | 000012        |      1 |
+----+-------------+---------------+--------+
--------------
select  *
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    WHERE (( (( i0_.code_document IN  ('D751001','D751010','D751020','D751000','D801000','D801001'))
      AND     ((i0_.code_client   <=  '852146')
       OR      (iin.code_client   <=  '852146')))
       OR    (( i0_.code_document IN  ('G100035','G100040','G100030','G100025','G100010'))
      AND     ( i0_.code_client   <=  '852146')))
      AND  (    i0_.status         =  1 )
      AND  (    i0_.code_document IN  ('G100035','G100040','G100045','G100030','G100025')))
--------------
 
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| id | status | code_client | code_document | num_matricule | enreg | id   | code_client | num_matricule | status |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| 22 |      1 | 850008      | G100025       | 000008        | enreg |    8 | 850008      | 000008        |      1 |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |    9 | 850009      | 000009        |      1 |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |   10 | 850010      | 000010        |      1 |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |   11 | 850011      | 000011        |      1 |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
--------------
explain
   SELECT  COUNT(bv.i0_.enreg) AS sclr_0
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    WHERE (( (( i0_.code_document IN  ('D751001','D751010','D751020','D751000','D801000','D801001'))
      AND     ((i0_.code_client   <=  '852146')
       OR      (iin.code_client   <=  '852146')))
       OR    (( i0_.code_document IN  ('G100035','G100040','G100030','G100025','G100010'))
      AND     ( i0_.code_client   <=  '852146')))
      AND  (    i0_.status         =  1 )
      AND  (    i0_.code_document IN  ('G100035','G100040','G100045','G100030','G100025')))
--------------
 
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref                                     | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | i0_   | NULL       | range | idx_1         | idx_1 | 7       | NULL                                    |    4 |    10.00 | Using index condition; Using where |
|  1 | SIMPLE      | iin   | NULL       | ref   | idx_2         | idx_2 | 12      | bv.i0_.code_client,bv.i0_.num_matricule |    2 |   100.00 | Using where; Using index           |
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
--------------
select  *
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    where  i0_.status         =  1
          AND  i0_.code_client   <=  '852146'
      AND  i0_.code_document IN  ('G100025','G100030','G100035','G100040')
--------------
 
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| id | status | code_client | code_document | num_matricule | enreg | id   | code_client | num_matricule | status |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| 22 |      1 | 850008      | G100025       | 000008        | enreg |    8 | 850008      | 000008        |      1 |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |    9 | 850009      | 000009        |      1 |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |   10 | 850010      | 000010        |      1 |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |   11 | 850011      | 000011        |      1 |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Le résultat dépend aussi de mon jeu d'essai.

@+

**laraki.fissel** · 21/02/2017, 16h49

Artemus24, je viens de voir ta solution, mais quand on parle des milions de lignes, mysql change son plan d'exécution.
je dispose exactement de la même structure, mais j'ai au moins 2 milions de lignes, alors Mysql préfère de scanner la table au lieu d'utiliser les index.

Merci bcp Artemus24

Envoyé par Artemus24

Re bonjour.

Il y a aussi un problème dans votre requête.

Seul les codes document : 'G100025', 'G100030', 'G100035', 'G100040' seront sélectionnées. Tout le reste sera rejeté !
Donc votre requête peut se simplfier en

Code :

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

1
2
3
4
5
6
7
8
9
10
    SELECT  COUNT( bv.i0_.enreg) AS sclr_0 
      FROM  bv.paper  as i0_
 
INNER JOIN  bvr.indiv as iin 
        ON  iin.num_matricule = i0_.num_matricule
       AND  iin.code_client   = i0_.code_client
 
     WHERE  i0_.status         = 'OK'
       AND  i0_.code_client   <= '852146'
       AND  i0_.code_document IN ('G100025','G100030','G100035','G100040');

Voici ce que je propose comme solution :

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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `bv`
--------------
 
--------------
CREATE DATABASE `bv`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `paper`
--------------
 
--------------
CREATE TABLE `paper`
( `id`             integer unsigned not null auto_increment primary key,
  `status`         tinyint unsigned not null,
  `code_client`       char(06)      not null,
  `code_document`     char(07)      not null,
  `num_matricule`     char(06)      not null,
  `enreg`          varchar(255)     not null,
  index `idx_1` (`code_document`)
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `paper` (`code_document`,`code_client`,`num_matricule`,`status`,`enreg`) values
  ('D751000','850001','000001',1,'enreg'),
  ('xxxxxxx','850013','000013',0,'enreg'),
  ('xxxxxxx','850014','000014',1,'enreg'),
  ('D751001','850002','000002',1,'enreg'),
  ('xxxxxxx','850015','000015',0,'enreg'),
  ('xxxxxxx','850016','000016',1,'enreg'),
  ('D751010','850003','000003',1,'enreg'),
  ('xxxxxxx','850017','000017',0,'enreg'),
  ('xxxxxxx','850018','000018',1,'enreg'),
  ('D751020','850004','000004',1,'enreg'),
  ('ooooooo','850019','000019',0,'enreg'),
  ('ooooooo','850020','000020',1,'enreg'),
  ('D801000','850005','000005',1,'enreg'),
  ('ooooooo','850021','000021',0,'enreg'),
  ('ooooooo','850022','000022',1,'enreg'),
  ('D801001','850006','000006',1,'enreg'),
  ('ooooooo','850023','000023',0,'enreg'),
  ('ooooooo','850024','000024',1,'enreg'),
  ('G100010','850007','000007',1,'enreg'),
  ('ooooooo','850025','000025',0,'enreg'),
  ('ooooooo','850026','000026',1,'enreg'),
  ('G100025','850008','000008',1,'enreg'),
  ('ooooooo','850027','000027',0,'enreg'),
  ('ooooooo','850028','000028',0,'enreg'),
  ('G100030','850009','000009',1,'enreg'),
  ('ooooooo','850029','000029',0,'enreg'),
  ('ooooooo','850030','000030',0,'enreg'),
  ('G100035','850010','000010',1,'enreg'),
  ('ooooooo','850031','000031',0,'enreg'),
  ('ooooooo','850032','000032',0,'enreg'),
  ('G100040','850011','000011',1,'enreg'),
  ('ooooooo','850033','000033',0,'enreg'),
  ('ooooooo','850034','000034',0,'enreg'),
  ('G100045','850012','000012',1,'enreg')
--------------
 
--------------
select * from `paper`
--------------
 
+----+--------+-------------+---------------+---------------+-------+
| id | status | code_client | code_document | num_matricule | enreg |
+----+--------+-------------+---------------+---------------+-------+
|  1 |      1 | 850001      | D751000       | 000001        | enreg |
|  2 |      0 | 850013      | xxxxxxx       | 000013        | enreg |
|  3 |      1 | 850014      | xxxxxxx       | 000014        | enreg |
|  4 |      1 | 850002      | D751001       | 000002        | enreg |
|  5 |      0 | 850015      | xxxxxxx       | 000015        | enreg |
|  6 |      1 | 850016      | xxxxxxx       | 000016        | enreg |
|  7 |      1 | 850003      | D751010       | 000003        | enreg |
|  8 |      0 | 850017      | xxxxxxx       | 000017        | enreg |
|  9 |      1 | 850018      | xxxxxxx       | 000018        | enreg |
| 10 |      1 | 850004      | D751020       | 000004        | enreg |
| 11 |      0 | 850019      | ooooooo       | 000019        | enreg |
| 12 |      1 | 850020      | ooooooo       | 000020        | enreg |
| 13 |      1 | 850005      | D801000       | 000005        | enreg |
| 14 |      0 | 850021      | ooooooo       | 000021        | enreg |
| 15 |      1 | 850022      | ooooooo       | 000022        | enreg |
| 16 |      1 | 850006      | D801001       | 000006        | enreg |
| 17 |      0 | 850023      | ooooooo       | 000023        | enreg |
| 18 |      1 | 850024      | ooooooo       | 000024        | enreg |
| 19 |      1 | 850007      | G100010       | 000007        | enreg |
| 20 |      0 | 850025      | ooooooo       | 000025        | enreg |
| 21 |      1 | 850026      | ooooooo       | 000026        | enreg |
| 22 |      1 | 850008      | G100025       | 000008        | enreg |
| 23 |      0 | 850027      | ooooooo       | 000027        | enreg |
| 24 |      0 | 850028      | ooooooo       | 000028        | enreg |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |
| 26 |      0 | 850029      | ooooooo       | 000029        | enreg |
| 27 |      0 | 850030      | ooooooo       | 000030        | enreg |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |
| 29 |      0 | 850031      | ooooooo       | 000031        | enreg |
| 30 |      0 | 850032      | ooooooo       | 000032        | enreg |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |
| 32 |      0 | 850033      | ooooooo       | 000033        | enreg |
| 33 |      0 | 850034      | ooooooo       | 000034        | enreg |
| 34 |      1 | 850012      | G100045       | 000012        | enreg |
+----+--------+-------------+---------------+---------------+-------+
--------------
DROP DATABASE IF EXISTS `bvr`
--------------
 
--------------
CREATE DATABASE `bvr`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `indiv`
--------------
 
--------------
CREATE TABLE `indiv`
( `id`             integer unsigned not null auto_increment primary key,
  `code_client`    char(06)         not null,
  `num_matricule`  char(06)         not null,
  `status`         tinyint unsigned not null,
  index `idx_2` (`code_client`,`num_matricule`)
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `indiv` (`code_client`,`num_matricule`,`status`) values
  ('850001','000001',1),
  ('850002','000002',1),
  ('850003','000003',1),
  ('850004','000004',1),
  ('850005','000005',1),
  ('850006','000006',1),
  ('850007','000007',1),
  ('850008','000008',1),
  ('850009','000009',1),
  ('850010','000010',1),
  ('850011','000011',1),
  ('850012','000012',1)
--------------
 
--------------
select * from `indiv`
--------------
 
+----+-------------+---------------+--------+
| id | code_client | num_matricule | status |
+----+-------------+---------------+--------+
|  1 | 850001      | 000001        |      1 |
|  2 | 850002      | 000002        |      1 |
|  3 | 850003      | 000003        |      1 |
|  4 | 850004      | 000004        |      1 |
|  5 | 850005      | 000005        |      1 |
|  6 | 850006      | 000006        |      1 |
|  7 | 850007      | 000007        |      1 |
|  8 | 850008      | 000008        |      1 |
|  9 | 850009      | 000009        |      1 |
| 10 | 850010      | 000010        |      1 |
| 11 | 850011      | 000011        |      1 |
| 12 | 850012      | 000012        |      1 |
+----+-------------+---------------+--------+
--------------
select  *
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    WHERE (( (( i0_.code_document IN  ('D751001','D751010','D751020','D751000','D801000','D801001'))
      AND     ((i0_.code_client   <=  '852146')
       OR      (iin.code_client   <=  '852146')))
       OR    (( i0_.code_document IN  ('G100035','G100040','G100030','G100025','G100010'))
      AND     ( i0_.code_client   <=  '852146')))
      AND  (    i0_.status         =  1 )
      AND  (    i0_.code_document IN  ('G100035','G100040','G100045','G100030','G100025')))
--------------
 
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| id | status | code_client | code_document | num_matricule | enreg | id   | code_client | num_matricule | status |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| 22 |      1 | 850008      | G100025       | 000008        | enreg |    8 | 850008      | 000008        |      1 |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |    9 | 850009      | 000009        |      1 |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |   10 | 850010      | 000010        |      1 |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |   11 | 850011      | 000011        |      1 |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
--------------
explain
   SELECT  COUNT(bv.i0_.enreg) AS sclr_0
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    WHERE (( (( i0_.code_document IN  ('D751001','D751010','D751020','D751000','D801000','D801001'))
      AND     ((i0_.code_client   <=  '852146')
       OR      (iin.code_client   <=  '852146')))
       OR    (( i0_.code_document IN  ('G100035','G100040','G100030','G100025','G100010'))
      AND     ( i0_.code_client   <=  '852146')))
      AND  (    i0_.status         =  1 )
      AND  (    i0_.code_document IN  ('G100035','G100040','G100045','G100030','G100025')))
--------------
 
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref                                     | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | i0_   | NULL       | range | idx_1         | idx_1 | 7       | NULL                                    |    4 |    10.00 | Using index condition; Using where |
|  1 | SIMPLE      | iin   | NULL       | ref   | idx_2         | idx_2 | 12      | bv.i0_.code_client,bv.i0_.num_matricule |    2 |   100.00 | Using where; Using index           |
+----+-------------+-------+------------+-------+---------------+-------+---------+-----------------------------------------+------+----------+------------------------------------+
--------------
select  *
 
     FROM  bv.paper as i0_
 
LEFT JOIN  bvr.indiv as iin
       ON  i0_.num_matricule = iin.num_matricule
      AND  i0_.code_client   = iin.code_client
      AND  i0_.num_matricule IS NOT NULL
 
    where  i0_.status         =  1
          AND  i0_.code_client   <=  '852146'
      AND  i0_.code_document IN  ('G100025','G100030','G100035','G100040')
--------------
 
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| id | status | code_client | code_document | num_matricule | enreg | id   | code_client | num_matricule | status |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
| 22 |      1 | 850008      | G100025       | 000008        | enreg |    8 | 850008      | 000008        |      1 |
| 25 |      1 | 850009      | G100030       | 000009        | enreg |    9 | 850009      | 000009        |      1 |
| 28 |      1 | 850010      | G100035       | 000010        | enreg |   10 | 850010      | 000010        |      1 |
| 31 |      1 | 850011      | G100040       | 000011        | enreg |   11 | 850011      | 000011        |      1 |
+----+--------+-------------+---------------+---------------+-------+------+-------------+---------------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Le résultat dépend aussi de mon jeu d'essai.

@+

**laraki.fissel** · 21/02/2017, 14h16

Bonjour Artemus24

merci d'avoir réagi à ma demande.

je suis tout a fait d'accord avec toi, c'est une table totalement dénormalisée, par contre c'est l'existant, et l'équipe n'a pas l'intention de faire des changements au niveau de la structure, du coup j'ai essayé d'optimiser le maximum possible un ensemble de requetes, mais celle que je t'ai montré pose vraimenet un problème, mysql préfère tjs de scanner toute la table.

**Artemus24** · 21/02/2017, 14h52

Salut laraki.fissel.

Je constate que nos deux messages se sont croisés à 1 minute prêt.
Je propose une solution qui peut améliorer un peu votre problème de performance.

Je ne réponds pas aux questions par MP. Autant la poser dans ce sujet !

@+

**laraki.fissel** · 21/02/2017, 16h39

Artemus24
ce n'était pas une question liée au sujet de la requête
je voulais simplement savoir s'il y a un moyen pour savoir le nombre de page (contenant les données) parcourues par mysql en exécutant une requête, show processlist affiche le temps d'exécution de la requête, mais il inclut aussi le temps de wait (différents lock).
SQL Server permet d'identifier exactement le nombre de pages parcourues lors d'une exécution d'une requête.
Voila c'était le contenu de mon MP.

Merci.

**Artemus24** · 21/02/2017, 17h34

Salut laraki.fissel.

Je pense que vous trouverez ce que vous cherchez en faisant "SHOW GLOBAL STATUS WHERE VALUE != 0".

Avant de poursuivre, j'aimerai savoir si votre requête est bien celle que vous utilisez ?
Déjà VTTMAN considère que l'on peut la simplifier mais je considère qu'elle est trop compliquée par rapport à ce que vous obtenez comme résultat.

Et j'aimerai savoir combien de lignes obtenez-vous à l'exécution de votre requête ?
Et est-ce toujours à peu près le même ordre de grandeur ?

J'aimerai savoir si vos deux bases de données sont sur le même serveur MySql ?

Envoyé par laraki.fissel

quand on parle des millions de lignes, mysql change son plan d'exécution

Oui, je sais, et c'est pourquoi donner par un forum interposé, un conseil d'optimisation sans avoir la base de données pour faire les tests, est mission impossible.
Ce n'est pas en fin de projet qu'il faut penser à l'optimisation de vos requêtes.

Ce que je peux faire, c'est poursuivre l'optimisation chez moi, mais il me faut un fichier export de vos deux tables avec un jeu d'essai complet.
Ainsi que la requête final qui sera testé.
Cette requête ne devra plus du tout changer !

@+

**laraki.fissel** · 21/02/2017, 17h52

salut Artemus24
la requete renvoie une seule ligne, les deux bases se trouvent dans le même serveur, la requête est générée automatiquement par un générateur, du coup parfois on peut se retrouver avec condition sur code document avec une centaine de codes.
les deux tables font 25 Go.

merci

Envoyé par Artemus24

Salut laraki.fissel.

Je pense que vous trouverez ce que vous cherchez en faisant "SHOW GLOBAL STATUS WHERE VALUE != 0".

Avant de poursuivre, j'aimerai savoir si votre requête est bien celle que vous utilisez ?
Déjà VTTMAN considère que l'on peut la simplifier mais je considère qu'elle est trop compliquée par rapport à ce que vous obtenez comme résultat.

Et j'aimerai savoir combien de lignes obtenez-vous à l'exécution de votre requête ?
Et est-ce toujours à peu près le même ordre de grandeur ?

J'aimerai savoir si vos deux bases de données sont sur le même serveur MySql ?

Oui, je sais, et c'est pourquoi donner par un forum interposé, un conseil d'optimisation sans avoir la base de données pour faire les tests, est mission impossible.
Ce n'est pas en fin de projet qu'il faut penser à l'optimisation de vos requêtes.

Ce que je peux faire, c'est poursuivre l'optimisation chez moi, mais il me faut un fichier export de vos deux tables avec un jeu d'essai complet.
Ainsi que la requête final qui sera testé.
Cette requête ne devra plus du tout changer !

@+

**Artemus24** · 22/02/2017, 10h46

Salut laraki.fissel.

Ne répondez pas avec citation, surtout si vous répondez au dernier message !

Envoyé par laraki.fissel

la requête renvoie une seule ligne,

Je sais très bien que votre requête finale retourne une seule ligne, mais je parlais du nombre de lignes avant l'exécution du count().
Autrement dit, votre count() retourne quelle valeur ?

Envoyé par laraki.fissel

la requête est générée automatiquement par un générateur, du coup parfois on peut se retrouver avec condition sur code document avec une centaine de codes.

Il sera difficile d'optimiser une requête si elle change tout le temps !

Envoyé par laraki.fissel

les deux tables font 25 Go.

Donc, vous ne compressez pas vos tables, ce qui vous ferait gagner un peu d'espace disque.

@+