Discussion :

[Dimitri69]

Bonjour,

Je me pose une question sûrement bête, à laquelle je n'arrive pas à trouver de réponses sur internet...

Imaginons une table articles, et une table pieces_jointes.

Un article peut avoir plusieurs pièces jointes. Dans ma table pièces-jointes, j'ai un index articleId (on pourrait aussi imaginer une relation plusieurs à plusieurs, mais là n'est pas la question qui me préoccupe).

Si je veux récupérer tous les articles avec leurs pièces jointes, je peux faire une jointure :

Code :

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

1
2
3
SELECT * 
FROM articles
LEFT JOIN pieces_jointes ON pieces_jointes.articleId = articles.id

Je me retrouve avec un résultat comme :

article A (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 1
article A (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 2
article A (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 3
article B (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 4
article C (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 5
article D (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 6
article D (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 7
article D (avec ses champs texte pouvant contenir des milliers de signes) | piece jointe 8
...


Ma question est la suivante : cette requête récupère autant de fois un même article qu'il y a de pièces jointes qui lui sont associées. Chaque article pouvant représenter une taille importante (imaginez plusieurs champs textes avec des milliers de caractères), cela n'est-il pas un problème en terme de mémoire (ou est-ce que le SGBD créé en fait un pointeur vers un unique enregistrement) ?

J'utilise PHP en langage serveur, et du coup, ne vaut-il pas mieux procéder en 2 requêtes ? Une qui récupères tous les articles, et une autre toutes les pièces jointes via un SELECT * FROM pieces_jointes WHERE articleId IN (...) ?
Ensuite, en PHP et avec une boucle, on peut associer aux articles les pièces jointes correspondantes.

Je me prends la tête pour rien, ou est-ce une vraie question ? Dans ce dernier cas, quelle est votre réponse ?

Merci d'essayer de m'aider à comprendre Je n'ai peut-être pas trouvé les formulations nécessaires, mais il m'a été impossible d'obtenir une quelconque réponse sur internet

[Mohamed_DEV]

Il faut ajouter DISTINCT à ton SELECT pour éviter d'avoir le même article plusieurs fois.

Code :

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

1
2
3
4
 
SELECT DISTINCT * 
FROM articles
LEFT JOIN pieces_jointes ON pieces_jointes.articleId = articles.id

[Dimitri69]

Merci pour ta réponse, Mohamed_DEV, mais elle n'est pas valide.

Le DISTINCT ne change rien ici, car je n'ai pas de doublons de lignes.

Une reformulation courte est simple de ma question pourrait être :

Ce type de jointure (cf. premier post) est-il adapté lorsqu'on récupère des données volumineuses, du fait des doublons obtenus sur la table de gauche ?

En php, pour exploiter le résultat de cette jointure, je vais faire :

Code :

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

1
2
3
foreach($recordset as $record){
// blablabla
}

À chaque itération, $record va contenir des données de taille importante, et la mémoire nécessaire sera donc importante également, non ? Alors qu'en faisant 2 requêtes séparées, je n'ai à gérer moins de données.

[seabs]

Bonjour,

Il aurait été préférable d'ajouter, à ta demande, la présentation des tables créées.

Nous sommes en présence d'une configuration table maître et table détail.

Si nous reprenons la présentation des tables, elles doivent reposer sur le schéma suivant

Table Articles (ArtId, Zone1, Zone2, etc)
Table Pieces_Jointes (PieceId, #ArtId, Piece, etc)
Souligné en gras = clé primaire
# clé étrangère

Les requêtes à établir
Requête pour la table maître

Code :

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

1
2
3
4
 
SELECT ArtId, Zone1, Zone2
FROM Articles
WHERE ArtId = :pArtId

Pour tes pièces jointes, table détail, nous aurons la requête

Code :

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

1
2
3
4
 
SELECT PieceId, Piece
FROM Pieces_Jointes 
WHERE ArtId = :pArtId

:pArtId = paramètre à transmettre.

Ensuite, il convient d'organiser la présentation à l'écran pour que, lors de l'affichage de l'article tu puisses présenter toutes les pièces jointes liées à l'article.
Tu peut ne présenter que le titre la pièce jointe dans une première approche, puis faire une présentation de la pièce complète pour la lecture.
Voilà une méthode à parfaire. Il est certainement possible de présenter des variantes.

Bon courage

[Dimitri69]

Merci pour ta réponse, seabs.

Oui, désolé pour le schéma, mais celui que tu as deviné est parfait.

Par contre, ma question porte sur la récupération de tous les articles et de leur(s) pièce(s) jointe(s) (ou d'un nombre limité d'articles). Or, ta réponse ne porte que sur la récupération d'un seul article et de ses éventuelles pièces jointes.

Cela dit, je vois que même dans ce cas, tu n'utilises pas de jointure, mais préfères effectuer 2 requêtes distinctes.

J'en déduis donc que la jointure n'est pas approprié dans mon cas, et que, comme je l'ai suggéré, mieux vaut faire deux requêtes pour :

1. récupérer les articles (requête 1)
2. récupérer les pièces jointes (deuxième requête, avec un IN (articlesIds) )
3. utiliser PHP pour présenter les bonnes pièces jointes avec les bons articles

C'est donc la meilleure méthode ?

[seabs]

Bonjour,

Pour récupérer tous les articles, il suffit de modifier la requête de la table maître ainsi

Code :

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

1
2
SELECT ArtId, Zone1, Zone2
FROM Articles

Mais, bonjour le trafic sur le réseau. Imagines un million d'articles avec leurs pièces jointes, le temps et les ressources nécessaires.

Pour les pièces jointes, la requête ne doit subir aucun changement. L'instruction IN n'est pas nécessaire, il faut simplement créer le code pour synchroniser la table Articles et la table Pièces_Jointes.

Tu utiles n'importe quel langage pour faire la présentation, en fonction de tes connaissances.

Là, nous sommes dans l'idée générale. Ensuite, il faut adapter tout cela au besoin de l'utilisateur.

La requête pour la table détail peut certainement s'améliorer en fonction des besoins réels.

Présentes plus en détail ton projet, il aura certainement d'autres intervenants qui pourront t'aider.

Bon courage

[Dimitri69]

Bein, je pensais avoir bien détaillé la problématique, mais apparemment non

Ci-dessous, une reformulation. Mes précédents posts peuvent être ignorés.

Table articles :

Code :

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

idArticle|title|text|...

Table pieces_jointes

Code :

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

idPieceJointe|articleId|filename|...

idArticle et idPieceJointe sont les clés primaires auto-incrémentées.

Pour récupérer 10 articles avec leurs pièces jointes :

Solution 1

Code :

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

1
2
3
SELECT * FROM articles
LEFT JOIN pieces_jointes ON articles.idArticle = pieces_jointes.articleId
LIMIT 0,10

Retourne, si l'article A contient 3 pièces jointes :

article A | pièce jointe 1
article A | pièce jointe 2
article A | pièce jointe 3
article B | NULL
article C |pièce jointe 4
...
...

Question : est-ce un problème (pour la gestion mémoire de la BDD, ou autre) de récupérer plusieurs fois le même article (et tous ses champs) ? Autrement dit, est-ce un problème, dans cette jointure, d'avoir des doublons au niveau de la table de gauche (la table articles) ?

Solution 2

Code :

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

1
2
SELECT * FROM articles
LIMIT 0,10

Puis, en PHP, je récupère toutes les clés primaires retournées (idArticle), que je place dans une variable $in.

Ensuite :

Code :

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

SELECT * FROM pieces_jointes WHERE articleId IN ($in)

Enfin, en PHP, je réorganise les données pour associer les pièces jointes aux articles correspondants.

Cette seconde solution ne récupère plus aucun doublons.

Question finale : quelle est la meilleure solution (s'il y en a une) ?

[SQLpro]

Vous n'indiquez pas les types de données, des contraintes, les index...

Conformez vous à la règle de postage SVP : http://www.developpez.net/forums/a69...gage-sql-lire/

A +

[Dimitri69]

Alors voici les informations demandées...

La base de donnée utilisée est MySQL, mais ma question est généraliste.

Soit une table article:

Code :

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

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` longtext NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Soit une table attachments (pièces jointes aux articles):

Code :

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

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleId` int(11) NOT NULL,
  `fileName` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `articleId` (`articleId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Nous avons ici une relation 1 à plusieurs, plusieurs attachments (les pièces jointes, fichiers pdf, par exemple) peuvent être associées à un même article. L'index articleId de la table attachments est relié à la clé primaire id de la table article, et référence l'article avec lequel la pièce jointe est attachée...

Ce que je veux faire, c'est récupérer, par exemple, 10 articles avec leurs pièces jointes.

Je fais donc :

Code :

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

1
2
3
4
SELECT * FROM article
    LEFT JOIN pieces_jointes 
        ON articles.idArticle = pieces_jointes.articleId
        LIMIT 0,10

J'obtiens bien ce que je veux, à savoir (par exemple) :

Code :

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

1
2
3
4
5
6
7
 
Titre article A | Très long texte (contenu de l'article A) | fichier1.pdf
Titre article A | Très long texte (contenu de l'article A) | fichier2.pdf
Titre article A | Très long texte (contenu de l'article A) | fichier3.pdf
Titre article B | Très long texte (contenu de l'article A) | fichier9.pdf
Titre article C | Très long texte (contenu de l'article A) | fichier4.pdf
Titre article C | Très long texte (contenu de l'article A) | fichier7.pdf

On remarque que 3 pièces jointes étaient attachées à l'article A, une seule à l'article B, 2 à l'article C, mais peu importe...

Ma question est (mais elle est générale, et donc je ne suis pas sûr de l'utilité de l'exemple précédent pour la comprendre) :

Dans une jointure externe gauche, nous récupérons des doublons au niveau de la table de gauche (dans l'exemple précédent, j'ai 3 lignes contenant l'article A, et deux autres avec l'article C). Cela pose t-il un problème, du fait que je récupère également, dans ma requête, les valeurs du champ "text" des articles, pouvant contenir un texte très long ? Au niveau de la mémoire de la base de données, créée t-elle un pointeur vers l'article A ? Ou retourne t-elle, pour chaque ligne, l'intégralité du contenu des articles (au niveau de son fonctionnement interne) ?

Aurait-il mieux fallu exécuter une première requête comme celle-ci :

Code :

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

SELECT * FROM article LIMIT 0,10

Puis récupérer les IDs (clé primaire) de chaque article retourné (placés ici dans la variable PHP $in) pour construire cette seconde requêtes :

Code :

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

SELECT * FROM attachments WHERE articleId IN ($in)

Et enfin, en PHP, associer les bonnes pièces jointes aux articles correspondants.

Quelle est la meilleure solution ?

[punkoff]

Bonjour,


Déjà vos deux méthodes ne font pas la même chose.

Votre 1ere méthode retourne les 10 1er résultats de la jointure, et donc potentiellement va ne ramener que 10 pièces jointes au 1er article.


Votre 2eme méthode ramène 10 articles distincts, puis va ramener n fichiers attachés.

Au final, vous aurez dans tous les cas 10 libellés "long" rattaché aux articles, ce qui représente en gros 2,5ko de données (voir le double selon l'encodage de la base) ....

[Dimitri69]

Bonjour,

Merci pour votre réponse.

En dehors du fait qu'effectivement les résultats sont différents, qu'elle est la méthode à privilégier, d'après vous ? Et comment calculez-vous les 2.5Ko ? Car 10 champs en longtext, (soit 4 294 967 295 de caractères potentiels), cela ne fait pas un peu plus quand même ?

[punkoff]

Ah, non c'est différent

J'ai cru voir que le texte associé était un varchar(255) et non un longtext !


Pour le calcul des tailles c'est assez simple ... regardez la doc de MySql
http://dev.mysql.com/doc/refman/5.0/...-overview.html
http://dev.mysql.com/doc/refman/5.0/...uirements.html

LONGBLOB, LONGTEXT

Une colonne LONGTEXT ou LONGBLOB peut contenir au maximum 4294967295 ou 4 Go (2^32 − 1) caractères. Jusqu'en version 3.23 le protocole client/serveur et les tables MyISAM avait une limite de 16 Mo par paquet de communication pour une ligne de table. Depuis les versions 4.x, la taille maximale d'un LONGTEXT ou LONGBLOB dépend de la taille maximal de paquet de communication pour le protocole de communication, et de la mémoire disponible.

Concernant votre question je n'ai pas de réponse toute faite, vu qu'il faudrait prendre en compte la taille moyenne / max de cette colonne avant de pouvoir donner un semblant de réponse ....

[Dimitri69]

Concernant votre question je n'ai pas de réponse toute faite, vu qu'il faudrait prendre en compte la taille moyenne / max de cette colonne avant de pouvoir donner un semblant de réponse ....

Cela répond à ma question

Parce que, vous laissez penser que la quantité de données retournées a un rôle, et que, du coup, la jointure externe gauche, par les doublons qu'elle occasionne éventuellement sur sa table de gauche, peut être un problème si les champs retournés dans les lignes du recordset contiennent beaucoup de données.

C'est bien ça ?

[punkoff]

Bein c'est plus pour le réseau que je m'inquiète personnellement.

Potentiellement 4Go de donnée * 10 ça fait beaucoup.

Maintenant est-ce bien le cas ?

Concernant le mécanisme interne de MySql pour effectuer une jointure je n'en ai aucune idée.

[CinePhil]

Sur le plan du principe général, une requête ramenant les articles et leurs pièces jointes avec une seule requête ne pose pas de problème.

Par contre, il faut se poser des questions sur la méthode, c'est à dire le processus de délivrance des données à l'utilisateur.

En tant qu'utilisateur, accepteriez-vous d'attendre un certain temps (et même un temps certain !) que le site puisse enfin afficher les 10 articles très longs, ce qui provoquera de plus un exercice de musculation sur l'index droit à force de manipuler la molette de la souris pour scroller l'écran ?

ou bien ne préféreriez-vous pas voir, toujours en tant qu'utilisateur, simplement le titre et un court extrait du début de chaque article puis sélectionner celui qui vous intéresse pour l'afficher en entier et pouvoir récupérer ses pièces jointes ?

Regardez nos blogs sur DVP : c'est la seconde solution qui est utilisée !
Quant aux forums, il n'affichent que les titres de discussions puis un nombre limité de messages par discussion.

[Dimitri69]

Oui, je suis bien d'accord.

Mais mon post a mal été rédigé et compris, donc. Ma question portait sur le fond uniquement, sur la requête et les doublons ramenés par la jointure externe gauche. Uniquement (et c'est pour cela que je ne souhaitais pas développer trop un exemple précis, comme demandé par SQLPro).

Je retiens de votre message que cela ne pose pas de souci à la BDD.

[CinePhil]

Je retiens de votre message que cela ne pose pas de souci à la BDD.

Encore une fois, sur le principe, la jointure est l'opération la plus optimisée donc ne posera pas de problème au SGBD.
Par contre, ce qui peut poser des problèmes, c'est extraire un très gros volume de données d'un coup. Tout dépend du dimensionnement du serveur et du réseau.

Si tous vos articles sont très gros, il faudrait se poser la question s'il est opportun de les stocker en BDD plutôt que sous forme de fichiers sur disque et ne stocker en BDD qu'un lien vers le fichier de l'article. Extraire 4 Go d'un coup n'est pas sans conséquences, avec jointure ou pas !

[Dimitri69]

Oui, merci pour ce complément d'info.

Bonne fin de journée.