L’intérêt d’utiliser un index full-text

Un index full-text (ou index de texte intégral) permet d’effectuer des recherches performantes sur des colonnes contenant de grandes quantités de texte. L’exploration de documents rédigés, de descriptions, de commentaires en devient plus rapide et plus simple. En effet, les outils habituels de requêtage textuel peuvent se montrer limités sur de grandes chaînes de caractères. De plus, un index full-text permet la recherche partielle ou par proximité, de mots.

Effectuons les manipulations suivantes sous SQL Server 

Créons une table ‘commentaire’, destinée à contenir des textes longs :

CREATE TABLE commentaire (

Id_commentaire INT IDENTITY(1,1) NOT NULL,

titre NVARCHAR(100),

contenu NVARCHAR(MAX),

CONSTRAINT PK_commentaire PRIMARY KEY (Id_commentaire)

);

Remplissons-la avec les données suivantes :

INSERT INTO commentaire (titre, contenu)

VALUES

('Le petit prince', 'Envoi parfait, très beau livre et de bonne qualité, un plaisir à relire et à faire lire dans cette édition collector.'),

('Les misérables', 'Rien à redire sur ce livre ! C est agréable de lire dans ce format qui donne envie de dévorer le récit.'),

('Les Quatre Accords toltèques', 'Ce livre, qui explore la sagesse des chamans mexicains, offre un code de conduite simple mais puissant pour mener une vie de liberté et de bonheur.');

Créons un catalogue full-text :

CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;

Créons maintenant un index full-text sur la colonne ‘contenu’ :

CREATE FULLTEXT INDEX ON commentaire(contenu)

KEY INDEX PK_commentaire;

On peut maintenant effectuer une recherche full-text :

Une fois l'index créé, vous pouvez effectuer des recherches avancées avec la fonction CONTAINS.

SELECT *

FROM commentira

WHERE CONTAINS(contenu, 'qualité');

>Cette requête retourne les lignes contenant le mot ‘qualité’ dans la colonne Contenu.

On peut aussi chercher une phrase exacte :

SELECT *

FROM commentaire

WHERE CONTAINS(contenu, ‘C'est agréable’);

>Cette requête retourne les lignes contenant la phrase ‘C'est agréable’.

Ou chercher des synonymes, par exemple :

SELECT *

FROM commentaire

WHERE FREETEXT(Contenu, 'offre');

>Cette requête retourne les lignes avec des mots proches ou liés au mot ‘offre’, comme ‘proposition’, ‘enchère’ ou ‘don’.

Pourquoi utiliser un index full-text ?

Les recherches standard (LIKE '%mot%') deviennent inefficaces pour des colonnes contenant de grandes quantités de texte.

Les index full-text sont optimisés pour les recherches textuelles complexes. Ce type de recherche offre de meilleures performances et des fonctionnalités avancées.

Inconvénients des index full-text :

Si un index full-text peut permettre de faire des recherches textuelles plus efficaces, lorsqu’on a des textes longs dans une table, les index full-text ont aussi des inconvénients :

• Un index full-text ne peut être utilisé que sur certains types de données (comme NVARCHAR, VARCHAR, ou TEXT).

• La maintenance de cet index peut ralentir les opérations d'écriture (INSERT, UPDATE) sur les colonnes indexées.

C’est pourquoi il est conseillé d’utiliser LIKE pour des recherches de motifs textuels simples, et sur de petites quantités de données, tandis qu’un index full-text est très approprié pour faire des recherches textuelles complexes, sur des blocs de textes. L’utilisation d’un index full-text permet aussi de prendre en compte le sens des mots recherchés, tandis que le mot-clef LIKE ne peut renvoyer que des enregistrements qui correspondent strictement au motif donné.

--

Retrouvez toutes nos formations en SQL sur https://www.apprendresql.com/ .