Recherche complexe et performante dans une base mysql ?

milkiway · 9 Juin 2011

Bonjour,

Je souhaiterais faire une recherche dans une base MySQL mais sur les termes proches de ma requête.

Exemple, si on parle de téléphone, je voudrais lister toutes les entrées qui approchent ma requête "iphone 3gs 16 Go"
Et que ça me liste par exemple
- iphone 3gs 16 Go
- 3gs apple iphone 16gb
- iphone 16GB version 3 GS
- etc.

Je rêve des genoux ou c'est faisable avec une fonction qui va bien ?

Merci

Marie-Aude · 9 Juin 2011

Tu ne précises pas comment tu définis les termes "proches"
En fait le problème est là : identifier les termes essentiels de la requête
Après c'est du like '%monterme%'

Bref le problème n'est pas dans mysql

milkiway · 9 Juin 2011

Merci pour la réponse.

Justement, il y a une telle variété de termes qu'il est impossible de les définir à l'avance.
like ne correspond pas à ce que je cherche. C'est plus du domaine du soundex, fulltext ou ce genre de chose me semble t il.
Et je cherche justement (si ça existe) une solution SQL.

Marie-Aude · 9 Juin 2011

Non ce sont d'autres techniques.
En même temps, tu peux essayer de travailler sur les longueurs de chaine, la composition (avec ou sans chiffre)

forty · 10 Juin 2011

avec un index fulltext et les bonnes options du SELECT ca doit être possible =>http://dev.mysql.com/doc/refman/5.0/fr/fulltext-search.html

jcaron · 10 Juin 2011

Oui, je pense que fulltext est ta meilleure option.

L'alternative, c'est de gérer tout ça "toi même": tu décomposes chaque champ en une série de mots, et tu les indexes séparément via une table de jointure. Tu peux ensuite utiliser de la canonicalisation, du stemming, du soundex, des synonymes, etc. suivant tes besoins. Ca te donne plus de souplesse, mais c'est bien entendu plus compliqué.

Par exemple, si le champ contient "toto tata tutu", tu décomposes en 3 mots, et tu as:
- une table qui contient les mots eux-mêmes et un id (avec index sur le mot), donc 3 lignes, une pour toto, une pour tata, une pour tutu;
- une table qui associe chacun de ces mots à ta table originale: id_tata->id_de_ta_ligne, etc. Tu peux optionnellement ajouter la position dans le champ, ou une notion de "poids" du mot, de façon générale (un mot très fréquent pourra avoir un poids plus faible par exemple) ou au niveau de cette ligne de la table originale (un mot très présent dans cette ligne aura un poids plus important)

Ensuite, tu obtiens ton résultat avec une requête tu type:

Code:

select * from table t,mots m1,mots m2,mots m3,mots_table mt1,mots_table mt2,mots_table mt3 where m1.mot='toto' and m2.mot='tata' and m3.mot='tutu' and mt1.mot=m1.id and mt2.mot=m2.id and mt3.mot=m3.id and mt1.table=t.id and mt2.table=t.id and mt3.table=t.id

Ca peut se générer de façon automatique en fonction du nombre de mots si nécessaire.

Avec les bons index là où il faut, ça devrait rester relativement rapide, à condition de traiter correctement les mots très présents.

Jacques.

milkiway · 10 Juin 2011

Merci beaucoup pour ces idées qui me semblent excellentes.
Tu factures combien pour ça Jacques ? MP si tu veux

Merci

spout · 10 Juin 2011

Est-ce que sphinxsearch ne permettrait pas de faire ça ?

milkiway · 10 Juin 2011

Je me suis posé la même question tout à l'heure. Je ne sais pas s'il peut être utilisé en gros juste pour renvoyer du contenu à un script PHP.

e-kiwi · 10 Juin 2011

sol'R rulez

http://php.net/manual/fr/book.solr.php

spout · 10 Juin 2011

milkiway a dit:
Je ne sais pas s'il peut être utilisé en gros juste pour renvoyer du contenu à un script PHP.

http://www.ibm.com/developerworks/library/os-php-sphinxsearch/
http://sphinxsearch.com/wiki/doku.php?id=php_api_docs

Julia41 · 10 Juin 2011

Avec sphinx search, tu aurais des bonnes fonctions pour ce que tu cherches avec du build stop word (par exemple) qui te construit une liste de "tous les mots inutiles" (habituellement les il, elle, les, etc...).
Sphinx log aussi toutes les recherches, avec un coup de script ça te permettrait de faire des beaux inserts dans ta base.

A noter qu'avec pspell (bon, pour la config tu vas en chier) tu pourrais peut-être aussi faire quelque chose de sympa.
Nous on s'en sert pour "did you mean ?"

Voilà la fonction (toute simple) la seule "merde" est de build le fichier pws:

PHP:

<span class="syntaxdefault">function didumean</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$query</span><span class="syntaxkeyword">)</span><span class="syntaxdefault"> </span><span class="syntaxkeyword">{<br /></span><span class="syntaxdefault">        </span><span class="syntaxcomment">//$pspell_config = pspell_new('fr', '', '', '',(PSPELL_FAST|PSPELL_RUN_TOGETHER));<br /></span><span class="syntaxdefault">        $pspell_config </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> pspell_config_create</span><span class="syntaxkeyword">(</span><span class="syntaxstring">'fr'</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> </span><span class="syntaxstring">''</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> SITENAME</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> </span><span class="syntaxstring">'cp1252'</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        pspell_config_mode</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$pspell_config</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> PSPELL_FAST</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        pspell_config_personal</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$pspell_config</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> SITEDIR </span><span class="syntaxkeyword">.</span><span class="syntaxdefault"> </span><span class="syntaxstring">'/includes/didumean.pws'</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        $pspell_link </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> pspell_new_config</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$pspell_config</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        $words </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> preg_split </span><span class="syntaxkeyword">(</span><span class="syntaxstring">'/\s+/'</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> $query</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        $ii </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> count</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$words</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        $spellchecked </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> </span><span class="syntaxstring">''</span><span class="syntaxkeyword">;<br /></span><span class="syntaxdefault">                for</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$i</span><span class="syntaxkeyword">=</span><span class="syntaxdefault">0</span><span class="syntaxkeyword">;</span><span class="syntaxdefault"> $i</span><span class="syntaxkeyword"><</span><span class="syntaxdefault">$ii</span><span class="syntaxkeyword">;</span><span class="syntaxdefault"> $i</span><span class="syntaxkeyword">++){<br /></span><span class="syntaxdefault">                        if </span><span class="syntaxkeyword">(</span><span class="syntaxdefault">pspell_check</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$pspell_link</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> $words</span><span class="syntaxkeyword">[</span><span class="syntaxdefault">$i</span><span class="syntaxkeyword">])){<br /></span><span class="syntaxdefault">                                $spellchecked </span><span class="syntaxkeyword">.=</span><span class="syntaxdefault"> $words</span><span class="syntaxkeyword">[</span><span class="syntaxdefault">$i</span><span class="syntaxkeyword">]</span><span class="syntaxdefault"> </span><span class="syntaxkeyword">.</span><span class="syntaxdefault"> </span><span class="syntaxstring">' '</span><span class="syntaxkeyword">;<br /></span><span class="syntaxdefault">                        </span><span class="syntaxkeyword">}</span><span class="syntaxdefault"> else</span><span class="syntaxkeyword">{<br /></span><span class="syntaxdefault">                                $erroneous </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> TRUE</span><span class="syntaxkeyword">;<br /></span><span class="syntaxdefault">                                $suggestions </span><span class="syntaxkeyword">=</span><span class="syntaxdefault"> pspell_suggest</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$pspell_link</span><span class="syntaxkeyword">,</span><span class="syntaxdefault"> $words</span><span class="syntaxkeyword">[</span><span class="syntaxdefault">$i</span><span class="syntaxkeyword">]);<br /></span><span class="syntaxdefault">                                if </span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$suggestions</span><span class="syntaxkeyword">)<br /></span><span class="syntaxdefault">                                        $spellchecked </span><span class="syntaxkeyword">.=</span><span class="syntaxdefault"> $suggestions</span><span class="syntaxkeyword">[</span><span class="syntaxstring">'0'</span><span class="syntaxkeyword">]</span><span class="syntaxdefault"> </span><span class="syntaxkeyword">.</span><span class="syntaxdefault"> </span><span class="syntaxstring">' '</span><span class="syntaxkeyword">;<br /></span><span class="syntaxdefault">                        </span><span class="syntaxkeyword">}<br /></span><span class="syntaxdefault">                </span><span class="syntaxkeyword">}<br /></span><span class="syntaxdefault">        if</span><span class="syntaxkeyword">(isset(</span><span class="syntaxdefault">$erroneous</span><span class="syntaxkeyword">)){<br /></span><span class="syntaxdefault">                return ucwords</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">$spellchecked</span><span class="syntaxkeyword">);<br /></span><span class="syntaxdefault">        </span><span class="syntaxkeyword">}</span><span class="syntaxdefault"> else</span><span class="syntaxkeyword">{<br /></span><span class="syntaxdefault">                return false</span><span class="syntaxkeyword">;<br /></span><span class="syntaxdefault">        </span><span class="syntaxkeyword">}<br />}<br /></span><span class="syntaxdefault"> </span>