Tag Archives: fulltext search

Recherche Fulltext sous MySql

Posted on by
Reply

Scripter un moteur de recherche nécessite :

  • un formulaire côté client pour soumettre des requêtes
  • des champs de table de base de données à l’intérieur desquels la recherche doit s’effectuer
  • des requêtes à la base de données pour rechercher la chaîne postée par le client

Je veux tout d’abord me pencher sur les caractéristiques de la base de données pour effectuer une telle recherche.

Auaparavant, j’utilisais des requêtes avec des sous-requêtes du type :

SELECT id,title,content from table_name WHERE title LIKE ‘%”.$search.”%’ OR content LIKE ‘%”.$search.”%’;

qui effectue une recherche de style expression régulière et peut causer des problèmes de ressources MySql (grand nombre de lignes parcourues, grand nombre de résultats,…).

Sous MySql, une recherche FULLTEXT peut solutionner ce problème de ressources et produire des résultats probants puisqu’elle effectue une recherche sur des données indexées, et donc pas directement dans la table. Ce type de recherche permet l’utilisation :

  • d’une liste de mots rejetés (mots que le serveurs exclut par défaut de la recherche)
  • de recherches booléennes (utilisant + ou -, par exemple, pour ajouter ou enlever des mots de la recherche)
  • d’un score de pertinence

Type de table qui permet l’indexation Fulltext

La recherche FULLTEXT est disponible avec les tables utilisant MyISAM comme moteur de stockage. Bien que le moteur de stockage InnoDB offre l’énorme avantage de pouvoir utiliser des transactions (gestion des erreurs par commit et rollback), la recherche par indexation Fulltext y est absente. Concentrons-nous donc sur les tables MyISAM.

Vous pouvez définir un index Fulltext sur un ou plusieurs champs de type CHAR, VARCHAR et TEXT. Comme mentionné dans la référence MySql reference sur le sujet, créer des données dans une table disposant d’un index Fulltext prendra plus de temps. Ainsi, si un nombre conséquent de données doit être traité avec un index Fulltext (et c’est dans ce cas qu’il sera le plus efficace), il vaut mieux d’abord insérer les données dans la table, puis créer l’index.

Vous pouvez créer un index sur les 2 champs “title” et “content” avec la requête suivante :

ALTER TABLE table_name ADD FULLTEXT index_name (‘title’ , ‘content’);

Une fois l’index Fulltext de la table défini, faites une recherche comme suit :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘);

Les champs de la sous-requête MATCH doivent être les mêmes que ceux définis dans l’index fulltext de la table, et vous pouvez créer un index Fulltext sur un champ ou plus.

Paramétrage de la recherche Fulltext

MySql est configuré avec un lot de paramètres par défaut :

  • les résultats sont classés par score de pertinence que MySql donne à chaque donnée trouvée
  • les mots de moins de 4 caractères seront ignorés
  • l’index Fulltext contient uniquement des mots entiers
  • liste des mots exclus d’une recherche Fulltext. Ce paramètre dépend de la langue d’installation de MySql
  • le score de chaque donnée trouvée dépendra du nombre d’occurences de ce mot dans la table. Si un mot apparaît dans plus de la moitié des lignes de la table, il sera ignoré

Le changement de ces paramètres par défaut ne sera possible que dans le cas où votre serveur Web MySql vous permet de les modifier.

Recherche en mode booléen

La sous-requête MATCH… AGAINST… permet la recherche booléenne :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘ IN BOOLEAN MODE);

MySql retournera toutes les lignes correspondantes même si plus de 50% des lignes contiennent le mot-clef.

Les recherches booléennes permettent en outre l’utilisation d’opérateurs comme + ou – pour forcer MySql à retourner les lignes contenant ‘works’ et cacher les lignes contenant ‘tables’ :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘+works -tables’ IN BOOLEAN MODE);

Vous pouvez aussi utiliser l’opérateur * comme joker en mode booléen pour rechercher une partie de mot :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘peopl*’ IN BOOLEAN MODE);

Webliography :

Référence sur la recherche FULLTEXT sous MySql

Référence sur la recherche FULLTEXT en mode Booléen sous MySql

MySql Fulltext search

Posted on by
Reply

Scripting an internal search engine requires :

  • a client form to submit requests
  • database fields to search posted keywords
  • database queries to match the form request

I want to focus on the database requirements to make such a search.

I used to build queries with sub-queries of the following type :

SELECT id,title,content from table_name WHERE title LIKE ‘%”.$search.”%’ OR content LIKE ‘%”.$search.”%’;

which performs a kind of regular expression search and can lead to MySql resource problems (large number of rows parsed, large number of results found,…).

Under MySql, a FULLTEXT search may solve this resource problem and achieve the search since it performs the search in indexed data. This feature allows

  • stopwords (words which will be excluded by the server itself)
  • boolean searches (using + or -, for example, to add to search or substract from search)
  • relevancy scoring

Type of table that allows fulltext indexing

FULLTEXT search is available in MyISAM tables. Though the InnoDB Storage Engine offers transactions (commit and rollback error handling), the fulltext search feature is absent. So, let’s stick to MyISAM tables.

You can define a FULLTEXT index on one or more table fields of types CHAR, VARCHAR and TEXT. As stated in MySql reference on the subject, creating rows in a table with fulltext index will take longer. So, if lots of rows need to be treated with a fulltext index (a fulltext index will be most efficient in this very case), it is better to first insert the rows, then create the index.

You can add a fulltext index on the 2 fields “title” and “content” with the following query :

ALTER TABLE table_name ADD FULLTEXT index_name (‘title’ , ‘content’);

Once the table has a fulltext index defined, the query can be the following :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘);

The fields in the MATCH statement have to be the same as defined in the table’s fulltext index, and you can define a fulltext index on one or more field.

Fulltext search parameters

MySql is configured with a set of automatic parameters :

  • the results are ordered by the relevancy score MySql gives to each row found
  • words with less than 4 characters will be ignored
  • the fulltext index contains complete words only
  • depending on the language MySql is set to, a list of stopwords will be automatically ignored
  • the score for each row depends on the number of occurences of that word in the table. If a word occurs in more than half the lines, it will be ignored

Changing these default parameters will only be possible if your MySql Web Server allows you to modify those parameters.

Boolean mode search

The MATCH… AGAINST… statement allows you to perform a Boolean search :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘ IN BOOLEAN MODE);

MySql will then return all rows matching the searched words even if more than 50% of the rows contain the keyword.

Boolean searches allow you to use operators like + or – to force MySql to return rows containing ‘works’ and hide rows containing ‘tables’ :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘+works -tables’ IN BOOLEAN MODE);

You can also use the * operator as a wildcard in Boolean mode to search part of a word :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘peopl*’ IN BOOLEAN MODE);

Webliography :

MySql FULLTEXT search reference

MySql FULLTEXT search – Boolean mode reference