Risposta.

Storia della domanda:
Inizialmente, SQL veniva utilizzato per lavorare principalmente con dati strutturati, dove la ricerca nei campi di testo era limitata a semplici operazioni come LIKE. Con l'aumento dei volumi di informazioni testuali, è emersa la necessità di effettuare ricerche rapide e flessibili su testi lunghi: articoli, messaggi, blog, ecc.

Problema:
Gli strumenti standard di SQL (LIKE/ILIKE) funzionano male con grandi volumi di testo e non riescono a trovare parole in base alla rilevanza, tenendo conto della morfologia o della distanza tra le parole. Ciò può portare a perdita di prestazioni e risposte troppo lente durante la ricerca.

Soluzione:
Per tali compiti si utilizzano meccanismi di ricerca full-text (Full-Text Search, FTS), incorporati nei database, come Full-Text Index e operatori speciali (CONTAINS, MATCH AGAINST, tsvector, tsquery). Questi indici costruiscono una "scheda delle parole" ("indice invertito"), accelerando la ricerca su testi di decine di volte.

Esempio di codice (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"');

Caratteristiche chiave:

Funziona su speciali indici full-text, separati da quelli normali.
Supporta query con rilevanza, lemmatizzazione, riconoscimento delle stop-word e condizioni complesse (NO, OR, prossimità).
Richiede la manutenzione dell'indice in caso di modifiche massicce ai dati — indicizzazione periodica.

Domande trabocchetto.

Qual è la differenza tra la ricerca con LIKE e la ricerca full-text?

LIKE è una semplice operazione di confronto con un modello, che non utilizza indici testuali, lenta per grandi volumi. La ricerca full-text utilizza un indice speciale e può tener conto della morfologia, della rilevanza.

Esempio:

SELECT * FROM articles WHERE body LIKE '%database%';
-- lento, nessun ranking

SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');
-- veloce, con ranking

Cosa succede all'indice full-text durante inserimenti o eliminazioni di massa?

Dopo modifiche di massa nei campi di testo, l'indice diventa obsoleto (a volte — auto-aggiornamento, a volte — manualmente), e occorre ricostruire l'indice per ripristinare le prestazioni.

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

È possibile utilizzare indici full-text per la ricerca in colonne di tipo JSON o XML?

No, la maggior parte dei motori di ricerca full-text non ha supporto diretto per le strutture JSON/XML; questi dati devono essere estratti in un campo stringa o devono essere utilizzati parser/strumenti esterni speciali (ad esempio, Elasticsearch).

Errori comuni e anti-pattern

Utilizzare l'operatore LIKE '%word%' su grandi tabelle — prestazioni catastrofiche
La reindicizzazione non viene eseguita, la ricerca diventa irrilevante
Non si tengono in considerazione le specificità delle lingue e delle stop-word
Indicizzare immediatamente diversi gigabyte di dati senza risorse aggiuntive

Esempi dalla vita reale

Caso negativo

Un'azienda ha memorizzato decine di milioni di articoli. Per la ricerca si utilizzava LIKE '%word%'. Il dipartimento IT si lamentava di timeout regolari, gli utenti attendevano risultati per oltre 10 minuti.

Pro:

Nessuna necessità di licenze o configurazioni aggiuntive
Implementazione semplice

Contro:

Prestazioni scarse, specialmente su grandi volumi
Tempi di risposta irrealistici del sistema
Risultati di ricerca errati (senza considerare le forme della parola)

Caso positivo

È stato implementato il Full-Text Search (FULLTEXT INDEX in MySQL). La ricerca ha iniziato a restituire risultati fino a 100 volte più velocemente, è stata aggiunta la possibilità di cercare "parole simili" e frasi, è stato implementato il ranking.