Subversion Repositories eFlore/Applications.del

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
1375 raphael 1
1) accélérer le SELECT + ORDER BY sur del_observation
2
 
3
C'est un aspect important de la requête car il représente près de 33% du temps.
4
En local, près de 50ms sur 1.5s.
5
Solution:
6
 
7
CREATE INDEX `date_transmission` ON `cel_obs` (`date_transmission` DESC) COMMENT "nécessaire à l'ORDER BY utilisé dans la liste d'observation de DEL";
8
 
9
Malheureusement l'INDEX ne semble pas utilisable par la view (http://bugs.mysql.com/bug.php?id=43341 ?):
10
EXPLAIN SELECT SQL_CALC_FOUND_ROWS  id_observation  FROM del_observation ORDER BY date_transmission desc LIMIT 0, 12;
11
# Using filesort (0.46s)
12
 
13
Allons donc invoquer directement la table, cependant, encore une fois l'INDEX n'est pas utilisé
14
EXPLAIN SELECT SQL_CALC_FOUND_ROWS  id_observation  FROM tb_cel.cel_obs ORDER BY date_transmission desc LIMIT 0, 12;
15
# Using filesort (0.46s)
16
 
17
Nous pouvons forcer l'index:
18
EXPLAIN SELECT SQL_CALC_FOUND_ROWS  id_observation  FROM tb_cel.cel_obs FORCE INDEX FOR ORDER BY (`date_transmission`) ORDER BY date_transmission desc LIMIT 0, 12;
19
# key=date_transmission (0.0s)
20
parfait !
21
 
22
 
23
Cependant, à peine ajoutons-nous le WHERE transmission = 1 que nous remontons à 0.60 seconde, et MySQL nous déconseille même l'utilisation de l'INDEX
24
qui, en effet, ralenti la requête (2.38s):
25
EXPLAIN SELECT SQL_CALC_FOUND_ROWS  id_observation  FROM tb_cel.cel_obs FORCE INDEX FOR ORDER BY (`date_transmission`) WHERE transmission = 1 ORDER BY date_transmission desc LIMIT 0, 12;
26
# Using where; Using filesort (0.60s)
27
# Using where; (key=date_transmission) (2.38s) [avec FORCE INDEX]
28
Dans tous les cas ci-dessus, "rows" indique "291265", et le type "ref", sauf en "FORCE INDEX", mais comme nous l'avons vu, cela sous-optimal.
29
 
30
Le processing de transmission = 1 est très (très) couteux et c'est lui qui s'avère bloquant.
31
 
32
 
33
Tentons l'ajout d'un INDEX sur transmission ,tout TINYINT soit-il:
34
CREATE INDEX `transmission` ON `tb_cel`.`cel_obs` (`transmission`) COMMENT "nécessaire à CEL/DEL qui officie avec transmission = 1"
35
ANALYZE TABLE tb_cel.cel_obs;
36
EXPLAIN SELECT id_observation FROM tb_cel.cel_obs WHERE transmission = 1 ORDER BY date_transmission desc LIMIT 0, 12;
37
# key = date_transmission, rows = 17, type = index (0.0s)
38
parfait...
39
et idem pour:
40
EXPLAIN SELECT * FROM del_observation ORDER BY date_transmission desc LIMIT 0, 12;
41
La vue prend donc bien en compte nos 2 INDEX.
42
 
43
MAIS... ...
44
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id_observation FROM tb_cel.cel_obs WHERE transmission = 1 ORDER BY date_transmission desc LIMIT 0, 12;
45
# Using where; Using filesort, key = date_transmission, rows = 291265, type = index (0.80s)
46
... à peine retrouvons-nous SQL_CALC_FOUND_ROWS() que les perf' sont à nouveau déplorable puis MySQL itère sur les 300k lignes à transmission=1
47
 
48
Cependant:
49
EXPLAIN SELECT count(1) FROM del_observation;
50
# key = transmission, 291265 = 17, type = ref, MAIS... "Using index" (0.10s)
51
 
52
 
53
 
54
Ainsi pour une requête initiale:
55
SELECT SQL_CALC_FOUND_ROWS  dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit,  station, milieu, date_observation, dob.mots_cles_texte, date_transmission,  dob.ce_utilisateur, prenom, nom, courriel,  dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur,  dob.commentaire as dob_commentaire,  dob.nt, dob.nom_sel_nn FROM del_observation AS dob  LEFT JOIN del_utilisateur AS du ON dob.ce_utilisateur = du.id_utilisateur  ORDER BY date_transmission desc  LIMIT 0, 12
56
de 1.52s, la suppression SQL_CALC_FOUND_ROWS() nous amène à 0.00s + 0.10s de count() additionnel.
57
 
58
Sans INDEX sur transmission, la requête est de même rapidité, mais le count(1), trois fois plus long (0.30s)
59
Sans INDEX sur date_transmission, la requête est 4 fois plus longue (0.40s) et le count(1) identique (0.35s)
60
Bref, les 2 INDEX sont intéressant même si l'idéal est de rendre optionnel l'affichage du total des résultats
61
 
62
 
63
 
64
 
65
2) accélérer le GROUP en requête complexe:
66
SELECT SQL_CALC_FOUND_ROWS  dob.id_observation, nom_sel, famille, ce_zone_geo, zone_geo, lieudit,  station, milieu, date_observation, dob.mots_cles_texte, date_transmission, di.id_image,  dob.ce_utilisateur, prenom, nom, courriel,  dob.prenom_utilisateur, dob.nom_utilisateur, dob.courriel_utilisateur,  nom_original, dob.commentaire AS dob_commentaire,  dob.nt, dob.nom_sel_nn FROM del_observation AS dob  LEFT JOIN del_utilisateur AS du   ON du.id_utilisateur = dob.ce_utilisateur  LEFT JOIN del_obs_image AS doi   ON doi.id_observation = dob.id_observation  LEFT JOIN del_image AS di   ON di.id_image = doi.id_image  WHERE 1 GROUP BY doi.id_observation ORDER BY date_transmission desc  LIMIT 0, 12
67
 
68
 
69
EXPLAIN SELECT dob.id_observation, di.id_image FROM cel_obs AS dob, cel_obs_images AS doi, cel_images AS di WHERE (transmission = 1 AND doi.id_observation = dob.id_observation AND di.id_image = doi.id_image) GROUP BY dob.id_observation ORDER BY date_transmission desc  LIMIT 0, 12;
70
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
71
| id   | select_type | table | type   | possible_keys             | key     | key_len | ref                       | rows  | Extra                                        |
72
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
73
|    1 | SIMPLE      | doi   | index  | PRIMARY,observation,image | PRIMARY | 16      | NULL                      | 10314 | Using index; Using temporary; Using filesort |
74
|    1 | SIMPLE      | di    | eq_ref | PRIMARY,id_image          | PRIMARY | 8       | tb_cel.doi.id_image       |     1 | Using index                                  |
75
|    1 | SIMPLE      | dob   | eq_ref | PRIMARY,transmission      | PRIMARY | 8       | tb_cel.doi.id_observation |     1 | Using where                                  |
76
+------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
77
# 0.04s
78
 
79
EXPLAIN SELECT dob.id_observation, di.id_image FROM del_observation AS dob, del_obs_image AS doi, del_image AS di WHERE (doi.id_observation = dob.id_observation AND di.id_image = doi.id_image) GROUP BY dob.id_observation ORDER BY date_transmission desc  LIMIT 0, 12;
80
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
81
| id   | select_type | table | type   | possible_keys             | key     | key_len | ref                      | rows  | Extra                                        |
82
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
83
|    1 | SIMPLE      | oi    | index  | PRIMARY,observation,image | PRIMARY | 16      | NULL                     | 10314 | Using index; Using temporary; Using filesort |
84
|    1 | SIMPLE      | oi    | ref    | PRIMARY,observation,image | image   | 8       | tb_cel.oi.id_image       |     1 |                                              |
85
|    1 | SIMPLE      | i     | eq_ref | PRIMARY,id_image          | PRIMARY | 8       | tb_cel.oi.id_image       |     1 | Using index                                  |
86
|    1 | SIMPLE      | o     | eq_ref | PRIMARY,transmission      | PRIMARY | 8       | tb_cel.oi.id_observation |     1 | Using where                                  |
87
|    1 | SIMPLE      | o     | eq_ref | PRIMARY,transmission      | PRIMARY | 8       | tb_cel.oi.id_observation |     1 | Using where                                  |
88
|    1 | SIMPLE      | o     | eq_ref | PRIMARY,transmission      | PRIMARY | 8       | tb_cel.oi.id_observation |     1 | Using where                                  |
89
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
90
# 1.67s
91
 
92
 
93
En fait, del_image se définit comme suit:
94
select `i`.`id_image` AS `id_image`,if((char_length(`i`.`ce_utilisateur`) <> 32),cast(`i`.`ce_utilisateur` as unsigned),0) AS `ce_utilisateur`,`i`.`prenom_utilisateur` AS `prenom_utilisateur`,`i`.`nom_utilisateur` AS `nom_utilisateur`,`i`.`courriel_utilisateur` AS `courriel_utilisateur`,`i`.`hauteur` AS `hauteur`,`i`.`largeur` AS `largeur`,`i`.`date_prise_de_vue` AS `date_prise_de_vue`,`i`.`mots_cles_texte` AS `mots_cles_texte`,`i`.`commentaire` AS `commentaire`,`i`.`nom_original` AS `nom_original`,`i`.`date_modification` AS `date_modification`,`i`.`date_creation` AS `date_creation`,`i`.`publiable_eflore` AS `publiable_eflore` from ((`tb_cel`.`cel_images` `i` join `tb_cel`.`cel_obs_images` `oi` on((`i`.`id_image` = `oi`.`id_image`))) join `tb_cel`.`cel_obs` `o` on((`oi`.`id_observation` = `o`.`id_observation`))) where (`o`.`transmission` = '1')
95
 
96
soit déjà une double-jointure.
97
 
98
EXPLAIN SELECT id_observation, id_image FROM del_image AS di GROUP BY id_observation ORDER BY date_transmission desc  LIMIT 0, 12;
99
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
100
| id   | select_type | table | type   | possible_keys             | key     | key_len | ref                      | rows  | Extra                                        |
101
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
102
|    1 | SIMPLE      | oi    | index  | PRIMARY,observation,image | PRIMARY | 16      | NULL                     | 10314 | Using index; Using temporary; Using filesort |
103
|    1 | SIMPLE      | i     | eq_ref | PRIMARY,id_image          | PRIMARY | 8       | tb_cel.oi.id_image       |     1 | Using index                                  |
104
|    1 | SIMPLE      | o     | eq_ref | PRIMARY,transmission      | PRIMARY | 8       | tb_cel.oi.id_observation |     1 | Using where                                  |
105
+------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+
106
# 0.04s
107
 
108
 
109
CREATE OR REPLACE VIEW `del_image` AS select o.*, i.id_image, i.ordre AS i_ordre, i.ce_utilisateur AS i_ce_utilisateur, hauteur, largeur, date_prise_de_vue, i.mots_cles_texte AS i_mots_cles_texte, i.commentaire AS i_commentaire, nom_original, publiable_eflore  from ((`tb_cel`.`cel_images` `i` join `tb_cel`.`cel_obs_images` `oi` on((`i`.`id_image` = `oi`.`id_image`))) join `tb_cel`.`cel_obs` `o` on((`oi`.`id_observation` = `o`.`id_observation`))) where (`o`.`transmission` = '1');
110
 
111
CREATE OR REPLACE VIEW `del2_image` AS select o.*, i.id_image, i.ordre AS i_ordre, i.ce_utilisateur AS i_ce_utilisateur, i.prenom_utilisateur AS i_prenom_utilisateur, i.nom_utilisateur AS  i_nom_utilisateur, i.courriel_utilisateur AS  i_courriel_utilisateur, hauteur, largeur, date_prise_de_vue, i.mots_cles_texte AS i_mots_cles_texte, i.commentaire AS i_commentaire, nom_original, publiable_eflore from `tb_cel`.`cel_obs` `o` INNER JOIN `tb_cel`.`cel_obs_images` `oi` ON `oi`.`id_observation` = `o`.`id_observation` INNER JOIN `tb_cel`.`cel_images` `i` ON `i`.`id_image` = `oi`.`id_image` where `o`.`transmission` = '1';
112
 
113
 
114
# ajout o.nom_referentiel à la VIEW del_observations