Recherche MySQL -> FULLTEXT

[thierry8]

Hello, re moi ! :wink:

http://dev.mysql.com/doc/mysql/fr/fulltext-search.html

J'ai du mal à imaginer faire une recherche sur une base de données gigantesque avec cette option...
ça pourrait être long, non ?

N'ayant jamais praitquer, j'ai du mal à me faire une idée..
Si quelqu'un d'expérimenté peut me donner son avis, quelques conseils sur la route je l'en remercie !!!

Thierry

 

[Haq]

Je dirais que l'index double à peu près la taille de ta table, si c'est une table genre forum. Reste à savoir quelle est la taille de ta base et ce que peut supporter ton serveur. Fais de toute façon une sauvegarde de ta table avant d'essayer.

 

[thierry8]

Mais la durée d'une recherche de se type peu s'évaluer à combien de temps ?
(ça me parait long...)

Quel autre moyen d'effectuer une recherche ?
(novice dans ce domaine, si vous pouviez m'indiquer des pistes précises..)

Merci

 

[Haq]

Mais la durée d'une recherche de se type peu s'évaluer à combien de temps ?
(ça me parait long...)

Quel autre moyen d'effectuer une recherche ?
(novice dans ce domaine, si vous pouviez m'indiquer des pistes précises..)

Merci

La durée dépend de la taille de la table (et surtout des champs de cette table) concernés.

Sinon, il y a les simples requêtes SELECT, avec un "WHERE texte LIKE '%requete%'", mais ça ne triera pas par pertinence et ça limitera aux résultats qui contiennent exactement la requête concernée (équivalent de la recherche entre guillemets sur Google).

 

[thierry8]

ok, et que pense tu même principe avec les requêtes "simple" mais en remplacant LIKE par une expression régulière ?
On pourra obtenir un meilleur résultat...mais le problème restera le même pour la pertinence...!

Personne n'a encore pratiquer le FULLTEXT afin d'effectuer des recherches ?

(pour répondre à ta question la taille de la table, pourrais devenir grosses, genre forum..., mais les champ plutôt simple je pense...un champ FULLTEXT contenant le "contenu"...vois-tu ?)

merci de ta patience... :?

 

[florianavs18]

En ce moment, je travaille sur une table qui fait 5.3 Millions d'entrées, 3 Go de data, 1.3 Go d'index et sur un Bi-Xeon 1.7, 1Go de ram j'obtiens de très bon résultats, de l'ordre de 10 à 20 secondes pour une recherche sur 1 ou 2 mots.

Oui je sais, 10 sec c'est énorme pour du web, mais pour mon application, ce n'est que du différé, donc peu important.

Si tu as besoin de plus d'infos, hésite pas.

Florian

 

[thierry8]

ok super interessant ! Donc tu met tout tes champs en FULLTEXT et ensuite avec la fonction match() tu réalise des recherches ? (ma table devrait être beaucoup moins lourde...)

Est-ce qu'il y à beaucoup de difficulté à mettre cela en place ?

EDIT: (question subsdisiaire)
comment fais tu pour avoir des chiffres précis ?
comment trouve tu l'espace utilisé par la table (normal) et les index ?

Merci beaucoup à toi !

 

[florianavs18]

Donc tu met tout tes champs en FULLTEXT et ensuite avec la fonction match() tu réalise des recherches ?

La documentation de mysql spécifie que si tu crée un index FULLTEXT sur (titre, contenu, auteur) par exemple, ta recherche doit absolument porter sur ces trois champs en même temps, pour que l'index FULLTEXT soit utilisé, c'est à dire :

SELECT * FROM articles WHERE MATCH (titre, contenu, auteur) AGAINST ('mot1, mot2');

Si tu fais une requete avec un champs en moins ou un champ en plus, genre :

SELECT * FROM articles WHERE MATCH (titre, contenu) AGAINST ('mot1, mot2');

L'index FULLTEXT sur (titre, contenu, auteur) n'est pas utilisé et donc ta requete prend plus de temps à répondre (j'ai pas fais de tests la dessus )

Est-ce qu'il y à beaucoup de difficulté à mettre cela en place ?

Non, c'est une installation classique de Mysql 4.1

comment fais tu pour avoir des chiffres précis ?

Peux tu préciser ?

comment trouve tu l'espace utilisé par la table (normal) et les index ?

phpMyAdmin te donne ces informations dans la page "Structure" de ta table, sinon y'a une fonction SQL mais je ne l'ai pas en tête

Florian

 

[thierry8]

SUper ! Niquel !
Déjà dans un premier temps un grand merci pour ces informations...

La documentation de mysql spécifie que si tu crée un index FULLTEXT sur (titre, contenu, auteur) par exemple, ta recherche doit absolument porter sur ces trois champs en même temps, pour que l'index FULLTEXT soit utilisé, c'est à dire :

C'est à dire que si j'ai une table pour faire une recherche dans cette table de dois lui spécifier tous les champs de type FULLTEXT (pas ceux numérique ou autre...?)

Citation:
comment fais tu pour avoir des chiffres précis ?

Peux tu préciser ?

Tu y as répondu juste en dessous... :wink: merci...!

 

[florianavs18]

C'est à dire que si j'ai une table pour faire une recherche dans cette table de dois lui spécifier tous les champs de type FULLTEXT (pas ceux numérique ou autre...?)

Et bien tu dois indexer les champs qui te seront utiles pour ta recherche
pour les recherches dans des colonnes de type numérique, tu peux faire un simple index, enfin tout dépend ce que représente cette colonne, par exemple si tu crée une colonne langue et que 1 = Anglais, 2 = Français, 3= Allemand là c'est utlise de faire un index _pour les grosse tables_ par contre si c'est pour dire que machin a 4 messages, l'index ne sera d'aucune utilitée.

Imaginons un contexte de forum, en simplifiant un peu, on dit que la table des messages est : titre, id_auteur, contenu, date_publication, html_actif, bbcode_actif, smilies_actif (je prend vraiment ce qui me tombe sous la main ).

- Un INDEX FULLTEXT sera utile sur (titre, contenu)
- html_actif, bbcode_actif, smilies_actif n'a aucun interet à être indexé puisque que ce sont des champs à caractère informatif et on ne recherchera surement jamais avec ces colonnes là en filtre
- par contre dans le cas où on ferait beaucoup de recherche sur les auteurs (je ne sais pas pour quel raisons), la colonne id_auteur (type numérique - identifiant unique interne de l'auteur) doit être indexés en INDEX (pas FULLTEXT).

L'indexation sert essentiellement à gagner du temps pour retrouver tes données, dans le cas de la colonne id_auteur :
- sans index, mySQL parcourait chaque ligne de ta table pour la comparer à la valeur que tu demande
- avec index, mySQL cherche dans son index la valeur que tu demande (qui est forcément indexée) et retourne directement les lignes qui nous interesse. (l'explication n'est pas forcément la plus didactique ni la plus exacte mais elle donne un point de vue général)

Florian

 

[thierry8]

ok ok...ça commence à rentrer la dedans :roll: !

la colonne id_auteur (type numérique - identifiant unique interne de l'auteur) doit être indexés en INDEX (pas FULLTEXT).

Oui mais un FULLTEXT, c'est bien un champ de type texte qui est indexés ?
(parce que je n'ai jamais vue dans mySQL de type FULLTEXT..[j'utilise EasyPHP]) Et c'est ce que j'ai compris du lien que j'ai indiqué à mon premier post.

EDIT:
Et si je fais une recherche je dois spécifier seulement tous les champs du style FULLTEXT ? (d'après ton exemple [contenu et titre]). Le reste je peux faire une recherche simple comme tu l'as souligné...

 

[florianavs18]

Et si je fais une recherche je dois spécifier seulement tous les champs du style FULLTEXT ? (d'après ton exemple [contenu et titre]). Le reste je peux faire une recherche simple comme tu l'as souligné...

Et bien par exemple pour recherche le mot forum dans les messages de l'auteur dont l'identifiant est 5 :

SELECT * FROM messages WHERE MATCH (titre, contenu) AGAINST ('forum') AND id_auteur = 5;

un FULLTEXT, c'est bien un champ de type texte qui est indexés ?

Oui, uniquement des champs de type VARCHAR et TEXT (si je me rappelle bien)

 

[thierry8]

Et bien par exemple pour recherche le mot forum dans les messages de l'auteur dont l'identifiant est 5 :

Ce que je voulais dire c'est par exemple si l'on à pas d'identifiant (un moteur de recherche sur le site, fouillant par exemple dans le forum) on ne connait que les mots clés....Vois-tu ?
Pas besoin de tout renseigner entre autre..?

 

[florianavs18]

Pas besoin de tout renseigner entre autre..?

A bah non, tu fais la requete sur les colonne dont tu as besoin

 

[thierry8]

En fait j'insiste sur ce point parce que tu avais dis:

La documentation de mysql spécifie que si tu crée un index FULLTEXT sur (titre, contenu, auteur) par exemple, ta recherche doit absolument porter sur ces trois champs en même temps, pour que l'index FULLTEXT soit utilisé, c'est à dire :

L'index FULLTEXT sur (titre, contenu, auteur) n'est pas utilisé et donc ta requete prend plus de temps à répondre (j'ai pas fais de tests la dessus )

Donc finallement je peux faire une recherche uniquement sur les champs dont j'ai besoin, meme si il y a plusieurs FULLTEXT je ne peux en utiliser que un...?

 

[florianavs18]

Si tu crée un INDEX FULLTEXT sur (titre, contenu, auteur), pour que ton index soit utilisé lors de la recherche il faut un match sur les 3 colonnes : MATCH (titre, contenu, auteur)

Si tu crée un INDEX FULLTEXT sur (titre, contenu), pour que ton index soit utilisé lors de la recherche il faut un match sur les 2 colonnes : MATCH (titre, contenu)


Par contre si tu crée un INDEX FULLTEXT sur (titre, contenu) et que tu fais un MATCH (titre, contenu, auteur) l'index en question ne sera pas utilisé

Florian

 

[thierry8]

ok ok encore une fois un grand merci à toi...!!!!

Par contre j'avais cru comprendre que la fonction match ne fonctionne qu'avec FULLTEXT...!
si ce n'est pas le cas l'interet d'utiliser TEXT en indexé est uniquement pour la rapidité...comme finallement tout autre type de champ...

 

[xdeslandes]

Votre discussion m'intéresse !
Moi j'ai créé un INDEX FULLTEXT sur 4 champs (ch1, ch2, ch3, ch4), mais mes requêtes sont du genre

SELECT * FROM table WHERE MATCH (ch2) AGAINST ('tels mots')

En effet, ch2 est le champ sélectionné par l'utilisateur lors de sa recherche. Genre: 'je veux les villes (ch2) contenant 'tels mots' ou les départements (ch3) contenant 'tels mots'.
Ce n'est donc pas correct ?

 

[Haq]

Votre discussion m'intéresse !
Moi j'ai créé un INDEX FULLTEXT sur 4 champs (ch1, ch2, ch3, ch4), mais mes requêtes sont du genre

SELECT * FROM table WHERE MATCH (ch2) AGAINST ('tels mots')

En effet, ch2 est le champ sélectionné par l'utilisateur lors de sa recherche. Genre: 'je veux les villes (ch2) contenant 'tels mots' ou les départements (ch3) contenant 'tels mots'.
Ce n'est donc pas correct ?

Si tu ne cherches que sur un champ, crée un index FULLTEXT sur ce seul champ...

 

[thierry8]

florianavs18 tu n'as jamais eu de soucis pour faire une recherche de moins de 4 caractères ?
Une autre question basique...qui me gène, lorsque l'on supprimer un enregistrement il est également supprimer des index (ca me parait logique en fait..mais bon ! :roll: )

Merci

 

[florianavs18]

l'interet d'utiliser TEXT en indexé est uniquement pour la rapidité

Exact !

Moi j'ai créé un INDEX FULLTEXT sur 4 champs (ch1, ch2, ch3, ch4), mais mes requêtes sont du genre
Code:
SELECT * FROM table WHERE MATCH (ch2) AGAINST ('tels mots')

En effet, ch2 est le champ sélectionné par l'utilisateur lors de sa recherche. Genre: 'je veux les villes (ch2) contenant 'tels mots' ou les départements (ch3) contenant 'tels mots'.

Si tu veux povoir avoir un resultat rapide sur l'un des 4 champs, il te faut créer 4 INDEX FULLTEXT, un pour chaque champ et là, l'index servira pour chaque requete et donc gain de performances !!

 

[florianavs18]

http://dev.mysql.com/tech-resources/articles/full-text-revealed.html

Voici un article qui précise pas mal de choses sur le FULLTEXT dont les StopWords pour la langue anglaise.

une recherche de moins de 4 caractères

Euh je ne peux pas te répondre la dessus, faudrait que je fasse des tests !

 

[florianavs18]

J'avais oublié ça :

lorsque l'on supprimer un enregistrement il est également supprimer des index

Non, mySQL modifi de lui meme l'index, si il fallait recontruire l'index a chaque fois, on ne serait pas sorti !! Sur ma base (3Go), ça prend environ 2h30 pour reconstruire l'index !

 

[thierry8]

Je n'y comprend pas grand chose moi à l'anglais...et oui grosse l'acune...!
(mais pas trop grave)

Pour la limite de car. c'est par défaut 4 mini. :wink:

 

[thierry8]

Non, mySQL modifi de lui meme l'index, si il fallait recontruire l'index a chaque fois, on ne serait pas sorti !! Sur ma base (3Go), ça prend environ 2h30 pour reconstruire l'index !

J'ai pas bien compris...il y a une contradiction...
Tu dis que mySQL modifie lui même l'index, mais ensuite que s'il fallait le reconstruire à chaque fois on ne s'en sortirait pas!.

Si je comprend bien mySQL le gère tout seul et enlève ce qu'il faut, mais pour une reconstruction totale de la base il faut le faire manuellement et cela prendrais du temps...si oui quel différence entre les deux ? mySQL n'est pas propre ?...

 

[florianavs18]

Si je comprend bien mySQL le gère tout seul et enlève ce qu'il faut, mais pour une reconstruction totale de la base il faut le faire manuellement et cela prendrais du temps...si oui quel différence entre les deux ? mySQL n'est pas propre ?...

Si ! mySQL est propre, j'ai évoqué ce probleme de temps parceque j'ai eu deux plantages de machine et à chaque fois une reconstruction d'index a faire

mySQL est autonome une fois l'index créé.

 

[thierry8]

Bonne chance pour ta reconstruction ! :? :wink:
Ca ce passe comment pour "reconstruire une table" ou les index ?

Tu dois juste la remettre en place et ca ce fais ou...?? sorry.. :?

EDIT: ou alors reparer - optimiser...? une de ces options ?

 

[florianavs18]

Ca ce passe comment pour "reconstruire une table" ou les index ?

REPAIR TABLE table

 

[thierry8]

ok tout simplement....

bon je vais abuser, mais comme tu t'y connais pas mal...
l'optimisation d'une table, si je ne me trompe pas permet par exemple à l'index de se "refaire"..car au fur et à mesure, modif, suppression, l'index comporte des trous...et l'optimisation permet de les combler si je ne me trompe pas ? :roll:

 

[florianavs18]

l'optimisation d'une table, si je ne me trompe pas permet par exemple à l'index de se "refaire"..car au fur et à mesure, modif, suppression, l'index comporte des trous...et l'optimisation permet de les combler

Et bien, REPAIR permet de reconstruire un index corrompu, aussi sur les données en elles même car un DELETE efface et créé un *trou* dans la table de donnée (je suis moins sûr là). Il faudrait regarder les docs

La doc mysql (http://dev.mysql.com/doc/mysql/en/repair-table.html) nous dis :

REPAIR TABLE tries to repair only the index tree

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

Donc REPAIR est pour les INDEX, et OPTIMIZE quand les trous de tables dynamique (longuer variable à cause des VARCHAR ou TEXT)ne sont pas entièrement recouvert par l'INSERT suivant.

Florian

 

[thierry8]

ah ouai....hummm...
ok je pense arriver au bout de mes peines...

Je te remercie pour toutes ces informations et ce temps consacré..
Marchiiiiiiiii beaucoup! :wink:

En cas de soucis ce petit post. resurgira... 8O

Merci, c'est vraiment sympa de ta part et agréable à venir poster dans ces conditions !

 

[thierry8]

euh...sorry...mais une table MyISAM c'est quoi ?
Est-ce par défaut sur MySQL..?

EDIT: encore une question comment peut-on savoir qu'une table doit etre réparer...? Qu'elle est corrompu...?
Ca plante cash....?

 

[florianavs18]

une table MyISAM c'est quoi

C'est le type standard de mySQL

comment peut-on savoir qu'une table doit etre réparer

avec un ANALYZE TABLE, ou sinon phpmyadmin te l'indiquera.

Florian

 

[thierry8]

ok ! Niquel tout est automatique...encore faut-il allé dans phpadmin de temps en temps...! lol

Merci encore à toi...