Requête "optimisée" pour site de rencontre?

[Guizmo95]

Bonjour,

Je travail actuellement sur un site de rencontre et comme tout site de rencontre les utilisateurs peuvent rentrer un nombre important de critères.

Mon idée est la suivante sur la structure de la base :

Une table utilisateur (là tout est normal )
(id util, pseudo, idville....)

Une table de référence
idref (auto incrémenté)
nom_ref (exemple de valeurs possibles : yx vert, yx bleu, chv long, fumeur...)
type_ref (me permet sous php de savoir quelle information correspond à quoi. Exemple : Pour un utilisateur X pour lequel je souhaite afficher la couleur des yx (imaginons que type_ref = 2 pour les yeux), je vais prendre le nom_ref qui a le type_ref = 2 pour l'utilisateur X)


Une table intermédiaire
idutil
idref




Mon but est le suivant : utiliser un index sur un champ INT (en l'occurence idref) sur la table intermédiaire plutôt que de rechercher un champ texte

Exemple : si je recherche tous les utilisateurs qui ont les cheveux long je ferai (sachant que l'idref pour les cheveux long est 54)

Code :

1
2
3
SELECT idutil 
FROM tableInter
WHERE idref = 54

Ma question est la suivante :

Mais? si je recherche tous les utilisateurs qui ont les yx bleu ET les cheveux long ET qui fume... Quelle sera ma requête???

J'aurais voulu faire une requête la plus optimisée possible mais je doute que ce soit le cas actuellement avec cette structure? non?

Au secours!

Merci par avance pour les retours que vous pourrez m'apporter

[punkoff]

bonjour,

il va vous falloir une table avec des metadonnées (vous êtes bien partie niveau modélisation, mais il faut approfondir)

Ensuite la requête type pour ce genre de demande est :

Code :

1
2
3
4
5
6
7
 
SELECT idutil
FROM utilisateur a
INNER JOIN votre_table_reference b ON a.idutil = b.idutil
WHERE b.col_critere IN (X, Y, Z)
GROUP BY idutil
HAVING count(*) = 3

Où 3 est le nombre de critère recherché.
Un seul scanage de table (au pire) et vous aurez les personnes qui correspondent à ces critères.

[Guizmo95]

il va vous falloir une table avec des metadonnées (vous êtes bien partie niveau modélisation, mais il faut approfondir)

C'est à dire? une table avec des metadonnées? ce n'est pas ma table de référence ?

En tout cas un grand merci pour cette réponse

[punkoff]

Oui en quelque sorte !

http://sqlpro.developpez.com/cours/m...n/metadonnees/
http://www.developpez.net/forums/d11...s-index-objet/

Votre structure est mal au vu du besoin en fait, regardez trop vite hier soir.

Si Cinephil passe par là il aura peut être quelque chose à redire

[Guizmo95]

hummm? ok, je vais regarder ça de plus près. Merci pour les infos.

Par contre, pour le moment (après lecture "rapide") je ne vois pas en quoi la structure n'est pas correcte?

[punkoff]

j'ai oublié un mot dans la précédente phrase : votre structure est pas mal*

[Guizmo95]

Ok
Encore merci pour la réponse

[Guizmo95]

Donc pour retrouver tous les utilisateurs qui sont brun, aux yx bleu et de silhouette normal je peux utiliser cette requête :

Code :

1
2
3
4
5
6
SELECT idutil
FROM utilisateur a
INNER JOIN votre_table_reference b ON a.idutil = b.idutil
WHERE b.col_critere IN (1, 2, 3)
GROUP BY idutil
HAVING count(*) = 3

1 étant la couleur de cheveux "Brun"
2 étant la couleur des yeux "bleu"
3 étant le type de silhouette "normal"

Jusqu'ici tout va bien

Par contre, si je souhaite récupérer tous les utilisateurs :

qui sont "Brun" (1)
ET
qui sont "étudiant" (4)
ET
qui ont les yeux "bleu" (2) ou "marron" (5) ou "vert" (6)
ET
qui ont une silhouette "sportive" (7) ou "normal" (3)

La requête si dessus ne plus s'appliquer. De plus, faire des OR tout partout dans la requête risque d'être fastidieux

Avez vous une idée?

[punkoff]

Bonjour,

Là par contre je crois qu'on ne va pas couper aux sous-requêtes..

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT idutil
FROM utilisateur a
INNER JOIN votre_table_reference b ON a.idutil = b.idutil
WHERE b.col_critere IN (1, 4) 
AND EXISTS (SELECT NULL FROM votre_table_reference c
 WHERE a.idutil = c.idutil AND c.col_critere IN (2, 5, 6))
AND EXISTS (SELECT NULL FROM votre_table_reference c
 WHERE a.idutil = c.idutil AND c.col_critere IN (7, 3))
GROUP BY idutil
HAVING count(*) = 2

edit : hmm y a peut etre un contournement avec un case .. when dans la clause having ...

[Jean.Cri1]

Code :

1
2
3
4
5
6
7
8
9
10
select U_id
FROM utilisateur U
    ,ref_U       R
where 
      R.U_id = U.U_id
  and R.R_ref in (1,2,3,4,5,6,7)
group by U_id
having sum ( case when R_ref = 1 then  1 else 0 end ) = 1
   and sum ( case when R_ref in (2,5,6) then 1 else 0 ) = 1
   and ...

Et on peut éviter les doublons de R avec >= 1 au lieu de =1

Edit: on n'a pas besoin de la jointure et je n'ai pas trouvé comment barrer alors j'ai mis en gras souligné

[Guizmo95]

Merci pour la rapidité des réponses

Par contre les deux solutions étant correctes, laquelle des deux est la moins gourmande en ressource (si la base d'utilisateurs est très importante)?

Si je comprends bien le code de Jean.Cri1 récupère une liste d'utilisateurs ayant la globalité des caractéristiques demandé (même si l'utilisateur a que les yeux bleu). Puis tri les données avec des sum (case when...)

• le faite de faire autant de "calcul" (sum + case when) n'est pas trop demandeur en ressources?

• le faite de récupérer la totalité des utilisateurs qui ont au moins une valeur ne va pas me ramener de trop?

Et si je comprends bien pour le code de punkoff, je fait un premier tri sur les valeurs 'obligatoires' c'est à dire qu'il y a une seule valeur possible par catégorie (ei : seulement brun pour la couleur des cheveux)

Puis après ce premier tri, je récupère dans cette liste les utilisateurs qui correspondent à au moins un critère par catégorie (ei : bleu ou marron ou vert pour la couleur des yx)

• Cette solution me semble plus optimisée mais je n'en mettrais pas ma main à couper

Pour finir, une solution possible est de faire un mix entre les deux solutions :

• Premier tri sur les valeurs 'obligatoires'
• Puis tri grâce au sum(case when) au lieu du exist

Non?

[punkoff]

Pour les perfs ... il faut que vous analysiez les plans d’exécution des requêtes avec une base chargée.

[Jean.Cri1]

Tu peux aussi faire la requête de punkoff en 'total exists' ce qui devrait éviter le recours au group by ...

Je suis curieux de connaitre les résultats du benchmark, n'hésite pas à nous en faire part.

PS: j'ai retouché la requête que j'ai proposée

[Guizmo95]

Merci pour vos réponses, je ne manquerai pas de vous faire part du BENCH