SQL Récupérer les sous-objets d'une liste d'objets - Page 1

Le titre n'est pas clair, mais je ne sais pas comment formuler ça autrement

Je me demande quelle est la façon la plus efficace de résoudre un problème que j'ai toujours traité de façon "crade", mais qui me bloque aujourd'hui puisque le volume de données à traiter devient trop important.

Imaginons que je veuille modéliser des personnes, qui possèdent tout un tas de caractéristiques, et que je veuille conserver pour chacune d'elles la liste des livres qu'elles ont lu. J'ai donc ces deux objets :

[ul][li]Personne[ul][li]Nom[/li][li]Prénom[/li][li]Age[/li][li]... plein d'autres attributs ...[/li][li]Liste des livres lus[/li][/ul][li]Livre[ul][li]Titre[/li][li]Auteur[/li][li]Date de lecture[/li][/ul][/li][/ul]
Je voudrais, en une ou plusieurs requête(s) rapide(s), récupérer certaines personnes (en fonction de critères qui dépendent des attributs de ces personnes) et pour chacune d'elle la liste des livres qu'elles ont lu. Je suppose, bien que ce ne soit pas obligatoire, que je possède deux tables pour contenir mes deux objets. Chaque livre possède une référence vers la personne qui l'a lu, et sera donc dupliqué autant de fois que nécessaire (3 personnes ont lu le même livre => il sera présent 3 fois en base ; dans la vraie vie mes "livres" sont en réalité des entités uniques donc ça n'est même pas une perte de place).

Je vois trois solutions simples :

- Une requête pour récupérer les N personnes qui m'intéressent, puis N requêtes pour récupérer les livres de chacune d'elle. C'est hyper lent, puisque si je remonte 1000 personnes il me faut 1001 requêtes, avec les performances que vous pouvez imaginer derrière.

- Une requête avec une jointure, pour remonter d'un seul coup les personnes qui m'intéressent et les livres qu'elles ont lu. Mais si une personne a lu 100 livres, je vais avoir 100 fois chacun de ses champs dans mon résultat, or autant un livre est une petite structure, autant une personne contient beaucoup de champs. Du coup mon résultat peut devenir potentiellement énorme, et donc très lent.

- Une requête avec une jointure, mais comme j'utilise MySQL je me sers de GROUP_CONCAT pour concaténer les champs des livres, de façon à n'avoir qu'une ligne de résultat par personne. Par exemple, si je concatène avec le caractère "|" comme séparateur, une ligne de résultat pourrait ressembler à ça :
Nom

Prénom	Age	... plein de colonnes ...	Titres	Auteurs	Dates
Croc	Odile	27	...	Titre1|Titre2|Titre3	Auteur1|Auteur2|Auteur3	Date1|Date2|Date3

Plusieurs problèmes avec cette troisième solution : c'est pas portable (GROUP_CONCAT n'est pas standard), je peux avoir des problèmes s'il y a vraiment trop de livres (il y a une limite pour la taille de la chaine), et je suis obligé d'échapper les "|" dans les valeurs des champs des livres. Malgré ces contraintes, c'est la solution la plus rapide des trois, mais je n'en suis pas vraiment satisfait.

Voyez-vous une solution plus propre et plus performante ? (ce problème me semble tellement générique qu'il y a forcément mieux, j'imagine ?)

hmm et si tu fais une variation de la première?

- 1 requete pour avoir la liste des gens qui t'intéressent
- 1 requete avec un WHERE ID IN (la liste des ID obtenus avant)

d'ailleurs on doit pouvoir faire
-SELECT les livres WHERE USERID IN (SELECT tous les ID qui t'intéressent)

tu vois le truc? ça le ferait pas?

heu, c'est pas propre comme structure de base de donnée, t'as une relation n<->m, il te faut une table de jointure à 2 colonnes : user_id | book_id


sinon, il faut éviter le WHERE id IN (liste hyper longue), car il y a une limitation en MySQL non négligeable, et meme si on atteint pas la limite, ca va être hyper long à traiter également. j'ai déjà eu le cas. il vaut beaucoup mieux avoir une requete imbriqué WHERE id IN (SELECT....)

voila, parce que là il doit pouvoir scanner les deux tables en parallèle.

./2 : J'y ai pensé, mais le "WHERE blah IN (...)" est vraiment très lent, encore plus que toutes les solutions que j'ai évoquées :/

./3 : Non non, cf. ce que j'ai précisé entre parenthèses dans le post, mes "livres" sont en réalité uniques (il s'agit de lignes d'historique liées à chaque personne, qui ne sont d'ailleurs pas des personnes non plus ^^). Deux utilisateurs ne peuvent pas avoir le même livre. Du coup il ne s'agit que d'une relation 1-N, et une table intermédiaire de liaison ne me servirait à rien (à part à rendre les performances encore plus mauvaises )

ACK pour la vitesse.

si c'est du 1..N autant stocker id_user dans la table livre?

C'est ce qui est fait (cf ./1 : "chaque livre possède une référence vers la personne qui l'a lu")

ah vi pardon, j'avais mal lu cette partie. c'est juste que l'exemple des livres portaient à confusion, je pensais qu'il gardait un historique des livres déjà lus. en fait, ca serait plus la liste des livres empruntés actuellement, et là forcement un même livre ne peut pas être emprunté 2 fois en même temps...

Oui bon, comme d'hab ma tentative de simplification était foireuse

ok
ben dans ce cas là, je passerais par une table temporaire, et je ferais le tout en 3 requetes :

création de la table temporaire "temp" avec : SELECT id FROM user WHERE (tous tes criteres)
récupération des users : SELECT * FROM user WHERE id IN (SELECT id FROM temp)
récupération des books : SELECT * FROM book WHERE id_user IN (SELECT id FROM temp)
et apres, bye bye table temporaire

l'avantage étant que tu ne fais ta recherche compliquée qu'une seule fois, et apres, ce n'est que des recherches sur un champs unique et indexé => c'est hyper rapide

Je n'ai pas fini d'implémenter ta solution, mais d'après mes premiers tests ça a effectivement l'air d'être performant. Verdict dès que j'ai un résultat

Bon ben fausse joie, finalement c'est (beaucoup) plus lent que la solution avec GROUP_CONCAT, j'ai des temps jusqu'à 5 à 6 fois plus importants :/

On notera aussi que la deuxième solution m'empêche de trier directement mes résultats comme je le veux, donc je suis obligé de le faire à la main ensuite, mais cette étape ne me prend pas beaucoup de temps ; ce sont vraiment les requêtes qui n'atteignent pas l'efficacité de la solution avec GROUP_CONCAT :/

Je me suis loupé quelque part, ou bien c'est sans espoir ? ^^

À mon avis, ce sont les IN qui prennent trop de temps. Ce serait peut-être plus efficace de faire une jointure.
Avec ta solution, il doit vérifier pour chaque utilisateur si son id est dans la liste, alors qu'avec une jointure, il ne prendrait que les utilisateurs qui ont le bon id.
Enfin, je ne suis pas sûr que ce serait plus rapide, mais tu peux peut-être essayer

Bien vu, avec une jointure ça devient effectivement plus rapide que la solution avec GROUP_CONCAT

La différence est minime, mais ça a le gros avantage de devenir portable. Merci !

bon, ben impec et en effet, j'aurais du conseiller les jointures et pas le IN dans ./10