Discussion :

[Sayrus]

Bonjour,

Je cherche à optimiser la requête suivante (car le multi-select est lent) et je me demandais si vous pourriez m'aider sur ce coup ?

Code :

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

SELECT c.*, s.lastname AS slast, s.firstname AS sfirst, u.lastname AS ulast, u.firstname AS ufirst, m.title AS model, cf.file, ch.date AS lastUpdate FROM tableA AS c LEFT JOIN tableB AS u ON u.id=c.ownerId LEFT JOIN tableB AS s ON c.salemanId=s.id LEFT JOIN tableC AS m ON m.id=c.modelId LEFT JOIN tableD AS cf ON cf.carId=c.id AND cf.type="photo" AND cf.ordering=0 LEFT JOIN tableE AS ch ON ch.carId=c.id INNER JOIN tableF AS cmo ON c.id=cmo.carId WHERE c.deleted=0 AND c.type=0 AND (cmo.offerPrice = (SELECT MAX(offerPrice) FROM tableF WHERE carId=cmo.carId)) AND cmo.viewed=0 AND cmo.offerPrice>0 GROUP BY c.id ORDER BY id DESC LIMIT 0, 20

La partie en rouge, permet au lieu de retourner une ligne pour chaque nouvelle offre reçue, de ne récupérer les offres QUE si la dernière offre postée non vue (via cmo.viewed=0) est supérieur à ce qui a déjà été vu avant.

Votre aide est la bienvenue!

Merci.

[Artemus24]

Salut Sayrus.

Faites au moins l'effort de présenter correctement votre requête sql.

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
SELECT c.*,
	   s.lastname  AS slast,
	   s.firstname AS sfirst,
	   u.lastname  AS ulast,
	   u.firstname AS ufirst,
	   m.title     AS model,
	   cf.file,
	   ch.date     AS lastUpdate

FROM tableA AS c

LEFT JOIN tableB AS u
ON u.id=c.ownerId

LEFT JOIN tableB AS s
ON c.salemanId=s.id

LEFT JOIN tableC AS m
ON m.id=c.modelId

LEFT JOIN tableD AS cf
ON  cf.carId=c.id
AND cf.type="photo"
AND cf.ordering=0

LEFT JOIN tableE AS ch
ON ch.carId=c.id

INNER JOIN tableF AS cmo
ON c.id=cmo.carId

WHERE c.deleted=0
AND c.type=0
AND (cmo.offerPrice = (
    SELECT MAX(offerPrice)
	FROM tableF
	WHERE carId=cmo.carId
	))
AND cmo.viewed=0
AND cmo.offerPrice>0
GROUP BY c.id
ORDER BY id DESC
LIMIT 0, 20;

Avez-vous créer des index sur les colonnes qui servent à faire vos jointure ?
--> u.id = c.ownerId
--> c.salemanId = s.id
--> m.id = c.modelId
--> cf.carId = c.id
--> cf.type = "photo"
--> cf.ordering = 0
--> c.id = cmo.carId
--> carId = cmo.carId
--> cmo.viewed = 0
--> cmo.offerPrice > 0

Dans la sous-requête (ce qui est en rouge), au lieu de mettre "WHERE carId=cmo.carId)", vous pourriez mettre "WHERE carId=c.id)".
Après tout, vous faites bien "ON c.id=cmo.carId". Je ne pense pas que cela va changer grand chose en terme de performance.

Hormis cela, si vous faites des jointures, vous devez faire en sorte de commencer par la ou les tables qui vont vous donner le plus petit résultat.
Les tables qui vous servent à rechercher des libelles ou des codes, viennent ensuite après avoir obtenu votre noyau dure.
Pour cela, vous pouvez décomposer votre requête en deux requêtes séparées en utilisant une table temporaire.
La première extraction, pour récupérer votre noyau dure et la seconde extraction, pour faire les liens vers vos libelles et codes.
Pour faire cela, on utilise le "with CTE" (common table expression) mais cela n'existe pas sous MySql.

Une sous-requête est toujours plus pénalisante que de faire une jointure, sous MySql.
Voici un exemple de transformation d'une sous-requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
select id,
       name,
       visiteur
 
from test as tb1
where visiteur = (	select max(visiteur)
			from test as tb2
			where tb2.name = tb1.name
		)
order by visiteur desc;

en jointure :

Code :

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

1
2
3
4
5
6
7
8
9
10
select tb1.id,
       tb1.name,
       tb1.visiteur
 
from test tb1
left outer join test tb2
on    tb2.name     = tb1.name
and   tb2.visiteur > tb1.visiteur
where tb2.id is null
order by tb1.visiteur desc;

Pour visualiser les chemins de votre grosse requête, utilisez l'explain.

Uniquement avec la requête, il nous est assez difficile de voir ce qui va ou ne va pas. Le descriptif des tables (DDL) serait le bienvenue !

@+

[Sayrus]

Bonjour,

Merci pour votre message.
Désolé pour le formation j'ai l'habitude de mettre toute ma requête sur une ligne mais effectivement cela peut-être plus facilement lisible comme vous l'avez présentée.

J'ai mis des index oui sur les id, mais pas sur tous (viewed, offerPrice, ...) par exemple.

Je vais tâcher de suivre l'exemple et d'adapter la requête en conséquence! Merci pour les précieux conseils en tout ças !

[escartefigue]

Bonjour,

quelques remarques :

Ne vous précipitez pas sur la création d'index, avant d'avoir vérifié le nombre de valeurs distinctes pour les colonnes de jointures
Créer des index sur des colonnes non discriminantes produit l'effet inverse de celui désiré : ça n'améliore en rien les recherches et ça pénalise les perfs en mise à jour !

Ne faites jamais de select *, ça nuit aux performances car vous transportez des colonnes inutiles, de plus, si votre table évolue, votre résultat évolue aussi !
Dans votre cas vu que vous faites une jointure entre la table C (celle pour laquelle vous faites un select *) et d'autres tables, vous transportez autant de fois les colonnes de jointures
Ca charge inutilement le réseau, au détriment aussi des autre utilisateurs.

De plus ce genre d'alias est particulièrement tordu :

Code :

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

1
2
3
4
FROM tableA AS  c
LEFT JOIN tableB AS  u ON u.id=c.ownerId

LEFT JOIN tableB AS s ON c.salemanId=s.id

Il faut penser à la lisibilité de la requête pour en faciliter la maintenance, remplacez vos alias, par exemple comme suit

Code :

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

1
2
3
4
5
6
7
FROM tableA AS A

LEFT JOIN tableB AS B1
  ON B1.id=A.ownerId

LEFT JOIN tableB AS B2
  ON A.salemanId=B2.id

Edit : autre remarque, je n'avais pas vu en première lecture, mais votre group by est inutile car il ne correspond pas à vos colonnes de votre select
MySQL est probablement le seul SGBD à accepter cette syntaxe normalement invalide.
Supprimez donc ce group by, votre requete deviendra standard (donc portable vers un autre SGBD)
D'une façon générale, ne mettez pas de group by inutiles car le group by génère des tris, qui plombent aussi les perfs (dans votre cas c'est différent puisqu'il est ignoré car invalide)