Discussion :

[ZN]

Bonjour,

je cale sur une requete SQL.

C'est une requete avec des sous requetes sur la meme table, je me demande s'il n'est pas possible de faire des jointures plutôt que des select imbriqués, qui affectent fortement les performances.

Plus concrètement, c'est pour un script de "forum maison"; en gros un forum à un id, et un parent_id s'il s'agit d'un sous forum. On pourrait schématiser mon arborescence comme ci dessous

forum 1
--sous forum 1.1
--sous forum 1.2
--sous forum 1.3
--sous forum 2.1
--sous forum 2.2
------sous forum 2.2.1
------sous forum 2.2.3
forum 2
etc

3 niveaux donc.

J'aimerai que le forum 1 affiche les topics de tous ses sous forums.

Ca me donne des requetes avec des SELECT imbriqué ainsi:

Code :

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

1
2
3
4
 
SELECT * FROM topics 
WHERE forum_id IN 
(SELECT id FROM forums WHERE id=1 OR parent_forum_id=1 OR id IN (SELECT parent_forum_id FROM forums WHERE parent_forum_id=1))

edit: (ma requette ci dessus est fausse la partie

Code :

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

OR id IN (SELECT parent_forum_id FROM forums WHERE parent_forum_id=1))

est bidon. )

ce qui ne me plait pas du tout ... je dois pouvoir faire tout ca avec des jointures non? Merci d'avance pour votre aide,

ZN

[Antoun]

Il faut utiliser des alias de table :

Code :

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

1
2
3
4
5
6
SELECT * 
FROM topics T
  INNER JOIN forums F1 ON T.forum_id = F1.id
  LEFT JOIN forums F2 ON F1.parent_forum_id = F2.id
  LEFT JOIN forums F3 ON F2.parent_forum_id = F3.id
WHERE 1 IN (F1.id, F2.id, F3.id)

Pour une meilleure modélisation (qui permettrait de faire sauter la contrainte du nombre de niveaux connu à l'avance, voir http://sqlpro.developpez.com/cours/arborescence).

[ZN]

Bonjour,

je déterre une vieille discussion. Tout d'abord merci Antoun, je me suis inspiré de ton code et je l'utilise depuis un bon moment maintenant.

Sauf que ... ma base grossissant, les perfs en prennent un méchant coup. Est ce qu'il serait possible d'alléger tout ça d'une façon ou d'une autre ? (index, vues, sous requetes, ou plusieurs requetes?)

(je suis sous mysql)

merci d'avance pour vos idées et suggestions,

ZN

[Antoun]

commence par indexer les colonnes de jointure.

[ZN]

Si la colonne de jointure est la clef primaire, est ce qu'elle ne serait pas déjà indexée ? (merci)

[Antoun]

Il me semble, mais ça doit dépendre du moteur de stockage... dans tous les cas, un petit SHOW INDEX te renseignera utilement.

Il faut également indexer la colonne avec laquelle tu jointures ta clé primaire (parent_forum_id dans l'exemple ci-dessus).

[ZN]

Hello,

merci encore. J'ai mis en place un index sur le parent_id, ça a l'air un peu mieux. L'identifiant/clef primaire etant déjà indexée sous mysql, je n'ai pas eu besoin de créer d'index dessus.

Autre chose, j'ai amélioré le code de mon application pour minimiser "l'imbrication" des 3 tables, ce qui me donne une requete du genre

Code :

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

1
2
3
4
5
SELECT * 
FROM topics T
  INNER JOIN forums F1 ON T.forum_id = F1.id
  LEFT JOIN forums F2 ON F1.parent_forum_id = F2.id
WHERE 1 IN (F1.id, F2.id)

Peut-etre devrais-je changer d'approche /de requete maintenant que je ne ne conserve que 2 niveaux, afin de gagner en performance ?

Auriez-vous des idées ou suggestions?

Merci infiniment!

[ZN]

En réflechissant là dessus hier soir et surtout ce matin à tête reposée, si l'enleve un niveau je peux utiliser le genre de requete ci dessous :

Code :

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

1
2
3
4
SELECT * 
FROM topics 
WHERE forum_id=MON_ID 
  OR forum_id IN (SELECT id from forums WHERE parent_forum_id=MON_ID)

En terme de performances (désolé je suis très novice dans ce registre) est ce c'est mieux que

Code :

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

1
2
3
4
5
6
 
SELECT * 
FROM topics T
  INNER JOIN forums F1 ON T.forum_id = F1.id
  LEFT JOIN forums F2 ON F1.parent_forum_id = F2.id
WHERE MON_ID IN (F1.id, F2.id)

?

A priori après test rapide sous phpMyAdmin il semblerait que c'est le cas mais est ce que je ne me trompe pas ?

Merci d'avance pour votre aide et vos suggestions,

ZN

[Antoun]

Autre chose, j'ai amélioré le code de mon application pour minimiser "l'imbrication" des 3 tables, ce qui me donne une requete du genre

Peut-etre devrais-je changer d'approche /de requete maintenant que je ne ne conserve que 2 niveaux, afin de gagner en performance ?

On parle d'imbrication quand il s'agit de sous-requêtes, et effectivement il est judicieux d'imbriquer le moins possible, à la fois en termes d'optimisation et de lisibilité.

Mais dans ton cas, il ne s'agit pas de niveaux d'imbrications de sous-requêtes, mais simplement de nombre de jointures. Or, il n'y a pas de vrai problème à multiplier les jointures, du moment qu'elles sont simples et indexées.

Dans ton cas, je ne vois que deux points susceptibles d'être améliorés : le LEFT JOIN (qui est bien moins bon qu'un INNER JOIN) et le IN (dont je crains qu'il ne te fasse perdre l'index).

Pour le LEFT JOIN, je te laisse voir s'il est possible de le remplacer par un INNER JOIN, mais j'imagine que non.

Pour le IN, l'alternative serait une union :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * 
FROM topics T
  INNER JOIN forums F1 ON T.forum_id = F1.id
  LEFT JOIN forums F2 ON F1.parent_forum_id = F2.id
WHERE MON_ID = F1.id
UNION ALL
SELECT * 
FROM topics T
  INNER JOIN forums F1 ON T.forum_id = F1.id
  INNER JOIN forums F2 ON F1.parent_forum_id = F2.id
WHERE MON_ID = F2.id
  AND MON_ID <> F1.id

En réflechissant là dessus hier soir et surtout ce matin à tête reposée, si l'enleve un niveau je peux utiliser le genre de requete ci dessous :

En terme de performances (désolé je suis très novice dans ce registre) est ce c'est mieux que (...) ?

A priori après test rapide sous phpMyAdmin il semblerait que c'est le cas mais est ce que je ne me trompe pas ?

Merci d'avance pour votre aide et vos suggestions,

ZN

En général, les performances des sous-requêtes dans MySQL oscillent entre le médiocre et le catastrophique. L'optimisation consiste plutôt à transformer les sous-requêtes en jointures que le contraire !

Quand tu fais tes tests, pense bien à les faire en vrai grandeur, car le comportement de l'optimiseur MySQL sur 10 000 lignes n'a rien à voir avec son comportement sur un million !