Pertinence instruction SQL

Version imprimable

Bonjour à tous, je boss actuellement sur mon moteur de recherche qui compte déjà pas mal d'option et basé sur un système LIKE / REGEXP et non FULLTEXT :-°

Donc voilà pour l'instant j'ai ajouté pas mal d'options et je me vois mal les convertir pour être compatible avec le FULLTEXT surtout que je n'en ai pas bien compris le fonctionnement et que complètement changer la structure de mon code ne m'intéresse pas trop :D

Donc je voulais ajouter un système de ORDER BY par pertinence des résultats, n'étant pas très balèze en SQL j'ai trouvé une méthode vraiment pas mal qui fonctionne mais que j'aimerais améliorer :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT *, ((CASE WHEN CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé1%' THEN 3 ELSE 0 END) + (CASE WHEN CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé2%' THEN 3 ELSE 0 END) + (CASE WHEN CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé3%' THEN 3 ELSE 0 END)) AS `pertinence` FROM `sujets` WHERE ( CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé1%' OR CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé2%' OR CONCAT(`titre`," ",`desc`," ",`intro`," ",`page`) LIKE '%motclé3%') AND `ref` REGEXP '^[1234]' ORDER BY `pertinence` DESC
J'ai remplacé toutes les variables pour obtenir le code comme il pourrait apparaître dans mysql_query(), si sa peut vous aider à comprendre mon code la recherche est faite avec "motclé1 motclé2 motclé3" en mode "au moins un mot", sur toutes les parties du site et dans les pages entière. J'ai mis aussi des retour à la ligne pour éclaicir un peu tout ça ;)

Ça marche relativement bien : ça permet d'afficher en priorité les pages contenant le plus de mots clés et d'attribuer plus de valeur à certains mots clefs mais ne permet pas de savoir si un mot clef apparaît plusieurs fois dans une même page ce qui est problématique pour les recherches "tous les mots" ou "expression exacte"....

J'aimerais donc que au lieu de trouver si un mot est présent dans une page, qu'il trouve le nombre de fois qu'il apparaît... et je pense que la structure case ne le permettrait pas (au peut être en imbriqué) donc je fais appelle à votre connaissance en SQL pour peut-être m'aider... ^^

Merci à tous.
Zwify.

PS : j'utilise MySQL

26/02/2010, 13h48
CinePhil

Lis donc l'article de SQLPro sur l'indexation textuelle qui propose une démarche s'approchant de la tienne et grâce à laquelle il sera à mon avis plus facile d'obtenir des statistiques.

Sinon je crois que l'utilisation des idnex FULLTEXT et de l'instruction MATCH permet d'obtenir la fréquence d'un mot.
26/02/2010, 14h35
Zwify

Bonjour, tout d'abord merci de m'avoir répondu.

J'ai lu ce très, très bon (et complet) article de SQLPro, c'est très instructif je trouve et là on arrive dans un moteur de recherche vraiment très complet. :)

Je suppose que la partie programmation est dans un langage spécifique à SQL Server (J'utilise MySQL) même si ça ne m'a pas empêcher d'en comprendre le fonctionnement dans l'ensemble (très ingénieux :ange: )

Mon moteur de recherche ne gère pas les mots noirs (même si supprime les lettres isolées), fautes d'orthographes, et encore moins la recherche sémantique. :mrgreen: Mon moteur ne nécessitant pas de telles options et étant assez longues à mettre en œuvre.

Je cherche donc à trouver via une syntaxe MySQL le nombre de fois que chaque mot clé apparaît. :calim2:

Vu que personne n'a l'air de réussir à trouver une solution à mon problème, je boss sur une version de mon moteur qui importe le contenu de mes tables dans des arrays pour ensuite effectuer les opérations dans les array. Mais pour convertir mon moteur en PHP il faut que je change toute la structure de mon moteur :bug:

Merci, Zwify.
26/02/2010, 15h24
CinePhil

Citation:

Envoyé par Zwify

Je suppose que la partie programmation est dans un langage spécifique à SQL Server (J'utilise MySQL) même si ça ne m'a pas empêcher d'en comprendre le fonctionnement dans l'ensemble (très ingénieux :ange: )

Non. MySQL comme SQL Server utilisent tous deux le langage SQL... avec quelques adaptations qui parfois fonctionnent sur un SGBD et pas l'autre. C'est malheureusement le cas de tous les SGBD.
Mais SQLPro est avant tout un expert en SQL et ses articles, tout comme son livre, sont basés sur le SQL Normalisé, sauf exceptions généralement signalées.
J'ai parcouru l'article ce matin et je n'y ai rien vu qui ne fonctionnerait pas sous MySQL.

Citation:

Je cherche donc à trouver via une syntaxe MySQL le nombre de fois que chaque mot clé apparaît. :calim2:

Cherche du côté de l'index FULL TEXT et de l'instruction MATCH (pour le coup ce sera peut-être un peu du spécifique MySQL).

Citation:

je boss sur une version de mon moteur qui importe le contenu de mes tables dans des arrays pour ensuite effectuer les opérations dans les array.

Ouch ! 8O
Tu imagines si ta table grossit à plusieurs dizaines de milliers de lignes, voire encore en multipliant par dix ce nombre ?
C'est tout à fait supportable par un SGBD mais par un programme, j'en doute fort !
Fais faire au SGBD ce qu'il sait faire le mieux, ce pour quoi il est conçu : manipuler les données !
Le programme en PHP ou autre langage est davantage là pour gérer les actions de l'utilisation, préparer les requêtes, les envoyer au serveur et présenter le résultat après avoir éventuellement agit en fonction de ce résultat.

Citation:

Mais pour convertir mon moteur en PHP il faut que je change toute la structure de mon moteur

Ca veut dire qu'il n'est pas en PHP aujourd'hui ? N'y change rien s'il est opérationnel !
26/02/2010, 18h46
Zwify

J'ai lu pas mal de code et je me suis rendu compte que les FULLTEXT permettait un classement par pertinence sans forcément être attaché à une recherche.

Donc c'est ce qui me fallait et j'ai réussi à mettre tout ça en place :yaisse2:
et ça fonctionne effectivement plutôt bien :mrgreen:

Par contre il subsiste un petit problème : le mode boolean faussant complètement le classement par pertinence je ne l'utilise pas mais sans lui je ne peux malheureusement pas utiliser les opérateurs tels que * et " " ce qui me pose problème pour les singuliers/pluriels ou les recherches par expressions exactes.

Connaîtriez-vous une alternative ?

Merci, Zwify.

PS : quand je parlais de moteur de recherche en PHP, c'était plutôt recherche dans les bases faites par le PHP ce qui ne laissait plus grand chose à faire au SQL, mon moteur étant basé sur le PHP... J'avais commencé à reprogrammer mon moteur un peu comme ça mais comme tu le dis le PHP n'est pas très adapté à ce genre de recherche (bien que très puissant avec les expressions régulières) et que j'ai compris les classement par FULLTEXT je l'ai abandonné même si ça me servira sûrement plus tard quand je voudrais faire un affichage dans le context en aperçu :mouarf:
26/02/2010, 19h26
Zwify

Pour le problème du pluriel j'ai dédoublé à chaque fois les mots clefs avec un "s" en plus (ou en moins selon si il y en a déjà un ou pas) ce qui fait un peu bricolage...

Savez vous si il n'y a pas moyen de faire marcher les %,* ou autre expressions régulières (à l'image de REGEXP) dans AGAINST sans utiliser le BOOLEAN MODE...

Je trouve ça d'ailleurs assez bizarre que les opérateurs permettant d'attribuer plus de poids dans les opérations de pertinence ne marche que en BOOLEAN MODE qui est une version très simplifiée du calcul de pertinence :aie:

Zwify.

EDIT : Je crois que je vais laisser ça comme ça, merci CinePhil :D