Discussion :

[lemathou]

Bonjour,

J'ai un peu de mal à comprendre la logique du moteur de MariaDB (que je suppose similaire à MySQL) dans une requête... J'ai pourtant la nette impression que ma requête R3 devrait prendre grosso modo le temps de R1 + le temps de R2, et pourtant...

Ne tremblez pas devant les STRAIGHT_JOIN et autres FORCE_INDEX, c'est pour me simplifier la vie car MariaDB optimisait mal ma requête dans certaines conditions et cela m'a permit, jusqu'à peu, de conserver d'excellentes perfs. Bref !

Je précise que j'ai viré le query_cache, je vous donne les perfs moyennes.

R1 : effectuée en 1.27s

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
 
SELECT STRAIGHT_JOIN d.*, UNIX_TIMESTAMP(d.rappel_date) rappel_date_ts,
IF(dc.num_com, MD5(CONCAT(dc.num_com, 'commandematnat007')), NULL) c_hash, dc.num_com,
cl.mail, cl.nom, cl.prenom, if (cl.cp_livraison != '', cl.cp_livraison, cl.cp) AS cp, cl.cp_livraison, cl.societe,
u.nom AS nom_user, u.prenom AS prenom_user,
COUNT(co.datetime) as vue_nb, MAX(co.datetime) as vue_lastdate, MAX(UNIX_TIMESTAMP(co.datetime)) as vue_lastdate_ts,
MAX(co2.datetime) as relance_lastdate, MAX(UNIX_TIMESTAMP(co2.datetime)) as relance_lastdate_ts,
GROUP_CONCAT(DISTINCT dl.date_depart SEPARATOR ';') l_list,
GROUP_CONCAT(DISTINCT dl2.date_recpt SEPARATOR ';') l_list_2,
GROUP_CONCAT(DISTINCT dpt2.id_produit SEPARATOR ';') l_prod
 
FROM devis d
INNER JOIN client cl ON cl.id_client=d.id_client
INNER JOIN user u ON u.id_user=d.id_user
LEFT JOIN devis_commande dc ON dc.num_devis=d.id
LEFT JOIN devis_comment co FORCE INDEX FOR JOIN (id_devis) ON co.type='clic' AND co.id_devis=d.id
LEFT JOIN devis_comment comc FORCE INDEX FOR JOIN (id_devis) ON comc.type='clic' AND comc.id_devis=d.id
LEFT JOIN devis_comment co2 FORCE INDEX FOR JOIN (id_devis) ON (co2.tel IN ('repondeur', 'eu') OR co2.envoi_email=1) AND co2.id_devis=d.id
LEFT JOIN contenu_devis dpt2 FORCE INDEX FOR JOIN (id_devis) ON dpt2.id_devis=d.id
LEFT JOIN devis_livraison dl FORCE INDEX FOR JOIN (devis_id) ON dl.devis_id=d.id
LEFT JOIN devis_livraison dl2 FORCE INDEX FOR JOIN (devis_id) ON dl2.devis_id=d.id AND (dl2.date_depart IS NULL OR dl2.date_depart='0000-00-00')
 
WHERE d.id IN (35087, 35110, 35110, 34736, 34923, 35059, 25573, 34066, 34362, 35112, 34801, 34997, 35081, 35087, 35110, 35076, 34371, 34893, 34981, 35011, 35078, 33803, 33803, 35129, 35129, 35082, 35132, 34473, 34173, 35103, 35134, 35134, 34187, 34751, 35036, 34731, 35103)
 
GROUP BY d.id
LIMIT 0,20

R2 : Comme R1 mais je vire ce qui concerne la jointure dl2 ainsi que le GROUP_CONCAT associé dans le SELECT, executée en 0.83s

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
 
 
SELECT STRAIGHT_JOIN d.*, UNIX_TIMESTAMP(d.rappel_date) rappel_date_ts,
IF(dc.num_com, MD5(CONCAT(dc.num_com, 'commandematnat007')), NULL) c_hash, dc.num_com,
cl.mail, cl.nom, cl.prenom, if (cl.cp_livraison != '', cl.cp_livraison, cl.cp) AS cp, cl.cp_livraison, cl.societe,
u.nom AS nom_user, u.prenom AS prenom_user,
COUNT(co.datetime) as vue_nb, MAX(co.datetime) as vue_lastdate, MAX(UNIX_TIMESTAMP(co.datetime)) as vue_lastdate_ts,
MAX(co2.datetime) as relance_lastdate, MAX(UNIX_TIMESTAMP(co2.datetime)) as relance_lastdate_ts,
GROUP_CONCAT(DISTINCT dl.date_depart SEPARATOR ';') l_list,
GROUP_CONCAT(DISTINCT dpt2.id_produit SEPARATOR ';') l_prod
 
FROM devis d
INNER JOIN client cl ON cl.id_client=d.id_client
INNER JOIN user u ON u.id_user=d.id_user
LEFT JOIN devis_commande dc ON dc.num_devis=d.id
LEFT JOIN devis_comment co FORCE INDEX FOR JOIN (id_devis) ON co.type='clic' AND co.id_devis=d.id
LEFT JOIN devis_comment comc FORCE INDEX FOR JOIN (id_devis) ON comc.type='clic' AND comc.id_devis=d.id
LEFT JOIN devis_comment co2 FORCE INDEX FOR JOIN (id_devis) ON (co2.tel IN ('repondeur', 'eu') OR co2.envoi_email=1) AND co2.id_devis=d.id
LEFT JOIN contenu_devis dpt2 FORCE INDEX FOR JOIN (id_devis) ON dpt2.id_devis=d.id
LEFT JOIN devis_livraison dl FORCE INDEX FOR JOIN (devis_id) ON dl.devis_id=d.id
 
WHERE d.id IN (35087, 35110, 35110, 34736, 34923, 35059, 25573, 34066, 34362, 35112, 34801, 34997, 35081, 35087, 35110, 35076, 34371, 34893, 34981, 35011, 35078, 33803, 33803, 35129, 35129, 35082, 35132, 34473, 34173, 35103, 35134, 35134, 34187, 34751, 35036, 34731, 35103)
 
GROUP BY d.id
LIMIT 0,20

R3 : Complémentaire à R2, on prend les tables principales, on vire tout ce qui est potentiellement lourd à jointer excepter dl2 : exécutée en 0.0025s

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
 
SELECT STRAIGHT_JOIN d.*, UNIX_TIMESTAMP(d.rappel_date) rappel_date_ts,
cl.mail, cl.nom, cl.prenom, if (cl.cp_livraison != '', cl.cp_livraison, cl.cp) AS cp, cl.cp_livraison, cl.societe,
u.nom AS nom_user, u.prenom AS prenom_user,
GROUP_CONCAT(DISTINCT dl2.date_recpt SEPARATOR ';') l_list_2
 
FROM devis d
INNER JOIN client cl ON cl.id_client=d.id_client
INNER JOIN user u ON u.id_user=d.id_user
LEFT JOIN devis_livraison dl2 FORCE INDEX FOR JOIN (devis_id) ON dl2.devis_id=d.id AND (dl2.date_depart IS NULL OR dl2.date_depart='0000-00-00')
 
WHERE d.id IN (35087, 35110, 35110, 34736, 34923, 35059, 25573, 34066, 34362, 35112, 34801, 34997, 35081, 35087, 35110, 35076, 34371, 34893, 34981, 35011, 35078, 33803, 33803, 35129, 35129, 35082, 35132, 34473, 34173, 35103, 35134, 35134, 34187, 34751, 35036, 34731, 35103)
 
GROUP BY d.id
LIMIT 0,20

Suis-je contraint d’exécuter deux requêtes ? C'est assez problématique, car le moteur de recherche associé comporte de nombreux filtres et je devrais passer du temps à redécouper tout ce qui est long...

Aussi, et surtout, quelqu'un a-t-il une explication sur ces écarts de temps de recherche ? Je pensais comprendre la façon de fonctionner de MySQL et sur ce coup, j'avoue que je perds pied...

[SQLpro]

MySQL est un outil de bricoleur doté du plus mauvais optimiseur qui soit. Dès que le nombre de jointures dépasse un tant soit peu 5 ou 6 il est perdu; Si, en plus, vous lui forcer des tags (STRAIGHT_JOIN, FORCE INDEX FOR JOIN) cela peut conduire à des plans de requête de plus en plus erratiques et donc des performances aléatoire, voire dégueulasse.

A lire sur MySQL : http://blog.developpez.com/sqlpro/p9...alles_en_sql_1
http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux

A +

[lemathou]

Bonjour,

C'est justement parce que l'optimiseur se "trompait" parfois sur le choix d'un index ou encore l'ordre d'analyse des tables que j'ai du m'en passer et utiliser STRAIGHT_JOIN et FORCE INDEX...
Toutefois, ma question ne porte pas tant sur le choix de l'optimiseur que sur la façon dont MySQL va récupérer les données dans les tables, une fois qu'il a trouvé (ou qu'on lui a donné) l'ordre dans lequel il doit le faire... et force est de constater que que ce n'est pas clair du tout ou bien qu'il s'y prend là aussi comme un manche pour peu qu'on lui en demande un peu trop d'un coup.

Dans mon cas de figure, il me semblait qu'il commençait par aller chercher les lignes de la table d (devis) correspondant aux id que j'ai spécifié dans ma clause WHERE, puis qu'il effectuait successivement les jointures avec le reste, finalement qu'il effectuait le GROUP BY et qu'il extrayait de cet amas d'information ce que je lui demande dans le SELECT, aussi, je ne comprend pas qu'il lui faille 500ms pour effectuer une malheureuse jointure supplémentaire, avec un bel index, sur une table pas trop grosse, et me sortir un group concat..

Si je lui demande juste ceci, il va vite, mais si je le lui demande avec d'autres choses, il se met à ramer et ce n'est pas logique. Du coup j'aimerais comprendre la "logique" de MySQL pour ce qui est de l'optimiseur, la construction des requêtes, la récupération des données, mais je n'ai pas trouvé d'informations à ce sujet. Quelqu'un aurait une doc, même un peu longue, enfin... autre chose que le code source parce que je ne me sens pas de me cogner des milliers de lignes pour une appli que je ne vais bientôt plus maintenir...

Merci de votre aide !

[CinePhil]

Vite fait...

Code :

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

SELECT STRAIGHT_JOIN d.*,

Il vaut mieux éviter la guerre des étoiles !

Code :

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

WHERE d.id IN (35087, 35110, 35110,

Il y a deux fois 35110 !
Mettez les valeurs dans l'ordre. Ça permettra de voir les éventuels doublons et ça pourrait peut-être accélérer lég

Code :

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

GROUP BY d.id

Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes et donc potentiellement des valeurs fausses dans le résultat.