Besoin d'aide sur une requete SQL

06 juin 2007, 15:47

Bonjour,

Mon site présente des groupes avec leur discographie. Sur la page de chaque album on peut noter l'album sur 7 étoiles. Si l'album a une de note de 5 ou plus, une liste de 5 autres albums qui ont étés notés de maniere similaire par un meme membre apparaît également sur la page.

ex: si X a voté 5 sur album1 et 6 sur album2, la page d'album1 affichera album2 dans sa liste.
La requête SQL est la suivante:

Code : Tout sélectionner

"SELECT a.id, a.Nom, a.Cover, a.Date, g.Groupe, u.username, v.vote as thisvote, p.vote as thatvote 
FROM user_votes v, user_votes p, albums a, rela_groupe_album r, groupes g, phpbb_users u 
WHERE v.alb_id='$ALB_ID' AND v.vote IN (5, 6, 7) AND p.user_id = v.user_id AND 
           p.alb_id != '$ALB_ID' AND p.vote IN (5, 6, 7) AND a.id = p.alb_id AND r.album_id = a.id AND 
           g.id = r.groupe_id AND u.user_id = v.user_id 
GROUP BY p.alb_id 
ORDER BY RAND() LIMIT 0, 5"

Le système fonctionne bien. Mais n'est pas très intéressant, vu que le site comprend des milliers d'album et des dizaines de milliers de votes.

J'aimerais donc optimiser cette requête pour qu'elle affiche cette liason en ordre decroîssant en rapport au nombre de membres qui ont voté sur deux albums de manière similaire.

Ex: X, Y et Z ont tous donné une note similaire à album1 et album2. X et Y ont donné une note similaire à album1 et album3. Seulement X a donné une note similaire à album1 et album4.

L'ordre serait donc album2, album3, album4

J'espère avoir été suffisament clair.
Est-ce possible en une seule requête? Si oui , comment ?

Merci d'avance.

06 juin 2007, 16:21

Just un mot pour dire que l'utilisation de jointures sous forme de virgules rend la lecture des requêtes très difficiles car elle pousse les conditions de jointure dans la clause WHERE. Si tu réécris ta requête pour utiliser "JOIN" (synonyme de "INNER JOIN", synonyme des virgules) tout devient plus clair

Code : Tout sélectionner

SELECT a.id, a.Nom, a.Cover, a.Date, g.Groupe, u.username, v.vote as thisvote, p.vote as thatvote 

FROM user_votes v
JOIN user_votes p        ON p.user_id = v.user_id
                        AND p.alb_id <> v.alb_id
                        AND p.vote IN (5, 6, 7)

JOIN albums a            ON a.id = p.alb_id
JOIN rela_groupe_album r ON r.album_id = a.id
JOIN groupes g           ON g.id = r.groupe_id
JOIN phpbb_users u       ON u.user_id = v.user_id

WHERE v.alb_id = '$ALB_ID'
  AND v.vote IN (5, 6, 7)  

GROUP BY p.alb_id 
ORDER BY RAND()
LIMIT 5

Pour le reste... c'est un problème compliqué donc très difficile à dire sans connaitre les données de la base. Exécute un EXPLAIN de ta requête et publie le résultat, ça peut aider.

Je vois que les données des tables a, r, g et u n'influe pas sur les résultats de la requête, donc tu devrais pouvoir faire le plus gros de la requête sous la forme d'une table dérivée (v,p) et joindre le résultat aux tables auxilliaires. De plus, vérifie que tu possèdes un index (probablement UNIQUE, d'ailleurs) sur (user_id, alb_id) ainsi que sur (alb_id) ou peut-être même sur (alb_id, vote) selon la répartition des votes (utile s'il y a une faible proportion de 5, 6 et 7, sinon oublie).

À part ça, j'ai un peu de mal à m'imaginer ce que tu cherches à faire (pour dire la vérité je ne suis pas super motivé non plus, essaie de le formuler sous la forme d'une requête si tu veux attirer mon attention

), est-ce que la requête ci-dessus fait ce que tu veux obtenir ? Sinon, en quoi diffère-t'elle de ce que tu souhaites obtenir ? (en termes fonctionnels si possible, merci)

06 juin 2007, 16:27

Au passage, voici la même requête réécrite pour utiliser une table dérivée. Elle devrait retourner les même résultats tout en étant plus rapide. Si mes souvenirs sont bons, les tables dérivées sont possibles à partir de MySQL 4.1

Code : Tout sélectionner

SELECT a.id, a.Nom, a.Cover, a.Date, g.Groupe, u.username, tmp.thisvote, tmp.thatvote 
FROM (
        SELECT v.alb_id, v.user_id, v.vote as thisvote, p.vote as thatvote
        FROM user_votes v
        JOIN user_votes p ON p.user_id = v.user_id
                         AND p.alb_id <> v.alb_id
                         AND p.vote IN (5, 6, 7)

        WHERE v.alb_id = '$ALB_ID'
          AND v.vote IN (5, 6, 7)  

        GROUP BY p.alb_id 
        ORDER BY RAND()
        LIMIT 5
) AS tmp
JOIN albums a            ON a.id = tmp.alb_id
JOIN rela_groupe_album r ON r.album_id = a.id
JOIN groupes g           ON g.id = r.groupe_id
JOIN phpbb_users u       ON u.user_id = tmp.user_id

06 juin 2007, 16:38

Bon alors deja. Merci bcp pour ta réponse. Je ne connaissais pas encore ce principe (j ai "appris" le SQL a la volé, selon mes besoins, donc optimisation = 0).

Des que je serais chez moi, je tenterai de mettre cette requete sur le site et j essairai de formuler la requete que je voulais.

Merci encore.

06 juin 2007, 16:53

Et si tu ne trouves toujours pas ton bonheur, il y a plus bourrin encore : les VIEW et les TEMPORARY TABLE...

06 juin 2007, 22:49

Je n'ai pas encore essayé la requete, je vais m'y appliquer de suite. Cela dit, j'avais cru comprendre que de mettre plusieurs select a la suite

SELECT a.* (SELECT blah blah)

était une méthode extremement lente. Mes sources étaient donc fausses?

@ Sékiltoyai: Je pense que comme ca , ca ira. Mais merci, j'y jetterai un oeil tout de même.

06 juin 2007, 23:39

Ce sont en fait deux choses différentes. Tu peux mettres des sous-requêtes un peu où tu veux, si tu en mets dans la liste des colonnes à récupérer alors oui ça va être lent parce que ça équivaut à exécuter une requête supplémentaire par ligne de résultat (10 résultats = 10 + 1 requêtes). Les requêtes dans la clause FROM sont appelées "tables dérivées" et ne sont exécutées qu'une seule fois.

Dans l'exemple précis, au lieu d'avoir une requête qui tire 5 enregistrement au hasard sur une jointure de 6 tables sur des milliers d'enregistrements on a une jointure de 2 tables sur des milliers d'enregistrement, puis une jointure de 5 tables sur 5 enregistrements. Pour donner une estimation de la complexité, au lieu d'avoir 6 x 1000 on a 2 x 1000 + 5 x 5.

07 juin 2007, 00:23

Re

Oki, je comprend mieux maintenant! Merci pour l'explication très claire. Ca va m'aider sur d'autres requetes aussi! (Tu avais oublié un FROM dans ton exemple de requête, donc je ne comprenais pas grand chose

)

Voici donc la version optimisée de la requête originale (testée et approuvée)

Code : Tout sélectionner

"SELECT a.id, a.Nom, a.Cover, a.Date, g.Groupe, u.username, tmp.thisvote, tmp.thatvote
FROM
( 
        SELECT v.alb_id, v.user_id, v.vote as thisvote, p.vote as thatvote, p.alb_id as thatid
        FROM user_votes v
        JOIN user_votes p ON p.user_id = v.user_id 
                         AND p.alb_id <> v.alb_id
                         AND p.vote IN (5, 6, 7) 

        WHERE v.alb_id = '$ALB_ID' 
          AND v.vote IN (5, 6, 7)

        GROUP BY p.alb_id
        ORDER BY RAND()
        LIMIT 5
) AS tmp
JOIN albums a            ON a.id = tmp.thatid
JOIN rela_groupe_album r ON r.album_id = a.id
JOIN groupes g           ON g.id = r.groupe_id 
JOIN phpbb_users u       ON u.user_id = tmp.user_id"

Bon ca fait une heure que j'essaye là donc je vais me permettre de vous demander:

Vous voyez le GROUP BY: je veux savoir combien d'entrées sont touchées à chaque fois par cette partie (donc en non-SQL, savoir combien de membres ont donné une note de plus de 4 a ces deux albums) et mémoriser ce nombre, pour ensuite faire un ORDER BY sur toute la requête par rapport a cette valeur.

Voilà. J'ai du mal (peut etre la fatigue) a voir comment réaliser cela, sans ajouter encore une requete.

07 juin 2007, 00:41

Si j'ai bien compris, tu veux trouver les albums les plus appréciés par les utilisateurs qui ont apprécié un album donné.

En entrée tu as le numéro d'album "v" et en sortie une liste d'albums "p" qui ont reçu une bonne note par les utilisateurs qui ont donné une bonne note à "v". Ça donnerait quelque chose comme

Code : Tout sélectionner

SELECT p.alb_id, COUNT(*) AS cnt
FROM user_votes v 
JOIN user_votes p ON p.user_id = v.user_id 
		 AND p.alb_id <> v.alb_id 
		 AND p.vote IN (5, 6, 7) 

WHERE v.alb_id = '$ALB_ID' 
  AND v.vote IN (5, 6, 7) 

GROUP BY p.alb_id
ORDER BY cnt DESC
LIMIT 5

En revanche impossible de récupérer en même temps les infos utilisateurs puisque les résultats sont groupés par album. Tu pourrais utiliser GROUP_CONCAT() [spécifique à MySQL] pour récupérer la liste des utilisateurs puis utiliser cette liste en PHP pour récupérer leurs noms dans une nouvelle requête, mais l'un dans l'autre ça ne paraît pas très utile. Enfin, à toi de voir.

Code : Tout sélectionner

SELECT p.alb_id, COUNT(*) AS cnt, GROUP_CONCAT(p.user_id) AS user_ids
FROM ...identique...

07 juin 2007, 01:03

Code : Tout sélectionner

"SELECT a.id, a.Nom, a.Cover, a.Date, g.Groupe, tmp.cnt
FROM
( 

               SELECT p.alb_id, COUNT(*) AS cnt
               FROM user_votes v
               JOIN user_votes p ON p.user_id = v.user_id
               AND p.alb_id <> v.alb_id 
               AND p.vote IN (5, 6, 7)
        
               WHERE v.alb_id = '$ALB_ID'
               AND v.vote IN (5, 6, 7)
        
               GROUP BY p.alb_id
               ORDER BY cnt DESC
               LIMIT 5


) AS tmp
JOIN albums a            ON a.id = tmp.alb_id
JOIN rela_groupe_album r ON r.album_id = a.id
JOIN groupes g           ON g.id = r.groupe_id
"

Fonctionne parfaitement.
Oui quand j'y pense c'était un peu bête de vouloir lister également les membres. Je m'emmelais légèrement les pinceaux là.

Cela dit, GROUP_CONCAT(xxx) AS yyy pourrait régler un autre problème qui me tracasse depuis longtemps!

Merci encore!

07 juin 2007, 01:25

Le résultat final dans son contexte: http://www.crypticmadness.com/Metal/album.php?id=74

07 juin 2007, 01:33

Content que ça marche. Au passage, tu pourrais me donner le résultat d'un EXPLAIN ainsi que le schéma des tables stp ?

07 juin 2007, 01:46

Excuse mon ignorance, mais comment je fais? Ca se fait directement à l'intérieur du script en php ou depuis la console?

07 juin 2007, 01:53

Plutôt dans phpMyAdmin, ou mieux encore dans le client MySQL si tu testes en local, mais c'est à toi de voir. Tu prends ta requête, tu rajoutes "EXPLAIN" devant et tu exécutes le tout. Ça te renvoit un tableau qui ressemble à ça :

Code : Tout sélectionner

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  f       ALL                     27                                      Using filesort
1       SIMPLE  p       eq_ref  PRIMARY PRIMARY 3       test.f.forum_last_post_id   1       
1       SIMPLE  u       eq_ref  PRIMARY PRIMARY 3       test.p.poster_id    1

07 juin 2007, 01:56

Code : Tout sélectionner

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 
1 PRIMARY a eq_ref PRIMARY PRIMARY 4 const 1 Using where 
1 PRIMARY r ref groupe_id,album_id album_id 4 gorghor.a.id 12   
1 PRIMARY g eq_ref PRIMARY PRIMARY 4 gorghor.r.groupe_id 1   
2 DERIVED v ref user_id,alb_id,vote alb_id 5   1 Using where; Using temporary; Using filesort 
2 DERIVED p ref user_id,alb_id,vote user_id 5 gorghor.v.user_id 2 Using where

c'est ca que tu voulais?

Nos posts se sont croisés

Besoin d'aide sur une requete SQL

Qui est en ligne