Recherche multiple de tags (MySQL)

[holoduke]

Bonjour,

Sur mon site, j'ai un système de tags à cocher pour effectuer une recherche de produits sur mon site, présenté comme ceci :

+ Couleur + Taille + Style
*tag *tag *tag
*tag *tag *tag
*tag *tag *tag

(en colonnes / type de tags)

Je veux faire en sorte que le visiteur puisse, par exemple cliquer sur 2 couleurs et 1 style et avoir comme résultats les produits ayant une des deux couleurs ET le style coché. Si le visiteur choisit deux couleurs et deux styles, le résultat doit contenir les produits ayants au moins 1 couleur ET 1 style correspondant.

Après pas mal de recherches, je n'ai trouvé qu'une solution mais très peu valable (30sec / requête, trop long surtout pour une solution AJAX... )

Code :

1
2
3
4
5
6
SELECT   p.products_id
FROM products p
WHERE products_id IN
( SELECT ttp.products_id FROM tags_to_products ttp WHERE  tags_id = 581 OR tags_id = 567 ) AND
products_id IN
( SELECT ttp.products_id FROM tags_to_products ttp WHERE  tags_id = 291);

Avez-vous une meilleure solution à m'indiquer ? Merci !

[dehorter olivier]

Pensez à utiliser la balise "CODE"

Pourquoi ne pas utiliser les jointures ?

Code :

1
2
3
4
SELECT P.Products_id
  FROM Products P
    INNER JOIN Tags_to_products Ttp ON Ttp.Products_id = P.Products_id
WHERE Ttp IN (581, 567, 291)

[aieeeuuuuu]

Bonjour

@dehorter olivier : attention, cette requête renverra les produits qui valident UN critère parmi les trois, et non un critère sur la couleur ET le critère sur le style.
Il faudrait pour cela deux jointures (dans l'exemple donné), et en fait trois dans le cas général : joindre la table Tags_to_products pour chaque type de tag.

pour l'exemple donné, cela donnerait :

Code SQL :

1
2
3
4
5
6
7
8
9
 
SELECT P.Products_id
FROM Products P
INNER JOIN Tags_to_products couleur
    ON couleur.Products_id = P.Products_id
    AND couleur.tags_id IN(581,567)
INNER JOIN Tags_to_products style
    ON style.Products_id = P.Products_id
    AND style.tags_id IN(291)

Pour le cas plus général, cela dépend de la façon dont vous passez les paramètres a votre requête (construisez vous dynamiquement la requête ?)

Cependant, cela ne reste qu'une légère optimisation : remplacement de vos IN par des jointures. Et 30 secondes pour votre requête, cela me parait très long. Je suspecte un index manquant, certainement sur la colonne tags_id (qui pourrait inclure products_id pour être couvrant)

Enfin, je suppose que vous voulez par la suite récupérer d'autres informations que le product_id, sinon la table products me semble inutile dans votre requête

[dehorter olivier]

oups

[holoduke]

aieeeuuuuu je vais tester ta méthode ça me semble pas mal ! Je ne savais pas que l'on pouvais faire 2 INNER JOIN sur la même table. Merci !

dehorter olivier, merci mais ta méthode s'applique uniquement si on souhaite que tous les tags soient cherchés avec des OU entre eux, sans prendre en compte les colonnes.

[holoduke]

aieeeuuuuu je confirme, ça marche très bien!

[holoduke]

Bonjour à tous,

Je cherche à faire une requête MySQL permettant de chercher des produits par leurs tags, sur une base osCommerce trafiquée.
Deux tables sont concernées: products et tags_to_products (qui lie les tables products et tags par leurs id)

Pour être plus précis, j'ai plusieurs catégories de tags qui se présentent ainsi:

Couleur
| Rouge
| Jaune
| Bleu

Matière
| Fer
| Bois
| Acier

etc...


Chaque tags présentant une checkbox, l'utilisateur peu en choisir plusieurs de catégories différentes.
Mon problème: Si l'utilisateur choisit plusieurs tags dans des catégories différentes.
Mettons que l'utilisateur choisissent Rouge, Jaune et Acier.
J'aimerais que les résultats donnent les produits qui on soit la couleur Rouge, soit la couleur Jaune, soit Rouge et Jaune mais qui soient obligatoirement en Acier.
Pour résumé, mon but est que les produits obtenus aient au moins 1 tags de chaque catégories sélectionnées qui corresponde.

J'ai trouvé un moyen mais qui alourdi considérablement la requête, en faisant plusieurs INNER JOIN de la table tags_to_products, autant qu'il a de catégories de tags à trier.

Voilà, je ne sais pas si j'ai été assez clair sur mes intentions ou sur ma description, mais je pense que la nature du problème doit connue et sa résolution aussi. Si vous souhaitez plus d'informations n'hésitez pas...

[holoduke]

Finalement j'ai à nouveau un problème, la requête est extrêmement lourde et fait fréquemment planter le serveur! Est-il possible de l'optimiser?

[punkoff]

Bonjour,

Manque la structure des tables, la requete que vous effectuez, les indexs.

[holoduke]

Envoyé par punkoff

Bonjour,

Manque la structure des tables, la requete que vous effectuez, les indexs.

C'est mieux expliqué dans le lien que j'ai posté, merci d'avance.

[punkoff]

non j'ai bien lu votre lien et aucune des 3 choses que je vous demande n'est fournit / explicité.

aujourd'hui vous utilisez une requete, on ne sait pas laquelle c'est (celle de aieeuuuuu ?) .
On ne connait pas la structure des tables.

vous vous plaignez de lenteur => on ne connait pas les indexs.

[Waldar]

Normalement votre couple (Products_id, Tags_Id) doit être une clef primaire de la table Tags_to_products et de facto être indexée.
Ça devrait répondre rapidement.

Ici j'aurai utilisé des existences plutôt que des jointures ou des IN :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT Prd.Products_id
  FROM Products AS Prd
 WHERE EXISTS (SELECT NULL
                 FROM Tags_to_products AS Tp1
                WHERE Tp1.Products_id = Prd.Products_id
                  AND Tp1.tags_id IN (581,567))
   AND EXISTS (SELECT NULL
                 FROM Tags_to_products AS Tp2
                WHERE Tp2.Products_id = Prd.Products_id
                  AND Tp2.tags_id = 291)

Quelles sont les volumétries de vos tables ?

[holoduke]

Waldar, je viens de tester cette solution mais là ça plante pour de bon.
La table products contient + ou - 10000 produits, la table tags_to_products + ou - 30000 entrées

La requête dans son état actuel dure 17 secondes.

[Rei Ichido]

Avec les index naturels, ça devrait passer sans le moindre problème ... Les 17 secondes sont-elles sur la base, ou depuis le site ?

Car si les requêtes génèrent de très grosses listes de résultats, le problème n'est plus le temps de traitement de la requête, mais la mémoire occupée côté serveur par la liste des 10 000 produits + le temps de communication BDD - Serveur puis Serveur - Browser par Ajax.
D'ailleurs, est-il vraiment intéressant pour qui que ce soit d'avoir une liste de 10 000 produits affichés ?
Pouvez-vous essayer de rajouter une condition de limite du nombre de résultats et voir si ça améliore le temps sur le site ?

Dernier point : la requête mentionnée ne comporte que les id. Est-ce que par hasard derrière il y aurait une boucle qui irait, à partir de l'id, chercher les valeurs de chaque produit ? Auquel cas, il faudrait simplement requêter en une fois les valeurs souhaitées pour l'affichage.

[holoduke]

Les 17 secondes sont sur la base uniquement, c'est le slow error log qui me le donne.

La requête génère peu de résultat (0->200 admétons), les chiffres que j'ai donné correspondent aux nombre d'entrées sur les tables.
Du coup est-il toujours intéressant de limiter le nombre de valeurs à retourner?

[Waldar]

Vous le faites tourner sur quelle machine votre MySQL ?
Parce que là c'est risible quand même les performances vue la volumétrie.

J'ai reproduis le cas sur Oracle.
100.000 produits, 2 millions de tag.

La requête dure 200 millisecondes.

Vous n'avez pas répondu sur les contraintes et index de vos tables.

[holoduke]

Alors en effet je viens de me rendre compte que la table tags_to_products n'a plus d'index ni de clé primaire

Je connais la sortie...

Si la requête pose toujours problème je reviendrais vers vous mais je ne pense pas...

[Waldar]

L'important c'est d'avoir résolu votre problème.
Dites-nous ce que ça donne !