| New file |
|---|
| 0,0 → 1,106 |
|---|
| 1) accélérer le SELECT + ORDER BY sur del_observation |
| |
| C'est un aspect important de la requête car il représente près de 33% du temps. |
| En local, près de 50ms sur 1.5s. |
| Solution: |
| |
| CREATE INDEX `date_transmission` ON `cel_obs` (`date_transmission` DESC) COMMENT "nécessaire à l'ORDER BY utilisé dans la liste d'observation de DEL"; |
| |
| Malheureusement l'INDEX ne semble pas utilisable par la view (http://bugs.mysql.com/bug.php?id=43341 ?): |
| EXPLAIN SELECT SQL_CALC_FOUND_ROWS id_observation FROM del_observation ORDER BY date_transmission desc LIMIT 0, 12; |
| # Using filesort (0.46s) |
| |
| Allons donc invoquer directement la table, cependant, encore une fois l'INDEX n'est pas utilisé |
| EXPLAIN SELECT SQL_CALC_FOUND_ROWS id_observation FROM tb_cel.cel_obs ORDER BY date_transmission desc LIMIT 0, 12; |
| # Using filesort (0.46s) |
| |
| Nous pouvons forcer l'index: |
| 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; |
| # key=date_transmission (0.0s) |
| parfait ! |
| |
| |
| 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 |
| qui, en effet, ralenti la requête (2.38s): |
| 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; |
| # Using where; Using filesort (0.60s) |
| # Using where; (key=date_transmission) (2.38s) [avec FORCE INDEX] |
| 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. |
| |
| Le processing de transmission = 1 est très (très) couteux et c'est lui qui s'avère bloquant. |
| |
| |
| Tentons l'ajout d'un INDEX sur transmission ,tout TINYINT soit-il: |
| CREATE INDEX `transmission` ON `tb_cel`.`cel_obs` (`transmission`) COMMENT "nécessaire à CEL/DEL qui officie avec transmission = 1" |
| ANALYZE TABLE tb_cel.cel_obs; |
| EXPLAIN SELECT id_observation FROM tb_cel.cel_obs WHERE transmission = 1 ORDER BY date_transmission desc LIMIT 0, 12; |
| # key = date_transmission, rows = 17, type = index (0.0s) |
| parfait... |
| et idem pour: |
| EXPLAIN SELECT * FROM del_observation ORDER BY date_transmission desc LIMIT 0, 12; |
| La vue prend donc bien en compte nos 2 INDEX. |
| |
| MAIS... ... |
| EXPLAIN SELECT SQL_CALC_FOUND_ROWS id_observation FROM tb_cel.cel_obs WHERE transmission = 1 ORDER BY date_transmission desc LIMIT 0, 12; |
| # Using where; Using filesort, key = date_transmission, rows = 291265, type = index (0.80s) |
| ... à peine retrouvons-nous SQL_CALC_FOUND_ROWS() que les perf' sont à nouveau déplorable puis MySQL itère sur les 300k lignes à transmission=1 |
| |
| Cependant: |
| EXPLAIN SELECT count(1) FROM del_observation; |
| # key = transmission, 291265 = 17, type = ref, MAIS... "Using index" (0.10s) |
| |
| |
| |
| Ainsi pour une requête initiale: |
| 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 |
| de 1.52s, la suppression SQL_CALC_FOUND_ROWS() nous amène à 0.00s + 0.10s de count() additionnel. |
| |
| Sans INDEX sur transmission, la requête est de même rapidité, mais le count(1), trois fois plus long (0.30s) |
| Sans INDEX sur date_transmission, la requête est 4 fois plus longue (0.40s) et le count(1) identique (0.35s) |
| Bref, les 2 INDEX sont intéressant même si l'idéal est de rendre optionnel l'affichage du total des résultats |
| |
| |
| |
| |
| 2) accélérer le GROUP en requête complexe: |
| 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 |
| |
| |
| 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; |
| +------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+ |
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
| +------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+ |
| | 1 | SIMPLE | doi | index | PRIMARY,observation,image | PRIMARY | 16 | NULL | 10314 | Using index; Using temporary; Using filesort | |
| | 1 | SIMPLE | di | eq_ref | PRIMARY,id_image | PRIMARY | 8 | tb_cel.doi.id_image | 1 | Using index | |
| | 1 | SIMPLE | dob | eq_ref | PRIMARY,transmission | PRIMARY | 8 | tb_cel.doi.id_observation | 1 | Using where | |
| +------+-------------+-------+--------+---------------------------+---------+---------+---------------------------+-------+----------------------------------------------+ |
| # 0.04s |
| |
| 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; |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| | 1 | SIMPLE | oi | index | PRIMARY,observation,image | PRIMARY | 16 | NULL | 10314 | Using index; Using temporary; Using filesort | |
| | 1 | SIMPLE | oi | ref | PRIMARY,observation,image | image | 8 | tb_cel.oi.id_image | 1 | | |
| | 1 | SIMPLE | i | eq_ref | PRIMARY,id_image | PRIMARY | 8 | tb_cel.oi.id_image | 1 | Using index | |
| | 1 | SIMPLE | o | eq_ref | PRIMARY,transmission | PRIMARY | 8 | tb_cel.oi.id_observation | 1 | Using where | |
| | 1 | SIMPLE | o | eq_ref | PRIMARY,transmission | PRIMARY | 8 | tb_cel.oi.id_observation | 1 | Using where | |
| | 1 | SIMPLE | o | eq_ref | PRIMARY,transmission | PRIMARY | 8 | tb_cel.oi.id_observation | 1 | Using where | |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| # 1.67s |
| |
| |
| En fait, del_image se définit comme suit: |
| 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') |
| |
| soit déjà une double-jointure. |
| |
| EXPLAIN SELECT id_observation, id_image FROM del_image AS di GROUP BY id_observation ORDER BY date_transmission desc LIMIT 0, 12; |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| | 1 | SIMPLE | oi | index | PRIMARY,observation,image | PRIMARY | 16 | NULL | 10314 | Using index; Using temporary; Using filesort | |
| | 1 | SIMPLE | i | eq_ref | PRIMARY,id_image | PRIMARY | 8 | tb_cel.oi.id_image | 1 | Using index | |
| | 1 | SIMPLE | o | eq_ref | PRIMARY,transmission | PRIMARY | 8 | tb_cel.oi.id_observation | 1 | Using where | |
| +------+-------------+-------+--------+---------------------------+---------+---------+--------------------------+-------+----------------------------------------------+ |
| # 0.04s |