Aide pour Simplifier/optimiser une requête SQL

Version imprimable

Bonjour,
Mon développeur ne maîtrisant pas totalement SQL et ses subtilités, on aurait besoin d'un coup de pouce pour simplifier/optimiser la requete suivante :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT DISTINCT video_id, video_type, video_titre, video_description, video_idPartenaire, video_urlMini, video_dateValide, partenaire_valide, partenaire_redirection, ( SELECT COUNT(Y.v_appartient_c_idVideo) AS NbCorresp FROM lol_v_appartient_c Y WHERE Y.v_appartient_c_idVideo=81 AND Y.v_appartient_c_idCategorie IN (SELECT Z.v_appartient_c_idCategorie FROM lol_v_appartient_c Z WHERE Z.v_appartient_c_idVideo=video_id)) as Compte, ( SELECT categorie_singulier FROM lol_categorie,lol_v_appartient_c X WHERE X.v_appartient_c_idVideo=video_id AND X.v_appartient_c_default=1 AND categorie_id=X.v_appartient_c_idCategorie ) as categorie_singulier FROM lol_categorie,lol_v_appartient_c A,lol_video LEFT JOIN lol_partenaire ON video_idPartenaire=partenaire_id WHERE (A.v_appartient_c_idCategorie IN (SELECT W.v_appartient_c_idCategorie FROM lol_v_appartient_c W WHERE W.v_appartient_c_idVideo=81) AND video_id=A.v_appartient_c_idVideo) AND (video_idPartenaire=0 OR (partenaire_valide=1 AND partenaire_redirection<>1)) AND video_valide=1 AND video_id<>81 AND video_langue='fr' ORDER BY Compte DESC LIMIT 0,14
Cette requête affiche les 14 premières vidéos ayant le plus de catégories similaires avec "les catégories" de la vidéo visionnée (dans notre exemple la vidéo 81)
La requête actuelle met 3.06 secondes pour s’exécuter en parcourant 2700 vidéos. On trouve cela un peu long d'ou notre demande sur ce poste. PS: le serveur est plutôt puissant

Je remercie d'avance l’âme charitable qui tentera de nous aider :)

10/05/2012, 08h11
Maljuna Kris

Saluton,

Il faudrait, peut-être, commencer par envisager de changer de développeur ;).

Une description a minima des tables concernées et des colonnes sur lesquelles sont censées porter les jointures nous aiderait, ainsi que les index présents sur lesdites tables.

Le DISTINCT illustre assez bien le manque de maîtrise du codeur, ce limitateur de lignes agit en effet sur l'ensemble des colonnes du n-uplet du SELECT, je doute par conséquent de son utilité ici.

MySQL apprécie assez peu les sous-requêtes (en termes de performances).

Je ne prends pas grand risque en affirmant qu'effectivement tout cela est optimisable.

C'est une horreur cette requête ! :aie:
=> Envoyer le codeur en formation SQL ! Il peut déjà commencer par apprendre les jointures.

Le besoin :

Citation:

Cette requête affiche les 14 premières vidéos ayant le plus de catégories similaires avec "les catégories" de la vidéo visionnée (dans notre exemple la vidéo 81)

Décomposons...

1) Quelles sont les catégories de la vidéo 81 ?

Code:

1
2
3
SELECT v_appartient_c_idCategorie
FROM lol_v_appartient_c
WHERE v_appartient_c_idVideo = 81

2) Quelles sont les vidéos qui ont des catégories en commun avec la vidéo 81 et combien ont-elles de catégories en commun ?

Code:

1
2
3
4
5
6
7
8
9
10
SELECT a1.v_appartient_c_idVideo, 
	COUNT(a1.v_appartient_c_idCategorie) AS nb_categories_communes
FROM lol_v_appartient_c a1
INNER JOIN 
(
	SELECT v_appartient_c_idCategorie
	FROM lol_v_appartient_c
	WHERE v_appartient_c_idVideo = 81
) t1 ON t1.v_appartient_c_idCategorie = a1.v_appartient_c_idCategorie
GROUP BY a1.v_appartient_c_idVideo

3) On récupère les informations de ces vidéos

Code:

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
SELECT v.video_id, v.video_type, v.video_titre, v.video_description, 
	v.video_idPartenaire, v.video_urlMini, v.video_dateValide, 
	p.partenaire_valide, p.partenaire_redirection
FROM lol_video v
LEFT OUTER JOIN lol_partenaire p ON p.partenaire_id = v.video_idPartenaire
INNER JOIN
(
	SELECT a1.v_appartient_c_idVideo, 
		COUNT(a1.v_appartient_c_idCategorie) AS nb_categories_communes
	FROM lol_v_appartient_c a1
	INNER JOIN 
	(
		SELECT v_appartient_c_idCategorie
		FROM lol_v_appartient_c
		WHERE v_appartient_c_idVideo = 81
	) t1 ON t1.v_appartient_c_idCategorie = a1.v_appartient_c_idCategorie
	GROUP BY a1.v_appartient_c_idVideo
) t2 ON t2.v_appartient_c_idVideo = v.video_id
WHERE v.video_idPartenaire = 0 
	OR 
	(
		p.partenaire_valide = 1 
		AND p.partenaire_redirection <> 1 -- Ne serait-ce pas plutôt <> 0 ?
	)
	AND v.video_valide = 1 
	AND v.video_id <> 81 
	AND v.video_langue = 'fr'
ORDER BY t2.nb_categories_communes DESC
LIMIT 14

Bon j'ai eu une aide d'un dev extérieur et voici le résultat de la requête finale :

Code:

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
 
SELECT 
  lol_video.video_id,
  lol_video.video_type,
  lol_video.video_titre,
  lol_video.video_description,
  lol_video.video_idPartenaire,
  lol_video.video_urlMini,
  lol_video.video_dateValide,
  lol_partenaire.partenaire_valide,
  lol_partenaire.partenaire_redirection,
  ( SELECT COUNT(Y.v_appartient_c_idVideo) AS NbCorresp FROM  lol_v_appartient_c  Y
   WHERE Y.v_appartient_c_idVideo=81 AND Y.v_appartient_c_idCategorie IN (SELECT Z.v_appartient_c_idCategorie FROM lol_v_appartient_c Z WHERE Z.v_appartient_c_idVideo=lol_video.video_id)) as Compte,  
  lol_categorie.categorie_singulier
FROM
  lol_video
  LEFT JOIN lol_v_appartient_c ON (lol_video.video_id = lol_v_appartient_c.v_appartient_c_idVideo)
  LEFT JOIN lol_categorie ON (lol_v_appartient_c.v_appartient_c_idCategorie = lol_categorie.categorie_id)
  LEFT JOIN lol_partenaire ON (lol_video.video_idPartenaire = lol_partenaire.partenaire_id),
  lol_video lol_video_search
  LEFT JOIN lol_v_appartient_c lol_v_appartient_c_search ON (lol_video_search.video_id = lol_v_appartient_c_search.v_appartient_c_idVideo)
WHERE
  (lol_video.video_idPartenaire = 0 OR 
  lol_partenaire.partenaire_valide = 1 AND 
  lol_partenaire.partenaire_redirection <> 1) AND 
  lol_video.video_valide = 1 AND 
  lol_video_search.video_id = 81 AND 
  lol_video.video_id <> 81 AND 
  lol_video.video_langue = 'fr' AND 
  lol_v_appartient_c.v_appartient_c_default = 1
GROUP BY
  lol_video.video_id
   ORDER BY Compte DESC
LIMIT 14

Temps d’exécution passé à 0.78 secondes ^^
Je ne serrai pas vous dire ce qui a changé mais n'aimant pas laisser les sujets sans réponse/remerciement je poste quand même la requête finale :)

Merci en tout cas à ceux qui ont tenté d'aider c'est sympa.