Question mysql

18 réponses
AuteurMessage

vincir |
Membre

 

Inscrit le : 26/10/2007

# Le 30/12/2016 à 21:21

Bonjour,

Ma dernière question de l'année (j'espère).
J'ai une table auteurs (id, nom_auteur) et une table livre (id, id_auteur, nom_livre)

Je souhaiterais faire une requête qui récupère pour chaque auteur 2 livres parmi tous ses livres. Est-ce possible de faire cela en une seule requete, et sinon quelle serait la méthode la plus efficace ?

Merci d'avance
Vincent

http://www.vrdeveloppement.comOuvrir dans une nouvelle fenetre : réalisation de sites web et de logiciels personnalisés.

MichaelL | Michael
Membre

Photo de MichaelL

Inscrit le : 29/01/2009

# Le 31/12/2016 à 08:51

order by rand() limit 2; ?
Mais si je me souviens bien il parait que c'est assez lent. Ca ne devrait pas changer grand chose si tu extraies tous les livres et que tu fasses la sélection aléatoire après, en PHP par exemple. S'il y a un traitement ultérieur bien sûr.

jambondardennes | Stéphan
Membre

 

Inscrit le : 01/07/2011

# Le 31/12/2016 à 11:18

Bonjour,

je proposes cela sous couvert de ne pas dire une grosse bêtise et en supposant que les champs nom_auteur et id_auteur correspondent :

SELECT *, COUNT(nomlivre) AS totalivre FROM auteurs INNER JOIN livre ON livre.id_auteur=auteurs.nom_auteur WHERE totalivre>1 ORDER BY RAND() LIMIT 100

Ce qui devrait donner la liste de 100 auteurs ayant au moins 2 livres avec tous les champs pour travailler dans une boucle et ne ressortir que ceux qu'on veut réellement.

Ensuite pour ce qui est de la lenteur, il suffit d'ajouter un INDEX sur la table livre à id_auteur par exemple ce qui aura normalement l'effet d'améliorer le temps de recherche de façon optimale.

Par contre je crois qu'il faut à chaque modification de la table livre supprimer l'index et le recréer.

Cordialement et en espérant avoir au moins donné une piste de travail

https://www.panier-plus.comOuvrir dans une nouvelle fenetre

vincir | Vincent
Membre

 

Inscrit le : 26/10/2007

# Le 31/12/2016 à 17:51

Le problème est que je peux avoir des auteurs avec 100 ou 200 livres (mon cas n'est pas sur auteur / livre, mais le principe est le même). Donc je ne souhaitais pas récupérer 1000 enregistrement pour n'en retenir que 20 au final.
Après si l'auteur n'a écrit qu'un livre, je le veux aussi, mais avec son livre.

Pour info voici ce qui donnerait le bon résultat, mais qui est affreux en terme de temps dès qu'un auteur a trop de livre

SELECT auteurs.id, auteurs.nom, livre1.nom, livre2.nom
FROM auteurs
JOIN livres AS livre1 ON livre1.id_auteur=auteurs.id
LEFT JOIN livres AS livre2 ON livre2.id_auteur=auteurs.id AND livre1.id<livre2.id
GROUP BY auteurs.id
LIMIT 10

Seulement il va faire une groupe by sur des milliers de lignes, donc c'est très lent.

La seconde solution serait de récupérer mes 10 auteurs, puis de faire 10 requêtes pour récupérer les 2 livres de chaque auteur (pas optimal, surtout si je veux ensuite afficher 100 auteurs).

Sinon la dernière solution que je vois serais de faire une tache cron qui associe deux livres à un auteur pour ensuite avoir une requête simple et rapide. Mais comme c'est un problème que je rencontre souvent, j'avais posé la question au cas ou quelqu'un aurait une solution.

http://www.vrdeveloppement.comOuvrir dans une nouvelle fenetre : réalisation de sites web et de logiciels personnalisés.

Bool | Olivier
Modérateur

Photo de Bool

Inscrit le : 09/05/2005

# Le 01/01/2017 à 15:34

Hello,

c'est surtout la seconde jointure sur "livres" qui pose problème ici : sans condition de jointure claire, elle fait quasiment un produit cartésien de tous les livres d'un auteur.

Si tu veux juste 2 livres pour l'auteur, peu importe lesquels, une approche possible :

SELECT a.id, a.nom, min(l.id) as premierLivre, max(l.id) as secondLivre
FROM auteurs a
JOIN livres l ON l.id_auteur = a.id
GROUP BY a.id

daevel : infogérance et conseilOuvrir dans une nouvelle fenetre

vincir | Vincent
Membre

 

Inscrit le : 26/10/2007

# Le 01/01/2017 à 21:21

Oui le double join pose problème.
Pour ta solution, le problème est que je n'ai que l'id des deux livres, pas le titre. Donc je dois derrière refaire une requête pour récupérer les noms des livres. Par contre on arrive au résultat en deux requêtes, ce qui est déjà mieux. Merci pour l'idée

http://www.vrdeveloppement.comOuvrir dans une nouvelle fenetre : réalisation de sites web et de logiciels personnalisés.

Julgates | Julien
Administrateur

Photo de Julgates

Inscrit le : 09/03/2005

# Le 01/01/2017 à 23:24

Met un champ cache_2livres dans ta table auteurs que tu met a jour a intervalle regulier

Shopping Time NetworkOuvrir dans une nouvelle fenetre - Founder / CTO

JeromeF | Jérôme
Membre

 

Inscrit le : 10/05/2005

# Le 02/01/2017 à 08:40

En fonction de ta base et de l'archi, une autre solution serait
- ajouter une colonne "selected" (boolean) sur la table livres, avec un index sur cet attribut.
- créer un cron qui met à jour cet attribut selon ta règle métier (2 livres par auteur à true).
- ajouter ce critère à ta requête dans le where livre.selected = true

tonguide | Jeremy
Modérateur

 

Inscrit le : 09/05/2005

# Le 02/01/2017 à 10:31

Pourquoi absolument vouloir faire ça en 1 requête ? Quel intérêt ? Je préfère X requêtes courtes, rapides, facile à optimiser, qu'une grosse qui sera impossible à optimiser le jour où elle sera en slow query.

Bref, il y a des solutions en ayant un SETTER, j'utilise jamais cette technique (ou presque).
Après, dans ton cas, si ce n'est que 2 livres (non valable si tu en voulais 10), mais que tu veux avoir un peu de contrôle sur le choix des lives, je le ferai directement en 3 requetes avec un peu de PHP entre les 2.

1 : requete auteurs : de base pour chercher tes auteurs (sans liaision aucune avec les livres)
2 : PHP : tu remplis un tableau avec les id des auteurs
3 : requete livre 1 : avec un implode(',',$ids) dans un id_auteur IN (...) + les éventuels critères pour choisir le livre (et order comme tu veux RAND() ou top_vue DESC)
4 : PHP : tu remplis un second tableau avec les id du premier livre
4 : requete livre 2 : avec le premier "IN implode" + le seconde en id NOT IN (...implode($id_premier_livres)...)

3 requetes très basique, sur des index et des PK ... théoriquement très rapide.

Une solution de plus ;)

vincir | Vincent
Membre

 

Inscrit le : 26/10/2007

# Le 02/01/2017 à 12:51

Merci à tous pour vos réponses.
Pour le tout en une requête, c'est parce que ça me semble plus simple et plus optimisé si il y a un moyen de faire simple. Ca évite de multiple aller - retour avec le serveur bdd. Après effectivement si la requête doit prend une demi seconde à s'exécuter, autant faire trois petites requêtes rapides.

Comme c'est un problème récurrent chez moi, je voulais savoir si il existait une solution simple pour cela. Sinon effectivement je vais me pencher sur la solution de la cron.

http://www.vrdeveloppement.comOuvrir dans une nouvelle fenetre : réalisation de sites web et de logiciels personnalisés.

tonguide | Jeremy
Modérateur

 

Inscrit le : 09/05/2005

# Le 02/01/2017 à 16:46

Disons qu'une requete complexe, même optimisé, va rapidement prendre 5/10ms, alors que 3 requetes très light sur des index et PK ne vont pas dépasser les 0.5ms, le calcul est vite fait, même avec un peu de traitement PHP entre les 2. Et surtout, ça reste basique à comprendre.

JeromeF | Jérôme
Membre

 

Inscrit le : 10/05/2005

# Le 03/01/2017 à 08:01

@tonguide: c'est un point de vue, tout dépend de la stratégie de cache et l'utilisation des données ...

Avec du cache de résultat sur les requêtes, la toute première complexe va prendre 5/10ms, et ensuite quasi 0ms grâce au cache.
Et en option, tu auras implémenté un "bruleur de cache", c'est à dire un bot qui crawle ton site pour générer tous les caches à tous les niveaux, et donc les 5/10 ms c'est pour ton bot, et tes users ensuite ils ont un quasi 0ms sur ta requête.

Ensuite si on est sur de la donnée personnelle, ou fréquemment mise à jour, en effet il vaut mieux 3 fois 0.5ms.
Si on est sur une page ou fonctionnalité peu fréquentée, on peut aussi laisser tomber et passer du temps à optimiser sa HP à la place.

Tout dépend, il n'y a pas de vérité absolue et les calculs ne sont pas si vite fait.

Bool | Olivier
Modérateur

Photo de Bool

Inscrit le : 09/05/2005

# Le 03/01/2017 à 09:39

Et on ajoute à quel moment les 0.2ms de latence par requête SQL, parce que le MySQL est déporté sur une autre machine ?
(idem avec memcached & redis d'ailleurs)

Ok ok je sors

daevel : infogérance et conseilOuvrir dans une nouvelle fenetre

JeromeF | Jérôme
Membre

 

Inscrit le : 10/05/2005

# Le 03/01/2017 à 11:28

;)

tonguide | Jeremy
Modérateur

 

Inscrit le : 09/05/2005

# Le 03/01/2017 à 11:35

Dès lors que je peux faire sans cache, je fais sans cache en ce qui me concerne.
D'autant que le cache peut rapidement devenir lui même gourmand si on en fait trop.
Evidemment, il arrive qu'on n'ait pas le choix ;)
J'ai manqué de nuance

krucial | Jean Christophe
Administrateur

Photo de krucial

Inscrit le : 09/03/2005

# Le 03/01/2017 à 12:10

A ta place, je fais une table "selection_livre", j'y mets deux ID de livre par auteur, il y a plus qu'a taper dedans, et une cron qui passe toutes les heures pour mettre a jour les livres. Simple, rapide, pas gourmand et sans php dans ta page.

JC - Mes sitesOuvrir dans une nouvelle fenetre | Affiliation devis travauxOuvrir dans une nouvelle fenetre

ratfou | Raphaël
Membre

Photo de ratfou

Inscrit le : 27/09/2008

# Le 03/01/2017 à 18:49

comme krukru

Homestay in ParisOuvrir dans une nouvelle fenetre
Get Free BitCoinsOuvrir dans une nouvelle fenetre

tonguide | Jeremy
Modérateur

 

Inscrit le : 09/05/2005

# Le 04/01/2017 à 12:18

La version avec le setter dont je parlais au dessus, si ça peut te servir :


SELECT id_auteur, nom_livre
FROM
(
SELECT id, id_auteur, nom_livre,
@increment := if(@group = id_auteur, @increment + 1, 1) as number_of_row,
@group := id_auteur
FROM
-- initialise les variables
(SELECT @increment := 0, @group := '') init_var,
livre
ORDER BY id_auteur
) as a
WHERE a.number_of_row <= 2;


Je te laisse la lire, c'est pas bien compliqué à comprendre.

Par contre, comme je le disais, j'utilise jamais, donc en terme de perf, j'ai aucune idée de ce que ça vaut. D'autres seront peut-etre mieux ?

vincir | Vincent
Membre

 

Inscrit le : 26/10/2007

# Le 04/01/2017 à 17:07

Pour mon cas présent, je suis parti sur la solution de bool, avec deux requetes. Le client n'avait pas la possibilité de faire des crons sur son hébergement, donc pas trop le choix.

http://www.vrdeveloppement.comOuvrir dans une nouvelle fenetre : réalisation de sites web et de logiciels personnalisés.

Répondre

Vous ne pouvez pas participer au forum, car votre inscription n'a pas été validée. Pour vous faire valider en tant que Membre, cliquez ici.

© MHN - Tous droits réservés | CNIL N°844440 | 30/03/2017 4:35:39 | Généré en 8.37ms | Contacts | Mentions légales |