Réponse.

Historique de la question :
Initialement, SQL était utilisé principalement pour travailler avec des données structurées, où la recherche dans des champs de texte était limitée à de simples opérations comme LIKE. Avec l'augmentation des volumes d'informations textuelles, il est devenu nécessaire d'effectuer des recherches rapidement et de manière flexible sur de grands textes : articles, messages, blogs, etc.

Problème :
Les outils standard de SQL (LIKE/ILIKE) fonctionnent mal avec de grands volumes de texte et ne savent pas trouver efficacement des mots en fonction de leur pertinence, en tenant compte de la morphologie ou de la distance entre les mots. Cela peut entraîner une perte de performance et des temps de réponse trop longs lors des recherches.

Solution :
Pour de telles tâches, des mécanismes de recherche en texte intégral (Full-Text Search, FTS) intégrés dans les SGBD sont utilisés, tels que les index de texte intégral et des opérateurs spéciaux (CONTAINS, MATCH AGAINST, tsvector, tsquery). Ces index construisent une "carte des mots" ("index inversé"), accélérant la recherche dans les textes de plusieurs dizaines de fois.

Exemple de code (SQL Server) :

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Documents(Content) KEY INDEX PK_Documents;

SELECT * FROM Documents WHERE CONTAINS(Content, '"SQL programming"');

Caractéristiques clés :

Fonctionne sur la base d'index de texte intégral spéciaux, séparés des index ordinaires.
Prend en charge les requêtes avec pertinence, lemmatisation, reconnaissance des mots vides et conditions complexes (NE, OU, proximité).
Nécessite le maintien de l'index lors de modifications massives de données — réindexation périodique.

Questions piégeuses.

Quelle est la différence entre une recherche utilisant LIKE et une recherche en texte intégral ?

LIKE est une simple opération de comparaison avec un modèle, qui n'utilise pas d'index sur le texte, lente pour de grands volumes. La recherche en texte intégral utilise un index spécial et peut tenir compte de la morphologie, de la pertinence.

Exemple :

SELECT * FROM articles WHERE body LIKE '%database%';
-- lent, pas de classement

SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');
-- rapide, avec classement

Que se passe-t-il avec l'index de texte intégral lors d'insertion ou de suppression massives ?

Après des modifications massives dans les champs de texte, l'index devient obsolète (parfois — mise à jour automatique, parfois — manuellement), et il est nécessaire de reconstruire l'index pour restaurer les performances.

-- Pour MSSQL
ALTER FULLTEXT INDEX ON Documents START FULL POPULATION;

Peut-on utiliser des index de texte intégral pour rechercher dans des colonnes de type JSON ou XML ?

Non, la plupart des moteurs de recherche en texte intégral ne prennent pas en charge directement les structures JSON/XML ; ces données doivent être extraites dans un champ de chaîne, ou des parseurs spéciaux/outils externes doivent être appliqués (par exemple, Elasticsearch).

Erreurs typiques et anti-patterns

Utilisation de l'opérateur LIKE '%mot%' sur de grandes tables — performance catastrophique
La réindexation n'est pas effectuée, la recherche devient non pertinente
Les particularités des langues et des mots vides ne sont pas prises en compte
Indexation de plusieurs gigaoctets de données sans ressources supplémentaires

Exemple de la vie réelle

Cas négatif

Une entreprise a stocké des dizaines de millions d'enregistrements d'articles. Pour la recherche, LIKE '%mot%' était utilisé. Le service informatique se plaignait de délais d'attente réguliers, les utilisateurs attendaient des résultats pendant plus de 10 minutes.

Avantages :

Pas besoin de licences ou de configurations supplémentaires
Mise en œuvre simple

Inconvénients :

Faible performance, surtout pour de grands volumes
Délais de réponse irréalistes du système
Résultats de recherche incorrects (pas de prise en compte des formes des mots)

Cas positif

Implémentation de la recherche en texte intégral (FULLTEXT INDEX dans MySQL). La recherche est devenue jusqu'à 100 fois plus rapide, avec la possibilité de rechercher des mots et phrases « similaires », un classement a été ajouté.