Comment utiliser les recherches en texte intégral dans MySQL –

La recherche en texte intégral est une technique de base de données qui récupère les enregistrements même s’ils ne correspondent pas exactement à vos critères de recherche. Cela permet une recherche riche en langage naturel qui semble plus familière.

La recherche de «moteur de base de données» à l’aide d’une recherche en texte intégral renverra des résultats contenant les expressions «base de données», «moteur» ou «moteur de base de données». Cela résout les ambiguïtés possibles dans vos critères de recherche, donc des lignes telles que « J’ai une base de données et un moteur » apparaissent toujours.

Des recherches simples dans MySQL peuvent être effectuées en utilisant le LIKE opérateur. Ceci est inefficace et limité en fonctionnalité. MySQL doit généralement effectuer une analyse complète de la table pour identifier les enregistrements correspondant à votre requête.

Les requêtes de texte intégral utilisent un index spécialement créé pour améliorer les performances. Cela permet également à MySQL de garder une trace des mots dans votre ensemble de données, facilitant la recherche en langage naturel.

Configuration de la recherche en texte intégral

Vous ne pouvez utiliser la recherche de texte intégral qu’avec des colonnes qui ont un index de texte intégral. Utilisez le FULLTEXT clause dans CREATE TABLE instructions pour configurer cela lorsque vous amorcez votre schéma de base de données. Vous pouvez également l’utiliser avec ALTER TABLE pour ajouter un index de recherche aux colonnes existantes.

CREATE TABLE articles(content TEXT, FULLTEXT (content));
 
ALTER TABLE articles ADD FULLTEXT (content);

Avec l’index en place, vous êtes prêt à commencer à interroger votre base de données.

Utilisation des recherches en texte intégral

Les recherches en texte intégral commencent par un WHERE clause. Tu utilises MATCH AGAINST au lieu de LIKE. Vous devez indiquer les colonnes indexées à mettre en correspondance, ainsi que la requête à rechercher.

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' IN NATURAL LANGUAGE MODE);

Cela effectuera une recherche en texte intégral des articles à l’aide de la requête database engine. En spécifiant IN NATURAL LANGUAGE MODE, MySQL est chargé d’interpréter la requête littéralement, sans la traiter d’aucune façon. Il s’agit du mode de recherche par défaut si aucun n’est spécifié.

Classement par pertinence des résultats

L’un des avantages de la recherche en texte intégral est qu’elle vous permet de classer les enregistrements renvoyés par pertinence. Ce n’est pas possible avec un LIKE mettre en doute. Vous pouvez utiliser un MATCH ... AGAINST clause dans le cadre d’un SQL SELECT. La colonne virtuelle renvoyée contiendra un score de pertinence, de 0 à 1, indiquant à quel point l’enregistrement correspond à la requête de recherche.

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles ORDER BY relevance DESC

Cette requête renvoie d’abord les résultats les plus pertinents. Cela permet à votre application de répondre aux attentes des utilisateurs quant aux performances des systèmes de recherche en langage naturel.

MySQL calcule les scores pertinents pour la recherche en évaluant plusieurs facteurs différents. Ceux-ci incluent le nombre d’enregistrements qui correspondent à la requête, ainsi que le nombre de fois où la requête se produit dans chaque enregistrement. Un résultat avec plusieurs correspondances exactes pour la requête sera classé plus haut que celui qui ne contient qu’une partie de la requête.

Lors de l’utilisation MATCH ... AGAINST dans un SELECT déclaration, vous n’avez pas besoin de la répéter dans le WHERE clause. Vous pouvez filtrer manuellement les résultats pour inclure uniquement les enregistrements avec un score de pertinence différent de zéro.

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles WHERE relevance > 0 ORDER BY relevance DESC

Mode d’extension des requêtes

Le langage naturel n’est pas le seul mode de recherche en texte intégral pris en charge. Le mode d’extension des requêtes est une alternative qui permet d’élargir la gamme des résultats de recherche. Il pondère automatiquement la requête de recherche par rapport aux termes les plus pertinents.

Une recherche étendue commence par trouver les enregistrements qui contiennent une correspondance pour la requête. Ces enregistrements sont ensuite vérifiés pour identifier les mots les plus pertinents. La base de données s’exécute ensuite un autre recherche, cette fois en se basant sur les mots pertinents au lieu de la requête d’origine. Cela se traduit généralement par un plus grand nombre d’enregistrements renvoyés tout en conservant un degré de pertinence acceptable.

Voici comment activer le mode d’extension des requêtes:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' WITH QUERY EXPANSION)

Mode booléen

Le dernier mode de recherche en texte intégral est le mode booléen. Cela vous permet d’inclure des modificateurs booléens dans votre requête. Vous pouvez utiliser ce mode lorsque vous avez besoin d’un contrôle avancé sur la logique de correspondance.

Vous pouvez exiger qu’un mot soit présent dans chaque résultat en le préfixant avec +. Utilisation - pour exclure les résultats contenant le mot. D’autres opérateurs peuvent être utilisés pour faire correspondre des parties de mots, créer des sous-expressions et créer des mots réduire le score de pertinence. Ce dernier peut être utile pour masquer les termes de «bruit». Si vous ne spécifiez pas d’opérateur, cela implique que le mot sera référencé dans la requête avec OR.

SELECT * FROM articles WHERE MATCH (content) AGAINST ('+data* engine -sqlite' IN BOOLEAN MODE)

Cette requête ferait apparaître les articles avec au moins un mot commençant par data où sqlite n’est pas mentionné. Chaque résultat peut ou non inclure le mot engine.

Le mode booléen vous permet de créer des recherches puissantes en utilisant votre propre logique. Une mise en garde est qu’il ne prend pas en charge la notation de la pertinence des recherches. C’est le compromis qui permet aux utilisateurs d’effectuer des recherches booléennes. Le classement des résultats peut ne pas correspondre aux attentes humaines.

Configuration de la recherche en texte intégral

MySQL est livré avec plusieurs options de configuration en texte intégral qui vous permettent d’affiner la façon dont les recherches sont menées. Voici quelques-uns des plus importants.

• innodb_ft_min_token_size – Définit la longueur minimale des mots pour les termes indexés. Les mots comportant moins de caractères que cette valeur ne seront pas ajoutés à l’index, vous ne pourrez donc pas les rechercher. La valeur par défaut est 3, qui exclut les mots extrêmement courants tels que a, an et I. Ce paramètre s’applique aux tables InnoDB; utilisation ft_min_word_len pour MyISAM.
• innodb_ft_max_token_size – Similaire à innodb_ft_min_token_size, cela définit le maximum longueur des mots indexés. Les mots plus longs ne pourront pas être recherchés. Utilisation ft_max_word_len pour les tables MyISAM.
• innodb_ft_enable_stopword – Ce paramètre, activé par défaut, vous permet de contrôler si MySQL doit filtrer les «mots vides». Les mots vides sont des mots très couramment utilisés qui pourraient indûment influencer les résultats de la recherche. La liste de mots vides par défaut contient 36 phrases fréquentes.
• innodb_ft_user_stopword_table – Vous pouvez définir cette valeur sur le nom d’une table de base de données à partir de laquelle MySQL doit générer la liste de mots vides. Cette table doit avoir un seul VARCHAR colonne appelée value. Ajoutez vos mots vides à exclure en tant qu’enregistrements dans ce tableau.

Ces paramètres sont généralement définis dans le fichier de configuration de votre serveur MySQL. L’emplacement varie selon la plate-forme; il peut souvent être trouvé à /etc/mysql/my.cnf. Vous devrez redémarrer le service MySQL après avoir modifié les paramètres.

Une fois le serveur sauvegardé, reconstruisez les index de texte intégral de votre table. Vous devez le faire pour que vos données soient réindexées à l’aide de la configuration actuelle. Sinon, les données précédemment indexées continueront d’être utilisées.

Pour réindexer une table InnoDB, exécutez OPTIMIZE TABLE my_table. Pour les tables MyISAM, utilisez REPAIR TABLE my_table QUICK. Les index de recherche seront ensuite reconstruits pour que vos modifications de configuration prennent effet.

Résumé

La recherche en texte intégral MySQL est activée en ajoutant un FULLTEXT index de vos champs de recherche. Vous utilisez ensuite MATCH ... AGAINST avec l’un des trois modes de recherche pour obtenir vos résultats. Les requêtes en langage naturel renvoient un score de pertinence de recherche que vous pouvez utiliser pour classer vos résultats.

La recherche en texte intégral offre un comportement de recherche plus puissant qu’un LIKE déclaration. Il est également beaucoup plus performant, en particulier sur les grands ensembles de données, car tout le texte est indexé à l’avance.