Optimiser requête avec grosses tables

**totatota** · 24/10/2015, 00h06

Bonjour
J'ai une requête qui donne des résultats correctes, mais je souhaite l'optimiser car elle prends beaucoup de temps à rendre les valeurs.

Code :

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

1
2
3
4
SELECT mdl_course.id ,  
(SELECT COUNT(mdl_log.id) FROM mdl_log WHERE mdl_log.course = mdl_course.id ) AS OLD , 
(SELECT COUNT(mdl_logstore_standard_log.id) FROM mdl_logstore_standard_log WHERE mdl_logstore_standard_log.courseid = mdl_course.id) AS NEW 
FROM mdl_course  WHERE mdl_course.visible = 1 GROUP BY mdl_course.id having (OLD+NEW) > 10;

Je cherche la liste des cours qui ont des traces dans une ancienne table de log (OLD) et une nouvelle table de log (NEW) avec la condition que la somme des log soit supérieure à 10. La table "mdl_course" est au moins 10000 cours, les deux tables de log sont énormes.

PS : les deux tables de log sont différentes, je suis sur MySQL

Comment optimiser cette requête ?

Merci par avance

**punkoff** · 26/10/2015, 10h39

Bonjour,

Sur les 10.000 lignes présentes dans la table mdl_course, combien répondent au critère mdl_course.visible = 1 ?

**escartefigue** · 26/10/2015, 12h13

Bonjour,

Pour pouvoir réfléchir à l'optimisation, il faut que vous précisiez quels sont les index présents sur les différentes tables, et les volumétries de ces tables, énorme, ça ne veut pas dire grand chose

**totatota** · 26/10/2015, 13h32

bonjour,

Voici le résultat du Explain , ça peut servir et répondre en partie aux questions :-)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
mysql> explain SELECT mdl_course.id , mdl_course.shortname ,  
    -> (SELECT COUNT(mdl_log.id) FROM mdl_log WHERE mdl_log.course = mdl_course.id  AND (mdl_log.time BETWEEN UNIX_TIMESTAMP('2014-09-01' ) AND UNIX_TIMESTAMP('2015-07-01'))  ) AS ANCIEN , 
    -> (SELECT COUNT(mdl_logstore_standard_log.id) FROM mdl_logstore_standard_log WHERE mdl_logstore_standard_log.courseid = mdl_course.id AND mdl_logstore_standard_log.origin='web') AS NOUVEAU 
    -> FROM mdl_course  WHERE mdl_course.visible = 1 GROUP BY mdl_course.id having(ANCIEN+NOUVEAU) > 10 ; 
+------+--------------------+---------------------------+-------+-------------------------------------+------------------------------+---------+------+---------+-------------+
| id   | select_type        | table                     | type  | possible_keys                       | key                          | key_len | ref  | rows    | Extra       |
+------+--------------------+---------------------------+-------+-------------------------------------+------------------------------+---------+------+---------+-------------+
|    1 | PRIMARY            | mdl_course                | index | NULL                                | PRIMARY                      | 8       | NULL |   13078 | Using where |
|    3 | DEPENDENT SUBQUERY | mdl_logstore_standard_log | ref   | mdl_logsstanlog_couanotim_ix        | mdl_logsstanlog_couanotim_ix | 9       | func |     553 | Using where |
|    2 | DEPENDENT SUBQUERY | mdl_log                   | ref   | mdl_log_coumodact_ix,mdl_log_tim_ix | mdl_log_coumodact_ix         | 8       | func | 1169589 | Using where |
+------+--------------------+---------------------------+-------+-------------------------------------+------------------------------+---------+------+---------+-------------+

merci

**totatota** · 26/10/2015, 13h40

mysql> select count(mdl_logstore_standard_log.courseid) from mdl_logstore_standard_log where mdl_logstore_standard_log.origin='web';
+-------------------------------------------+
| count(mdl_logstore_standard_log.courseid) |
+-------------------------------------------+
| 5742252 |
+-------------------------------------------+
1 row in set (6.81 sec)

mysql> select count(mdl_log.course) from mdl_log WHERE mdl_log.time BETWEEN UNIX_TIMESTAMP('2014-09-01' ) AND UNIX_TIMESTAMP('2015-07-01');
+-----------------------+
| count(mdl_log.course) |
+-----------------------+
| 20467701 |
+-----------------------+
1 row in set (15.34 sec)

mysql> SELECT mdl_course.id FROM mdl_course WHERE mdl_course.visible = 1 GROUP BY mdl_course.id ;
8710 rows in set (0.01 sec)

**punkoff** · 26/10/2015, 15h49

Bonjour,

Avec votre méthode, MySql va exécuter autant de fois les deux sous requetes qu'il y a de ligne dans la table mdl_course.

Ca aurait été une bonne approche si la condition mdl_course.visible = 1 était très restrictive.

Là ça n'est pas le cas.

La première optimisation à tester est de ramener vos deux requêtes corrélé dans le FROM.

Vous aurez une structure de la sorte :

Code :

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

1
2
3
4
 
select ...
From table_a a
inner join (select id_a, count(*) from table_b group by id_a) b on a.id = b.id_a

Ensuite il faut bien vérifier que vous avez des index sur vos tables.

En particulier :
mdl_logstore_standard_log (origin, courseid)
mdl_log (time, course)

**Artemus24** · 26/10/2015, 17h58

Salut totatota.

Combien de temps dure votre requête ?

Vous devez utiliser les jointures pour optimiser votre requête.
Vous devez mettre un index sur 'mdl_course.visible'.

Créer des clefs étrangères entre vos tables 'mdl_log' et 'mdl_logstore_standard_log' et votre table 'mdl_course'.

Voici ce que je propose. Je ne l'ai pas testé.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select tb1.id, count(tb2.id) as old, count(tb3.id) as new
 
from       mdl_course                as tb1
inner join mdl_log                   as tb2
on         tb2.course   = tb1.id
 
inner join mdl_logstore_standard_log as tb3
on         tb3.courseid = tb1.id
 
WHERE  tb1.visible = 1
GROUP  tb1.id
having (OLD+NEW) > 10;

Si vous désirez que je fasse des tests d'optimisation, il me faut un fichier 'text' contenant toutes vos tables, et toutes vos insertions qui sont en relation avec votre requête.
Pour procéder à cet export, vous devez passer par phpmyadmin.

Mon adresse email est 'artemus@jcz.fr'.

@+

**totatota** · 28/10/2015, 10h28

Bonjour ,

Pour la durée, c'est 18 minutes

Pour les index , oui il y a des index
J'ai testé la requête d'artemus, elle n'est pas plus rapide.
La bonne nouvelle c'est qu'on m'a demandé de ne plus faire cette requête, ils ne veulent plus croiser les données des deux tables de logs

Merci à vous

**punkoff** · 28/10/2015, 12h09

La requête d'artemus n'est pas bonne.

Le résultat va être une multiplication entre le nombre de ligne dans la 1ere table de log et la 2eme table de log.

**Artemus24** · 29/10/2015, 00h00

Salut punkoff.

Envoyé par punkoff

La requête d'artemus n'est pas bonne.

Le résultat va être une multiplication entre le nombre de ligne dans la 1ere table de log et la 2eme table de log.

Voilà ce qu'il ne faut pas faire : donner une solution alors qu'elle n'a pas été testée au préalable.
En effet, j'ai testé ma précédente solution, et tu as raison, punkoff, le résultat est faux !

Pour une valeur données de 'id' de la table 'mdl_course', je vais me retrouver avec la même valeur pour 'old' que pour 'new'.
Bien sûr, si on change de valeur de 'id', on n'aura pas le même résultat.
Et ce résultat correspond bien à la multiplication du nombre de lignes de la première table avec la seconde table.

Tu aurais pu donner la solution, punkoff, non ?

J'ai cette fois-ci testé la requête. J'ai décomposé mon test afin de montrer que l'on retrouve le résultat attendu. Il n'y a que trois index à créer :

1) un index sur la colonne 'visible' de la table 'mdl_course'.

2) un index de type 'foreign key' entre la table 'mdl_log' et la table père : 'mdl_course'.

3) un index de type 'foreign key' entre la table 'mdl_logstore_standard_log' et la table père : 'mdl_course'.

4) je ne suis pas allez plus loin dans la performance de la requête. Cela devrait répondre au problème de totatota.

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
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
--------------
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 `mdl_course`
--------------
 
--------------
CREATE TABLE `mdl_course`
(
  `id`       integer unsigned NOT NULL auto_increment,
  `lib`      char(05)         NOT NULL,
  `visible`  tinyint unsigned NOT NULL,
  INDEX `idx` (`visible`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO mdl_course (`lib`,`visible`) VALUES
('un', 1),('deux', 1), ('trois', 0)
--------------
 
--------------
select * from mdl_course
--------------
 
+----+-------+---------+
| id | lib   | visible |
+----+-------+---------+
|  1 | un    |       1 |
|  2 | deux  |       1 |
|  3 | trois |       0 |
+----+-------+---------+
--------------
DROP TABLE IF EXISTS `mdl_log`
--------------
 
--------------
CREATE TABLE `mdl_log`
(
  `id`       integer unsigned NOT NULL auto_increment,
  `course`   integer unsigned NOT NULL,
  CONSTRAINT `FK_1` FOREIGN KEY (`course`) REFERENCES `mdl_course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO mdl_log (`course`) VALUES
(1),(1),(1),
(2),(2),(2),(2),(2),
(3),(3),(3),(3),(3),(3)
--------------
 
--------------
select * from mdl_log
--------------
 
+----+--------+
| id | course |
+----+--------+
|  1 |      1 |
|  2 |      1 |
|  3 |      1 |
|  4 |      2 |
|  5 |      2 |
|  6 |      2 |
|  7 |      2 |
|  8 |      2 |
|  9 |      3 |
| 10 |      3 |
| 11 |      3 |
| 12 |      3 |
| 13 |      3 |
| 14 |      3 |
+----+--------+
--------------
SELECT tb1.id            AS 'id',
       COUNT(tb2.course) AS 'tot'
FROM       mdl_course    AS tb1
INNER JOIN mdl_log       AS tb2
ON         tb2.course  = tb1.id
GROUP BY   tb1.id
--------------
 
+----+-----+
| id | tot |
+----+-----+
|  1 |   3 |
|  2 |   5 |
|  3 |   6 |
+----+-----+
--------------
DROP TABLE IF EXISTS `mdl_logstore_standard_log`
--------------
 
--------------
CREATE TABLE `mdl_logstore_standard_log`
(
  `id`        integer unsigned NOT NULL auto_increment,
  `courseid`  integer unsigned NOT NULL,
  CONSTRAINT `FK_2` FOREIGN KEY (`courseid`) REFERENCES `mdl_course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO mdl_logstore_standard_log (`courseid`) VALUES
(1),(1),(1),(1),(1),(1),
(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),
(3),(3),(3),(3)
--------------
 
--------------
select * from mdl_logstore_standard_log
--------------
 
+----+----------+
| id | courseid |
+----+----------+
|  1 |        1 |
|  2 |        1 |
|  3 |        1 |
|  4 |        1 |
|  5 |        1 |
|  6 |        1 |
|  7 |        2 |
|  8 |        2 |
|  9 |        2 |
| 10 |        2 |
| 11 |        2 |
| 12 |        2 |
| 13 |        2 |
| 14 |        2 |
| 15 |        2 |
| 16 |        2 |
| 17 |        3 |
| 18 |        3 |
| 19 |        3 |
| 20 |        3 |
+----+----------+
--------------
SELECT tb1.id              AS 'id',
       COUNT(tb3.courseid) AS 'tot'
FROM       mdl_course                AS tb1
INNER JOIN mdl_logstore_standard_log AS tb3
ON         tb3.courseid = tb1.id
GROUP BY   tb1.id
--------------
 
+----+-----+
| id | tot |
+----+-----+
|  1 |   6 |
|  2 |  10 |
|  3 |   4 |
+----+-----+
--------------
explain
SELECT tb1.id            AS 'id',
       COUNT(tb2.course) AS 'tot'
 
FROM       mdl_course    AS tb1
INNER JOIN mdl_log       AS tb2
ON         tb2.course  = tb1.id
WHERE      tb1.visible = 1
GROUP BY   tb1.id
 
UNION
 
SELECT tb1.id              AS 'id',
       COUNT(tb3.courseid) AS 'tot'
 
FROM       mdl_course                AS tb1
INNER JOIN mdl_logstore_standard_log AS tb3
ON         tb3.courseid = tb1.id
WHERE      tb1.visible  = 1
GROUP BY   tb1.id
--------------
 
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+--------------------------+
|  id  | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                    |
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+--------------------------+
|   1  | PRIMARY      | tb1        | NULL       | ref  | PRIMARY,idx   | idx  | 1       | const       |    2 |   100.00 | Using where; Using index |
|   1  | PRIMARY      | tb2        | NULL       | ref  | FK_1          | FK_1 | 4       | base.tb1.id |    1 |   100.00 | Using index              |
|   2  | UNION        | tb1        | NULL       | ref  | PRIMARY,idx   | idx  | 1       | const       |    2 |   100.00 | Using where; Using index |
|   2  | UNION        | tb3        | NULL       | ref  | FK_2          | FK_2 | 4       | base.tb1.id |    1 |   100.00 | Using index              |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL        | NULL |     NULL | Using temporary          |
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+--------------------------+
--------------
SELECT tb1.id            AS 'id',
       COUNT(tb2.course) AS 'tot'
 
FROM       mdl_course    AS tb1
INNER JOIN mdl_log       AS tb2
ON         tb2.course  = tb1.id
WHERE      tb1.visible = 1
GROUP BY   tb1.id
 
UNION
 
SELECT tb1.id              AS 'id',
       COUNT(tb3.courseid) AS 'tot'
 
FROM       mdl_course                AS tb1
INNER JOIN mdl_logstore_standard_log AS tb3
ON         tb3.courseid = tb1.id
WHERE      tb1.visible  = 1
GROUP BY   tb1.id
--------------
 
+----+-----+
| id | tot |
+----+-----+
|  1 |   3 |
|  2 |   5 |
|  1 |   6 |
|  2 |  10 |
+----+-----+
--------------
explain
SELECT id, SUM(tot) AS 'tot'
FROM (
    SELECT tb1.id            AS 'id',
           COUNT(tb2.course) AS 'tot'
 
    FROM       mdl_course    AS tb1
    INNER JOIN mdl_log       AS tb2
    ON         tb2.course   = tb1.id
    WHERE      tb1.visible = 1
    GROUP BY   tb1.id
 
    UNION
 
    SELECT tb1.id              AS 'id',
           COUNT(tb3.courseid) AS 'tot'
 
    FROM       mdl_course                AS tb1
    INNER JOIN mdl_logstore_standard_log AS tb3
    ON        tb3.courseid = tb1.id
    WHERE     tb1.visible = 1
    GROUP BY  tb1.id
) AS x
GROUP BY id
HAVING tot > 10
ORDER BY id
--------------
 
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
|  id  | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra                           |
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
|   1  | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL        |    4 |   100.00 | Using temporary; Using filesort |
|   2  | DERIVED      | tb1        | NULL       | ref  | PRIMARY,idx   | idx  | 1       | const       |    2 |   100.00 | Using where; Using index        |
|   2  | DERIVED      | tb2        | NULL       | ref  | FK_1          | FK_1 | 4       | base.tb1.id |    1 |   100.00 | Using index                     |
|   3  | UNION        | tb1        | NULL       | ref  | PRIMARY,idx   | idx  | 1       | const       |    2 |   100.00 | Using where; Using index        |
|   3  | UNION        | tb3        | NULL       | ref  | FK_2          | FK_2 | 4       | base.tb1.id |    1 |   100.00 | Using index                     |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL        | NULL |     NULL | Using temporary                 |
+------+--------------+------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+
--------------
SELECT id, SUM(tot) AS 'tot'
FROM (
    SELECT tb1.id            AS 'id',
           COUNT(tb2.course) AS 'tot'
 
    FROM       mdl_course    AS tb1
    INNER JOIN mdl_log       AS tb2
    ON         tb2.course   = tb1.id
    WHERE      tb1.visible = 1
    GROUP BY   tb1.id
 
    UNION
 
    SELECT tb1.id              AS 'id',
           COUNT(tb3.courseid) AS 'tot'
 
    FROM       mdl_course                AS tb1
    INNER JOIN mdl_logstore_standard_log AS tb3
    ON        tb3.courseid = tb1.id
    WHERE     tb1.visible = 1
    GROUP BY  tb1.id
) AS x
GROUP BY id
HAVING tot > 10
ORDER BY id
--------------
 
+----+------+
| id | tot  |
+----+------+
|  2 |   15 |
+----+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

Optimiser requête avec grosses tables

Requêtes MySQL

Discussions similaires

Partager

Partager