Discussion :

[Antoun]

En testant la requête d'Antoun, cela fait maintenant 6 minutes que j'attend un retour. Logiquement, entre 5000 et 7000 champs devraient avoir été retournés.

EDIT :

même en limitant les résultats, c'est la panade complète. Je passe en timeout quoi qu'il arrive.

Je continue mes tests

C'est normal, MySQL est nul à chier avec les sous-requêtes corrélées (par contre, ça devrait bien passer sur SQL Server) . Pour MySQL, il vaut mieux tester avec ma première requête.

[defcon_suny]

Pour des raisons de migration et traitement de données importantes, J'ai relevé le timeout du serveur. En production, il est clair que je ne peux pas me permettre des temps d'exécution pareils. J'ai quand même remarqué une réelle amélioration en implémentant la clause WHERE. Peut être devrai-je revoir mes INDEX de table.

Une autre contrainte d'environnement est l'utilisation de Mysql. Pas le choix!

[Antoun]

J'ai réécrit ma requête normalisée pour remplacer la sous-requête corrélée par une sous-requête dans le FROM :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT p.nom, p.prenom, a.rue, a.num, a.cp, a.localite
FROM aff_adresse AS a 
  LEFT JOIN aff_individu_has_aff_adresse AS pha ON a.id_adresse = pha.fk_id_adresse
  LEFT JOIN aff_individu AS p ON pha.fk_id_individu = p.id_individu
  LEFT JOIN (
    SELECT fk_id_adresse, MIN(fk_id_individu) AS min_id_p
    FROM aff_individu_has_aff_adresse
    GROUP BY fk_id_adresse
  ) AS tmp ON pha.fk_id_adresse = tmp.fk_id_adresse AND pha.fk_id_individu = tmp.min_id_p
GROUP BY a.rue, a.num, a.cp, a.localite

Par contre, je n'ai pas pu télécharger tes INSERT (politique de sécu de ma boîte) et donc pas pu tester avec des données.

[defcon_suny]

Je peux te les mettre dans le post mais cela risque de faire long

[Antoun]

lol !

Teste juste ma dernière requête. Si ça merdouille, j'essairai ce soir avec tes données (chez moi j'ai encore le droit de télécharger ce que je veux).

[defcon_suny]

C'est ce que je viens de faire et mis à part 100% des ressources processeur, la requête ne sélectionne rien. j'ai killé le process au bout de 8 minutes.

Je suis vraiment désolé de t'annoncer ce genre de nouvelle... je dois avouer que je suis complètement dépassé par le résultat de ce post... cela ne me paraissait pas aussi compliqué initialement.

Dois-je revoir quelque chose dans la structure de mes tables?

[Antoun]

Dois-je revoir quelque chose dans la structure de mes tables?

A vue de nez tout est bon dans ta structure... Il va juste falloir que tu patientes jusqu'à ce soir.

(au fait, la première requête que je t'avais donnée...

Code :

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

1
2
3
4
5
SELECT t1.nom, t1.prenom, t2.rue, t2.numero, t2.code, t2.localite
FROM `personne` AS t1 
LEFT JOIN adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
LEFT JOIN personne_has_adresse AS t3 ON t3.fk_id_personne = t1.id_personne
GROUP BY t2.id_adresse

Elle fonctionne bien et rapidement ?)

[defcon_suny]

Voilà le résultat de ta première requête Antoun

Code :

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

1
2
3
4
5
6
 
SELECT t1.nom, t1.prenom, t2.rue, t2.num, t2.cp, t2.commune
FROM aff_individu AS t1 
LEFT JOIN aff_individu_has_aff_adresse AS t3 ON t3.fk_id_individu = t1.id_individu
LEFT JOIN aff_adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
GROUP BY t2.id_adresse

Me retourne 77502 enregistrements en 9.1092 sec.

[Orgied]

Ce que je ne comprend pas, c'est que cette première requête n'a jamais fonctionné sous le MySQL de WAMP que j'utilise...

Erreur

requête SQL: Documentation

SELECT t1.nom, t1.prenom, t2.rue, t2.numero, t2.code, t2.localite
FROM `personne` AS t1
LEFT JOIN adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
LEFT JOIN personne_has_adresse AS t3 ON t3.fk_id_personne = t1.id_personne
GROUP BY t2.id_adresse
LIMIT 0 , 30

MySQL a réponduocumentation
#1054 - Unknown column 't3.fk_id_adresse' in 'on clause'

En même temps, je me méfie. C'est souvent les erreurs les plus flagrandes qu'on ne remarque jamais, même en passant 36 fois dessus. Donc si vous voyez où je me plante, hésitez pas

[defcon_suny]

Code :

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

1
2
3
4
5
SELECT t1.nom, t1.prenom, t2.rue, t2.numero, t2.code, t2.localite
FROM `personne` AS t1
LEFT JOIN personne_has_adresse AS t3 ON t3.fk_id_personne = t1.id_personne
LEFT JOIN adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
GROUP BY t2.id_adresse

Les 2 LEFT JOIN doivent être intervertis! puisqu'on fait références à t3.fk_id_adresse lorsque t3 n'est pas encore aliaser

Fait attention que les requêtes du début du post étaient adaptées à des tables simplifiées... Je vous ai posté les requêtes de création des tables réellement utilisées avec des insertions.

Et moi qui croyait que je commençais à maitriser mon SQL... BARDAF!!! c'est l'embardée...

[Antoun]

Bon voilà le résultat de mes tests sur les données-test fournies par defcon (au passage, j'ai dû passer toutes les colonnes NOT NULL en NULL sur aff_individu) :

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
select version() ; -- chez moi, 5.1.19
 
RESET QUERY CACHE ; -- vide le cache pour permettre de lancer les requêtes plusieurs fois de suite sans amélioration anormale du temps de réponse (en utilisant le mot-clé SQL_NO_CACHE comme je le fais par la suite)
 
-- requête 1 bis : GROUP BY avec optimisation spécifique à MySQL
-- jointures retournées par rapport aux messages précédents
-- renvoie 471 lignes en 150 à 200 millisecondes
SELECT SQL_NO_CACHE t1.nom, t1.prenom, t2.rue, t2.num, t2.cp, t2.commune
FROM aff_adresse AS t2
LEFT JOIN aff_individu_has_aff_adresse AS t3 ON t2.id_adresse = t3.fk_id_adresse
LEFT JOIN aff_individu AS t1 ON t3.fk_id_individu = t1.id_individu
GROUP BY t2.id_adresse ;
 
 
-- requête 2 : sous-req corrélée
-- renvoie 471 lignes en 180 à 200 millisecondes
SELECT SQL_NO_CACHE p.nom, p.prenom, a.rue, a.num, a.cp, a.localite
FROM aff_adresse AS a 
  LEFT JOIN aff_individu_has_aff_adresse AS pha ON a.id_adresse = pha.fk_id_adresse
  LEFT JOIN aff_individu AS p ON pha.fk_id_individu = p.id_individu
     AND p.id_individu = (
       SELECT MIN(fk_id_individu)  -- individu avec la plus petite ID...
       FROM aff_individu_has_aff_adresse  pha2
       WHERE pha2.fk_id_adresse = a.id_adresse  -- ... pour l'adresse en cours
     )
GROUP BY  a.rue, a.num, a.cp, a.localite ;
 
 
-- requête 3 : sous-req dans le FROM (table temporaire)
-- renvoie 471 lignes en 180 à 200 millisecondes
SELECT SQL_NO_CACHE p.nom, p.prenom, a.rue, a.num, a.cp, a.localite
FROM aff_adresse AS a 
  LEFT JOIN aff_individu_has_aff_adresse AS pha ON a.id_adresse = pha.fk_id_adresse
  LEFT JOIN aff_individu AS p ON pha.fk_id_individu = p.id_individu
  LEFT JOIN (
    SELECT fk_id_adresse, MIN(fk_id_individu) AS min_id_p
    FROM aff_individu_has_aff_adresse
    GROUP BY fk_id_adresse
  ) AS tmp ON pha.fk_id_adresse = tmp.fk_id_adresse AND pha.fk_id_individu = tmp.min_id_p
GROUP BY a.rue, a.num, a.cp, a.localite ;

Mes conclusions :

• la suboptimisation aberrante des sous-requêtes corrélées semble avoir été corrigée en 5.1... mais elle existait en 5.0. Du coup, les temps de réponse sont équivalents entre les requêtes 2 et 3, sans doute parce que MySQL réécrit la requête 2 en équivalent de la requête 3
  
• le volume de données testé est assez faible, du coup les tests de perf ne donnent pas de différence spectaculaire ; la requête 1 est néanmoins plus sensiblement plus rapide. Ce n'est pas pour rien que MySQL parle d' "optimisation du GROUP BY".
  
• j'ai également testé en remplaçant tous les LEFT JOIN par des INNER JOIN. Résultat : 92 lignes dans les trois requêtes, avec des temps de réponse légèrement meilleurs (durée environ -30%).

Au total, les trois versions fournissent le même résultat ; la première est optimisée pour MySQL et devraient être utilisée ; les deux autres seraient OK avec un autre SGBD, et auraient vraisemblablement des temps de réponse corrects. Il pourrait être utile de les mettre dans le commentaire du code, pour prendre les précautions nécessaires en cas de migration.

[defcon_suny]

Ma version de MySQL => 5.0.45
Je comprends maintenant le mauvais résultat des requêtes corrélées et mon manque de connaissance de l'évolution de MySQL.

Je vais mettre en place la requête utilisant l'optimisation du GROUP BY MySQL. Je n'ai pas vraiment le choix vu la version du serveur en prod. Je renseignerai les requêtes corrélées en commentaire.

Je voudrais spécialement vous remercier du temps que vous m'avez accordé et de votre volonté à aller au bout des choses.

Ce post a été très enrichissant pour moi!

Une dernière fois merci!