Optimisation de requete fulltext

[Mamat]

Bonjour,

J'essaye d'optimiser un code que j'ai fait il y a un bout de temps et qui est devenu surper crade à mes yeux ;oD le voici :

$result = mysql_query("SELECT Verset FROM `$Table` WHERE `Texte` LIKE '% $Terme %' OR `Texte` LIKE '% $Terme, %' OR `Texte` LIKE '% $Terme. %' OR `Texte` LIKE '% $Terme: %' OR `Texte` LIKE '% $Terme; %' OR `Texte` LIKE '% $Terme! %' OR `Texte` LIKE '% $Terme? %'")

Le but étant donc de chercher dans une table, un terme exact. en comparaison l'autre option de recherche est la suivante :

$req = mysql_query ("SELECT * FROM `$Table` WHERE MATCH ( Texte ) AGAINST ( '%$Terme%' ) ORDER BY Livre ASC LIMIT $limit, $enreg_par_page")

Vous l'aurez donc compris, l'un utilise la clef fulltext et l'autre rien.. résultat mon serveur est au bord de la syncope ;oD les requetes mettent près de 3 sec !! comment pourrais-je donc faire la première requete de façon plus intelligente à votre avis ?

[Vincent]

Pour la 1ere requete, je ne comprends pas pourquoi tu fais tous ces distinctions de cas avec des 'OR'?

si tu mets %$terme%, cela equivaut a $terme. $terme, $terme! etc...

donc moi je mettrais une requete simple du genre :

$result = mysql_query("SELECT Verset FROM `$Table` WHERE `Texte` LIKE '%$Terme%'")

et ensuite, ne pas oublier de mettre un index sur ton champs Texte!

[TheRec]

Un index FULLTEXT qui plus est...sur tous les champs qui seront exploré lors de requête FULLTEXT...

Dans ton cas :

ALTER TABLE Verset ADD FULLTEXT(Texte)

Ou éventuellement à la création du la table avec la syntaxe CREATE FULLTEXT INDEX

[Mamat]

Mais l'index y est déjà ! J'était persuadé que sur un simple SELECT avec WHERE, l'index fulltext n'était pas pris.... Je viens de vérifier en effet avec EXPLAIN il n'utilise pas l'index, d'où l'inefficacité de cette méthode...

Modifié 20 Avril 2006 par Mamat

[Spidetra]

Mais l'index y est déjà ! J'était persuadé que sur un simple SELECT avec WHERE, l'index fulltext n'était pas pris.... Je viens de vérifier en effet avec EXPLAIN il n'utilise pas l'index, d'où l'inefficacité de cette méthode...

<{POST_SNAPBACK}>

Ton index ce n'est pas un index FULLTEXT ?

Pourquoi ne pas dropper ton index actuel et le remplacer par un index fulltext.

Ensuite tu refais un explain pour t'assurer qu'il est bien utiliser.

Les index ne sont pas systématiquement utilisé. D'ailleurs je ne suis pas sur qu'ils soient utilisé avec des requêtes de type LIKE et %.

Je fait un copier/coller d'un post que je viens de faire en face

Ensuite il faut être prudent. Il ne sert à rien d'indéxer, si on utilise pas les index dans les requêtes.

Un exemple concret :

ng int,

nd int,

KEY idx_ng (ng)

Deux champs de type int et un index sur le champ ng.

Si tu fait cette requête :

WHERE nd - ng = 1

ton index sur ng ne sera pas utilisé.

Il faut réécrire ta requête pour bénéficier de l'index sur ng.

WHERE  ng = nd - 1

Modifié 20 Avril 2006 par Spidetra

[Mamat]

Heu oui je sais tout ça... je l'ai dit dans mes deux premiers post, l'index en fulltext est là depuis des années, mais pour qu'une requete where l'utilise il faut utiliser "martch ... against", donc avec mon expression ce n'est pas possible, vois-tu ?

[Spidetra]

Heu oui je sais tout ça... je l'ai dit dans mes deux premiers post, l'index en fulltext est là depuis des années, mais pour qu'une requete where l'utilise il faut utiliser "martch ... against", donc avec mon expression ce n'est pas possible, vois-tu ?

<{POST_SNAPBACK}>

Désolé, je n'ai donc absolument rien compris à ton pb.

[TheRec]

Si tu met en plas le FULLTEXT c'est justement pour éviter de passer par des requêtes non optimisées comme celle que tu présente ne premier

Si tu veux vraiement faire cette requête essaie, peut-être, de définir un INDEX (normal) en plus de l'index FULLTEXT que tu as déjà pour les champs sur lesquels tu effectues la recherche.

Les champs sur lesquels il y a un INDEX sont optimisé selon le manuel...je ne sais pas si c'est toujours le cas lorsqu'il existe déjà un un index FULLTEXT...

[Spidetra]

Si tu veux vraiement faire cette requête essaie, peut-être, de définir un INDEX (normal) en plus de l'index FULLTEXT que tu as déjà pour les champs sur lesquels tu effectues la recherche.

Cela n'est pas une bonne idée.

En règle générale, on n'utilise jamais d'index FULLTEXT, on utilise des index.

On ne met jamais d'index sur un champ de type TEXT ou sur un BLOB.

Si on veut indéxer un champ de type TEXT, on utilise FULLTEXT et des requêtes du type MATCH...AGAINST.

Le fait de doubler ( Index + FULLTEXT ) sur la même colonne ne sert à rien. C'est peut-être même contre-performant.

Les champs sur lesquels il y a un INDEX sont optimisé selon le manuel...je ne sais pas si c'est toujours le cas lorsqu'il existe déjà un un index FULLTEXT...

<{POST_SNAPBACK}>

L'optimisation dépend de l'utilisation que tu fait de la colonne dans tes requêtes.

[TheRec]

On ne met jamais d'index sur un champ de type TEXT ou sur un BLOB.

Le manuel semble ne pas être d'accord avec toi :

The MyISAM and (as of MySQL 4.0.14) InnoDB storage engines also support indexing on BLOB and TEXT columns. When indexing a BLOB or TEXT column, you must  specify a prefix length for the index.

Si on veut indéxer un champ de type TEXT, on utilise FULLTEXT et des requêtes du type MATCH...AGAINST.

Sauf que si je n'utilise pas MyISAM ? Non soyons sérieux, un INDEX peut être mis sur un champ TEXT, et cela optimisera les requêtes SELECT (sinon pourquoi les développeur on décidé d'implémenter cette fonctionnalité depuis MySQL 3.23 ?) :

All MySQL data types can be indexed. Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations.

Le fait de doubler ( Index + FULLTEXT ) sur la même colonne ne sert à rien. C'est peut-être même contre-performant.

Sur quoi te bases-tu pour affirmer ceci ? Il est clair que les performance lors de l'ajout de données s'en ressentira (plus d'index à générer) mais à la consultation, j'ose supposer que le moteur de MySQL est assez grand pour choisir l'optimisation qui sied le mieux (une requête MATCH .. AGAINST = optimisations FULLTEXT, autres optimisation des INDEX simples)... enfin il faudrait tester cela à une échelle représentative...

[Spidetra]

Je me rend compte que j'ai été trop catégorique dans mon précédent post.

En fait il faut remplacer ON par JE.

Je ne cherchais pas à imposer des pratiques, juste à décrire les miennes.

C'est la mise en production qui impose les pratiques d'optimisation.

Je m'impose un certains nbrs de règles d'otimisation que j'applique systématiquement. Et non, je ne suis pas tjrs d'accord avec les manuels, désolé

Ces règles sont issus de mon expérience, de ma connaissance des SGBD ou de le littérature.

Parmis ces règles : je ne met jamais un index sur un champ de type TEXT. Tant pis si je ne suis pas d'accord avec le manuel.

Mon cycle d'optimisation est assez simpliste :

1. Je conçois mon modèle en m'imposant des règles strictes.

2. J'optimise en phase de dév, en fct° des requêtes dont j'ai besoin. Cette optimisation va porter essentiellement sur :

=> La syntaxe des requêtes SQl

=> L'ajout de nouveaux index.

3. Je met mon modèle à l'épreuve de la production. C'est en recherchant les goulots d'étranglement que je vais mettre à l'épreuve mes règles initiales et les faire évoluer.

Ensuite, il est vrai que dans mes choix je ne suis pas tjrs d'accord avec la majorité des développeurs. Mais ça c'est pas très grave.

Deux exemples concret de choix techniques où je ne suis pas d'accord avec la grande majorité des développeurs :

- L'indexation FULLTEXT : Je n'utilise jamais un SGBD, pour les recherches FULLTEXT. J'estime que les systèmes ne sont pas assez performant.

- La modélisation d'une arborescence : je ne modélise jamais une arborescence par auto-jointure ( Merci, M. Celko ) . Tant pis, si la grande majorité des développeurs choisissent systématiquement le modèle par auto-jointure et ne sont pas d'accord avec moi.

[TheRec]

Hmm... je n'impose pas non plus l'utilisation de ma solution Simplement Mamat veut pouvoir faire une requête qui soit "optimisée" (SELECT WHERE) en ayant un modèle qui est optimisée pour une autre utilisation (FULLTEXT)...

En même temps on ne peut pas avoir tout...soit on sacrifie un peu de ressources lors de la phase modification de la base de données...soit on en sacrifie à chaque recherche...je préfère la première solution dans le cas de Mamat...mais si tu en as une autre faut pas hésiter

On peut tout à fait faire le parallèle avec les arbres, l'utilisation du modèle Adjacent (auto-jointures) est tout à fait justifiable dans le cas où les accès (consultations) à l'arbre sont moins fréquents que la modification de sa structure...sinon un modèle pré-ordré (intervallaire) sera préconisé (jutilise plus souvent cette solution, car sur le Web on a tendance à rencontrer plus de systèmes ou la consultation est ce qui doit se faire le plus rapidement possible)...

[robinsonvendredi]

Les arguments de spidetra me semblent plutôt bons.

J'ai une requête vraiment très similaire à celle de Mamat pour un moteur de recherche.

Je n'ai pas de problème car je n'utilise pas de champs de type TEXT.

Sans faire de pub pour une bdd, la mienne autorise en effet les champs varchar(MAX).

[Spidetra]

Simplement Mamat veut pouvoir faire une requête qui soit "optimisée" (SELECT WHERE) en ayant un modèle qui est optimisée pour une autre utilisation (FULLTEXT)...

...

mais si tu en as une autre faut pas hésiter

En fait, comme dit un plus haut, je ne comprend pas vraiment le pb de Mamat. Je dois être un peu trop psycho-rigide

FULLTEXT => Match AGAINST.

Concernant les LIKE, l'optimisation relève du parcours du combattant. Je ne suis même pas surs qu'il soit possible d'optimiser des requêtes LIKE.

En fait, je dois avouer que j'utilise très peu souvent LIKE, si possible jamais.

La réponse au pb de Mamat a été donné par le premier post de Vincent.

Ce qui en veut pas dire que le reste de la discussion soit inutile. Bien au contraire

On peut tout à fait faire le parallèle avec les arbres, l'utilisation du modèle Adjacent (auto-jointures) est tout à fait justifiable dans le cas où les accès (consultations) à l'arbre sont moins fréquents que la modification de sa structure...sinon un modèle pré-ordré (intervallaire) sera préconisé (jutilise plus souvent cette solution, car sur le Web on a tendance à rencontrer plus de systèmes ou la consultation est ce qui doit se faire le plus rapidement possible)...

<{POST_SNAPBACK}>

Je vois que l'on partage la même conception des choses

Le nbr de prise de becs que j'ai pu avoir avec des developpeurs pour leur imposer le modèle intervallaire ! Je me sentirais moins seul

Mais là on est en train de dériver du post initial

[Mamat]

Spidetra je pense que tu as assez bien compris le soucis, pour un type de recherche ("phrase identique", ou "tous les mots") le FULLTEXT ET DONC le MATCH AGAINST, car la clause WHERE LIKE n'utilisera jamais un index FULLTEXT. Par contre pour une recherche de chaque mot et non la phrase entière le seul moyen est un simple WHERE LIKE %$Terme% simplement y'a-til moyen d'être plus performant sur cette requète qui ne passe forcèment par auncu index (EXPLAIN à l'appui).

Enfin pour ce qui est de mettre un index sur un champ text, c'est tout bonnement inutile ;oD... enfin d'après mes connaissances, savoir qu'il existe en page 4 d'un livre un paragraphe avec des mots divers et que celui que cherche et dans ça... je vois pas l'intérèt (j'espère que ma parabolle est claire)

[TheRec]

Enfin pour ce qui est de mettre un index sur un champ text, c'est tout bonnement inutile ;oD... enfin d'après mes connaissances, savoir qu'il existe en page 4 d'un livre un paragraphe avec des mots divers et que celui que cherche et dans ça... je vois pas l'intérèt (j'espère que ma parabolle est claire)

<{POST_SNAPBACK}>

Je répète que les INDEX sous MySQL sont utilisés pour l'optimisation...mettre un champ comme INDEX augmente les performances lors des requêtes SELECT : http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html

Vu qu'il est possible de mettre un champ TEXT en INDEX, il n'y a qu'une seul raison logique à cela, c'est que lors de requête SELECT (WHERE champ LIKE) sur ce champ la recherche se fera plus vite... Il y a deux cas ou c'est faux : lorsque la valeur recherchée commence par un caractère joker (par exemple %) ou qu'il est variable (par exemple un autre champ de la base)...Donc, dans le cas que tu évoques %Terme% il n'y aura pas d'optimisation...(Terme% aurait été optimisé), je n'avais pas lu ceci dans le manuel lors de mon premier message, donc je te prie d'ignorer mon conseil...l'index sur le champ texte, dans ton cas, n'aidera pas...mais il ne ralentira pas pour autant la recherche (juste l'ajout/modification/suppression de données à la base...lorsque l'index est reconstruit)

[Spidetra]

Par contre pour une recherche de chaque mot et non la phrase entière le seul moyen est un simple WHERE LIKE %$Terme% simplement y'a-til moyen d'être plus performant sur cette requète qui ne passe forcèment par auncu index (EXPLAIN à l'appui).

C'est ce point qui me surprend !

Je n'utilise jamais d'index FULLTEXT, mais à priori, j'aurai envie de dire que ce genre de recherche est assez souple pour résoudre tes problèmes.

Tu aurais un exemple de recherche que tu arrive à faire avec un LIKE, et pas avec une recherche FULLTEXT ?

Si tu poste la structure de ta table, un petit jeu de donnée et les recherches qui posent un pb avec LIKE, on pourra s'amuser à essayer de t'aider.

Les requêtes en FullText me semblent quand même assez souple ( petit extrait du manuel ) :

The following examples demonstrate some search strings that use boolean full-text operators:

    *

      'apple banana'

      Find rows that contain at least one of the two words.

    *

      '+apple +juice'

      Find rows that contain both words.

    *

      '+apple macintosh'

      Find rows that contain the word apple, but rank rows higher if they also contain macintosh.

    *

      '+apple -macintosh'

      Find rows that contain the word apple but not macintosh.

    *

      '+apple ~macintosh'

      Find rows that contain the word apple, but if the row also contains the word macintosh, rate it lower than if row does not. This is softer than a search for '+apple -macintosh', for which the presence of macintosh causes the row not to be returned at all.

    *

      '+apple +(>turnover <strudel)'

      Find rows that contain the words apple and turnover, or apple and strudel (in any order), but rank apple turnover higher than apple strudel.

    *

      'apple*'

      Find rows that contain words such as apple, apples, applesauce, or applet.

    *

      '"some words"'

      Find rows that contain the exact phrase some words (for example, rows that contain some words of wisdom but not some noise words). Note that the " characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotes that enclose the search string itself.