Bonswère,
 
J'ai fait un mini moteur de recherche pour mon site (gallerie), et il serait interessant de le booster pour accroître sa pertinence. La merde que j'ai pondue va bien si l'utilisateur stipule un seul mot dans le champ de saisi, mais si il en met plusieurs... ça marche plus ou moins, voyons pourquoi:
 
en gros, si on résume mon moteur, c'est ça:

 
dans la table, il y a un champ qui s'appelle "alt" et qui contient des keywords en rapport avec une photo de la gallerie, par exemple:

 
1.donc si l'utiliseur tape UN SEUL MOT dans le input de recherche, par exemple "sport" ça va lui ressortir les 2 entrées. impeccable.
2.maintenant, si il tape "sport vitres" ça lui ressort la première entrée SI ET SEULEMENT il les saisi dans cet ordre. ce qui est infiniment probable. et en plus ça ne va pas lui retrouner l'entrée numéro 2 (la bmw) qui pourtant contient ces 2 mots.
3.pire, si il tape "sport clio" ça ne lui retournera AUCUN résultat! on voit bien pourquoi.
 
 
Voilà, donc l'idéal serait de faire un explode sur la saisie avec le caractère "espace" puis de traiter chacun de ces mots afin qu'il n'y ai pas cette contrainte d'ORDRE consécutif des mots pour que ça retourne un résultat.
 
Je ne sais pas comment faire ça proprement parce que la requete peut vite devenir malpropre et pas optimisée.
est-ce qu'une ébauche telle que la suivante est susceptible de fonctionner, et, surtout, est-ce optimisable?
 

 
 
en vous remerciant.  

Fait une table séparée avec des champs "id" et "alt", et dans le champ alt tu ne met qu'un seul mot.
Une requète la dessus + un join pour avoir le reste des infos et hop, tu n'aura plus ce genre de problèmes

oulah ça me semble bien bien bien crados ce que tu me proposes là.
en gros ce que tu me conseille c'est une entrée=un mot. non merci.    
 
Il doit être possible de faire celà plus proprement.

 
Pourquoi est-ce que tu trouves ça crado exactement ?

parce que je vais me retrouver avec 20 fois plus d'entrées et une requête encore plus lourde (jointure), alors que je cherche à compacter au maximum. ta méthode marchera mais c'est du bidouillage.
Et puis il est trop tard, j'ai tout bâti sur ce modèle, et y'a pas moins de 1500 entrées actuellement. si j'employais ta méthode, sachant qu'il doit y avoir +-20 mots-clés par articles: 1500x20=30000 entrées    
 
merci quand même pour la suggestion.  

T'aura 20x plus d'entrées mais tes entrées seront 20x plus petites.
Tu as une jointure pour avoir les infos autre que l'id mais en contrepartie tu ne fait qu'une seule requète.
 
Pour la conversion, il te faut un script qui lit l'ancienne table, tu explode le alt de chaque entrée, et tu insère chaque mot séparément dans la nouvelle, pas compliqué ...

Bien que je pense que la solution de 0x90 reste la meilleur et la plus logique, je donne quand même ce qu'il cherche. Du moins quelque chose qui s'en rapproche :

Le OR pouvant être remplacé par un AND selon besoins.
Et attention aux injections de SQL.

Edit : Franchement tu devrais prendre la solution de 0x90. Le code que je viens de donner CA c'est de la bidouille. Comme il l'a dit, transférer les données c'est pas compliqué et si tu évite les doublons tu dois pouvoir réduire le nombre d'enregistrement.

merci à vous.  
tu as oublié le mysql_query() avant le mysql_fetch_array().    
 
Vraiment, ça m'ennuierai de reprendre ce point. J'ai developpé un panneau admin qui repose également sur ça, ainsi que ma gallerie, donc il faudrait revoir des sacrés blocs de codes si je me re-fonde sur votre architecture. Et puis ce champ alt doit absolument contenir plusieurs mots en même temps, car je les récupère +-100 fois dans une même page, dans un autre but. avec une jointure je vous garantie que la requête serait énorme.    
Bref, vous auriez surement raison dans le cadre d'un moteur de recherche, mais ce champ 'alt' est repris également dans un autre contexte sur le site, donc dans ce cadre là la méthode que je cherche à employer est impeccable croyez moi.    
 
merci encore.

Exacte, pas fait attention    

Pas sûr ce soit normal ça... Enfin bon, tu fais comme tu veux.

parcours tout tes textes, splitte tout les mots d'apres certains characteres (, " ' ! - .) retire les accents, converti en minuscule, apres insere tout ses mots dans une table...
 
attention tu n'insere les mots qu'une fois...
il te faut egalement un table qui fait le lien entre un mot et une page de ton site
apres tu fait plus de like juste un = sur une seule colonne.... Il y a plein d'autre avantages, tu peut rajouter une colonne de points pour favoriser certains mots (ex : si c dans le titre de la page) et tu peut egalement parcourir la table des mots pour "proposer" un mot clé si lutilisateur n'a pas mit le mot clé avec la bonne orthographe...
 
en gros ca donne ca
 

 
puis l'autre table
 

Je suis en train de faire un bench sur les deux méthodes avec SQL Server 2005 (jeu de test de 1 000 000 lignes avec en moyenne 12.5 mots clés par ligne)
 
Je mise très largement sur le coup des jointures.
 
(par contre, ça prends du temps à fabriquer un tel jeu de test, y'aura pas de résultat avant cet après-midi )
 
PS : MySQL supporte aujourd'hui le PL/SQL (enfin, un dérivé) donc ce que j'arrive à faire avec SQL Server 2005 est parfaitement adaptable. Deplus, MySQL est réputé plus rapide que SQL Server, sans parler du fait que je tourne sur un ordinateur portable, donc pas forcément dans les meilleurs conditions pour un serveur de base de données En gros, si les jointures explosent (comme elles devraient) la grosse merde infâme qu'est la succession de mots clés sur ma config, y'a pas moyen de moyenner que t'arrive pas à un résultat similaire en MySQL.

ah c'est chouette, je suis le premier interessé. tiens nous au courant MagicBuzz.    
j'adore faire ce genre de tests moi aussi, on est tout de suite fixé.
 
 
j'espère que la succession de keywords fait mieux que les jointures parce que si je ne retouche pas mon architecture, je dormirais pas l'esprit tranquille, si je la retouche, c'est du boulot.

Bon, j'ai merdé le script de génération du jeu de test. Là il tourne, mais ça va pas vite du tout pour le remplir
 
Reste ensuite à créer les index...
 
Et je pourrai enfin donner le résultat.
 
A savoir que de toute façon, même sans faire le test, il est "évident" qu'avec les mots-clés ça sera plus rapide dans le mesure où on ne fait plus de like mais des =, ce qui permet d'entrée de jeu d'utiliser une colonne indexée, et nécessite bien moins de traîtements.

quel feinte , vous etiez tous persuadés que google faisait un like sur le contenu des pages?

C'est long à initialiser
 
En attendant, voici un début...
 
Script de la structure de la base :

CREATE TABLE alt1 (
    id numeric(18, 0) NOT NULL,
    alt varchar(8000) NULL,
 CONSTRAINT PK_alt1 PRIMARY KEY CLUSTERED 
(
    id ASC
)
)
GO
 
CREATE TABLE alt2 (
    id numeric(18, 0) NOT NULL,
 CONSTRAINT PK_alt2 PRIMARY KEY CLUSTERED 
(
    id ASC
)
)
 
CREATE TABLE alt2keys (
    id numeric(18, 0) NOT NULL,
    id2 numeric(18, 0) NOT NULL,
    keyword varchar(50) NOT NULL,
 CONSTRAINT PK_alt2keys PRIMARY KEY CLUSTERED 
(
    id ASC,
    id2 ASC
)
)
GO
ALTER TABLE alt2keys WITH CHECK ADD  CONSTRAINT FK_alt2keys_alt2 FOREIGN KEY(id2)
REFERENCES alt2 (id)
GO
ALTER TABLE alt2keys CHECK CONSTRAINT FK_alt2keys_alt2
GO
 
-- Cette table est inutile, elle ne sert qu'à créer le jeu de test. Elle contient 25 mots clés
CREATE TABLE keywords(
    id numeric(18, 0) IDENTITY(1,1) NOT NULL,
    keyword varchar(50) NOT NULL,
 CONSTRAINT PK_keywords PRIMARY KEY CLUSTERED 
(
    id ASC
)
)
GO
-- Ca va faire boum parceque à la base, un index ça peut pas dépasser 900 octets de profondeur
CREATE INDEX ix_keys1 ON alt1 (alt)
go
-- Et vu que ça dit ça
-- Warning! The maximum key length is 900 bytes. The index 
-- 'ix_keys1' has maximum length of 8000 bytes. For some 
-- combination of large values, the insert/update operation will 
-- fail.
-- Du coup, on le dégage...
DROP INDEX ix_keys1 ON alt1
go
CREATE INDEX ix_keys2 ON alt2keys (keyword)
go

 
Nota : On voit ici que je ne me base pas sur un dictionnaire, ceci afin de rester le plus proche possible de ta solution actuelle. Effectivement, entre la réécriture complète de ton truc, et les soucis de gestions d'un dictionnaire, je ne suis pas sûr du gain réel de passer par un dictionnaire. J'utilise donc une table alt2keys bourrée de mots en double, mais tant pis.
 
 
Ma petite fonction SPLIT() bien utile puisque dans SQL Server elle n'existe pas en natif :

CREATE FUNCTION split(@keywords AS varchar(8000))
returns @tmp TABLE (keyword varchar(50))
AS
begin
    while charindex(' ', @keywords, 1) > 0
    begin
       INSERT INTO @tmp VALUES (rtrim(LEFT(@keywords, charindex(' ', @keywords, 1))));
       SET @keywords = rtrim(RIGHT(@keywords, len(@keywords) - charindex(' ', @keywords, 1)))
    end
    INSERT INTO @tmp VALUES (@keywords);
    RETURN;
end
go;

 
 
Le script de création du jeu de test :

declare @i AS int;
declare @j AS int;
declare @m AS int;
declare @n AS int;
declare @str AS varchar(8000);
declare @KEY AS varchar(50);
 
SET @i = 0;
SELECT @m = count(*) FROM keywords;
 
while @i < 1000000
begin
  SET @str = '';
  SELECT @n = cast(rand() * @m AS int);
  SET @j = 0;
  INSERT INTO alt2 (id) VALUES (@i);
  while @j < @n
  begin
    SELECT @KEY = keyword FROM keywords WHERE id = cast(rand() * @m AS int);
    SELECT @str = @str + @KEY + ' ';
    INSERT INTO alt2keys (id, id2, keyword) VALUES (@j, @i, @KEY);
    SELECT @j = @j + 1;
  end
  INSERT INTO alt1 (id, alt) VALUES (@i, rtrim(@str));
  SELECT @i = @i + 1;
end

 
Voici les requêtes de test :

SELECT count(*)
FROM alt1 a INNER JOIN split('clio sport coupé') s ON a.alt LIKE '%' + s.keyword + '%'
 
SELECT count(*)
FROM alt2 a INNER JOIN alt2keys ak ON ak.id2 = a.id INNER JOIN split('clio sport coupé') s ON ak.keyword = s.keyword

 
=> Logiquement, je devrais avoir le même résultat pour les deux (ce serait cool ) et on va pouvoir comparer le temps d'exécution.

meuh non
 
tout le monde sait qu'à chaque recherche sur google, il ca interroger l'ensemble des pages de tous les sites de la planète pour voir s'il trouve le mot dedans

-- Bon, le script à la con est en train de retourner...
 
-- Grrr, y'a un bug
 
-- Ah non, pas de bug, juste que j'ai pas rechargé la table des keywords après avoir recréé la base
 
-- Bon ben dans deux heures je pourrai tester (en espérant que ça foire pas ce coup-ci )
 
-- ben ça monte pas vite... 150 000 lignes
 
-- tout pété. chuis en train de vider la base (pigé pkoi ct aussi lent, j'ai une table qui... grossissait de façon exponentielle, alors je vous laisse imaginer ce que ça donnait autour de 300k lignes ) - et du coup c'est très long à vider
 
-- bon, ça re-rempli...

Bon, ça me gave, j'ai arrêté le truc au bout de 201 092 lignes (trop lent, fait chier )
 
Alors :

SELECT a.id, count(s.keyword) rank
FROM alt1 a INNER JOIN split('clio sport coupé') s ON a.alt LIKE '%' + s.keyword + '%'
GROUP BY a.id
ORDER BY rank DESC, a.id

Déjà, à cause du like, le "rank" ne permettra d'identifier que si 3, 2 ou 1 mot clés parmis les mots clés saisis sont présents. Ainsi, le rang de pertinance n'est pas très bon, mais y'a déjà quelquechose, c'est pas mal

SELECT a.id, count(ak.id) rank
FROM alt2 a INNER JOIN alt2keys ak ON ak.id2 = a.id INNER JOIN split('clio sport coupé') s ON ak.keyword = s.keyword
GROUP BY a.id
ORDER BY rank DESC, a.id

Ici, le problème du rank est inverse : il donne le nombre total de mots trouvés, y compris si le même mot est trouvé plusieurs fois (logiquement, dans ton cas c'est pas possible, mais on sait jamais)
 
Note : alt2keys contient 2 413 280 lignes
 
On a donc une requête 2,5 fois plus rapide en se basant sur une table de mots clés plutôt que sur une série de like, malgré l'utilisation d'une jointure supplémentaire.
Ensuite, d'un point de vue programmatique, une chose importante est à noter : j'ai des lignes qui commencent à s'afficher presque immédiatement avec la première requête tandis que la seconde demande bien plus de temps (qu'on ne me demande pas pourquoi )
 
J'aimerais confronter les résultats à un fulltext index, mais j'arrive pas à en créer un avec cette saleté de SQL Server 2005
Je trouve plus où on les crée (je les ai activé, mais pas moyen de les créer, il me dis que je ne peux pas en spécifier un sur un champ si j'en ai pas déjà un et rendu actif... oui mais où ? )

Bon, je le crée à la main puisque c'est comme ça
 

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON alt1 (alt) KEY INDEX pk_alt1;

 
Interrogation de la base avec le catalogue :

SELECT a.id, f.rank
FROM alt1 a INNER JOIN freetexttable(alt1, alt, 'clio sport coupé') f
ON a.id = f.[KEY]
ORDER BY f.rank, a.id

 
En bref, quand un SGBD propose des outils adaptés, autant les utiliser
 
 
PS : MySQL dispose d'un fulltext search tout comme SQL Server.
 
 
 
PS² : Maintenant que tous les index ont bien reconstruits, je viens de retester les 3 requête.
 
Like : 9 secondes
Keywords : 1 seconde
Fulltext : 1 seconde
 
Je n'observe aucune différence entre les deux dernières requêtes d'un point de vue temps d'exécution.

bon, en attendant, tu peux applause quelques instants quand même...
 
je viens de passer là journée à écrire un putain de bench de merde qui s'écrit normalement en 5 minutes (normal, je suis payé à l'heure par le client pour faire autrechose )
si ça c'est pas une performance...

whoohoo j'ai la loose.  \o/
 
Bon faudra que je reprenne ça du coup. merci pour le benchmark chef, c'est bon à savoir.

Style c'est des photos de bagnole ton truc  .
En fait ton truc de alt là, c'est un peu comme un système de tags (c.f Dotclear 2) non ?

mais de quoi jme mêle puceau.
 
 
 
 

J'oubliais un truc...
 
Certes, entre la table de mot-clé et le fulltext search, il ne semble pas y avoir de différence notable d'un point de vue performances...
Mais à côté de ça, il apporte de très grandes améliorations :
- Un RANK très fiable (à la sauce Google on va dire)
- Une recherche "naturelle" dans le sens où, selon les modules complémentaires et les fonctionnalités de la base, il peut se base sur des synonymes, traductions, champ lexical, etc.
- Permet de rechercher différentes formes d'un mot : tu recherches "habiller", il va trouvers aussi "habit", "habillage", etc. mais pas "habitat".
- Permet des recherches très rapides dans des descriptions : adieu les mot-clés, et bonjour les vrais textes.
- Permet des fonctionnalités telles que des expression entières "3 portes", des conditions "confiserie et artisanal ou patisserie" ou simplement d'affecter un "poids" à des mots afin d'influencer directement le rank, ou imposser que deux mots soient plus ou moins proches, etc.
 
Bref, ceci permet des fonctionnalités très diverses et extrêment puissantes (le seul produit que je connaisse qui sâche faire tout ça, pour le moment, c'est Oracle, et moyennant des packages supplémentaires payants, mais bon, c'est tout de même à noter).
 
Mais enfin, surtout, comme tu peux voir dans mon exemple, sans changer du tout la structure de tes tables, et en modifiant à peine ta requête, tu peux exploiter la fonction "freetext" (supportée par MySQL) afin d'obtenir déjà d'excellents résultats.

Un article intéressant (car même si une grande partie est spécifique à SQL Server 2005, les notions abordées sont généralisables).
T'as le droit de pas tout comprendre ou de pas tout lire, moi j'ai callé arrivé au tiers
http://msdn2.microsoft.com/en-us/library/ms345119.aspx