Travail sur l'optimisation des vues Mysql

Optimisation DEL & CEL

SELECT SQL_CALC_FOUND_ROWS do.id_observation AS id_observation, do.nom_referentiel, nom_ret, nom_ret_nn, nt, famille, ce_zone_geo, zone_geo, lieudit, station, milieu, date_observation, 
	do.mots_cles_texte AS mots_cles_texte, date_transmission, 
	di.id_image AS id_image, di.commentaire AS commentaire, di.mots_cles_texte AS mots_cles_texte_image, 
	di.ce_utilisateur AS ce_utilisateur, di.nom_utilisateur, di.prenom_utilisateur, di.courriel_utilisateur, nom_original, 
	prenom, nom, courriel, 
	ce_protocole, moyenne, nb_votes, nb_tags 
FROM del_image AS di 
	LEFT JOIN del_obs_image AS doi ON (di.id_image = doi.id_image) 
	LEFT JOIN del_observation AS do ON (doi.id_observation = do.id_observation) 
	LEFT JOIN del_utilisateur AS du ON du.id_utilisateur = IF(LENGTH(di.ce_utilisateur) > 5, 0, di.ce_utilisateur) 
	LEFT JOIN del_image_stat AS ds ON (di.id_image = ds.ce_image) 
WHERE ds.ce_protocole = '3'  AND moyenne >= '4' 
ORDER BY moyenne 
LIMIT 0, 10

Doit on utiliser SQL_CALC_FOUND_ROWS ou COUNT(*) ? => SQL_CALC_FOUND_ROWS

SELECT COUNT(*) 
FROM del_image AS di 
	LEFT JOIN del_image_stat AS ds ON (di.id_image = ds.ce_image) 
WHERE ds.ce_protocole = '3' AND ds.moyenne >= '4';

• SQL_CALC_FOUND_ROWS : 1.6 s
• COUNT(*) : 1.6 s (requete) + 1 s (count) = 2.6 s

Doit on supprimer GROUP BY ? => oui

• avec GROUP BY : 32317 résultats => 1.68 s
• sans GROUP BY : 32317 résultats => 1.67 s

Problème restant

Optimisation des tables du CEL pour accélérer les vues

• remplacer le champ eflore_publiable de la table cel_images par un champ transmission (NULL par défaut). Et indiquer les images "transmisses" quand l'observation correspondante à l'image est rendue public (= transmise).
• supprimer la possibilité de lier plusieurs observations à une même image. Ce qui implique la suppression de la table cel_obs_image et la création d'un champ "ce_observation" (juste après id_image) dans la table cel_images. Il contiendra l'id de l'observation principale seulement liée à l'image.
  • Fait poussant à supprimer cette liaison :
    • Actuellement (au 04-09-2014) les votes dans PictoFlora se font sur une image et non sur le couple id_image+id_observation. Donc, les images liées à plusieurs observations aboutissent à des erreurs d'affichage des votes...
    • Aucun widget, flux RSS ou appli ne prend en compte cette possibilité de liaison d'une image à plusieurs observations.
    • Le nombre d'images liées à plusieurs observations est de 99 sur 199 000 (au 04-09-2014)
    • Une alternative simple est d'uploader plusieurs fois la même image pour la liée à plusieurs observations (le champ contenant la somme md5 du fichier image permettra de retrouver ces images).

• Actuellement, avec cel_obs_image + transmission dans table cel_observations => 2.4 secondes
• Si les deux optimiastions sont réalisées, avec ce_observation dans cel_images + transmission dans cel_images => 1.67 secondes

SELECT `i`.`id_image` AS `id_image`, 
`i`.`ce_observation` AS `ce_observation`, 
if((char_length(`i`.`ce_utilisateur`) > 5),0,`i`.`ce_utilisateur`) 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` 
from `tb_cel`.`cel_images` `i` 
where (`i`.`transmission` = '1')

Optimisation de la partition où sont écrits les fichiers et tables temporaires