Respuesta.

Historia de la pregunta:
Inicialmente, SQL se utilizaba principalmente para trabajar con datos estructurados, donde la búsqueda en campos de texto estaba limitada a operaciones simples como LIKE. Con el aumento de los volúmenes de información textual, surgió la necesidad de realizar búsquedas de manera rápida y flexible en textos grandes: en artículos, mensajes, blogs, etc.

Problema:
Las herramientas SQL estándar (LIKE/ILIKE) funcionan mal con grandes volúmenes de texto y no pueden encontrar palabras de manera efectiva según la relevancia, considerando la morfología o la distancia entre palabras. Esto puede llevar a una pérdida de rendimiento y tiempos de respuesta demasiado largos durante la búsqueda.

Solución:
Para tales tareas se utilizan mecanismos de búsqueda de texto completo (Full-Text Search, FTS), integrados en bases de datos, como el índice de texto completo y operadores especiales (CONTAINS, MATCH AGAINST, tsvector, tsquery). Estos índices crean una “ficha de palabras” (índice invertido), acelerando la búsqueda en textos por decenas de veces.

Ejemplo de código (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"');

Características clave:

Funciona sobre la base de índices de texto completo especiales, separados de los normales.
Soporta consultas con relevancia, lematización, reconocimiento de stop-words y condiciones complejas (NO, O, proximidad).
Requiere mantener el índice durante cambios masivos de datos, lo que implica reindexación periódica.

Preguntas capciosas.

¿Cuál es la diferencia entre la búsqueda con LIKE y la búsqueda de texto completo?

LIKE es una operación simple de comparación con un patrón que no utiliza índices de texto, es lenta para grandes volúmenes. La búsqueda de texto completo utiliza un índice especial y puede considerar morfología y relevancia.

Ejemplo:

SELECT * FROM articles WHERE body LIKE '%database%';
-- lento, sin clasificación

SELECT * FROM articles WHERE MATCH(body) AGAINST ('database');
-- rápido, con clasificación

¿Qué pasa con el índice de texto completo durante inserciones o eliminaciones masivas?

Después de cambios masivos en los campos de texto, el índice se vuelve obsoleto (a veces se actualiza automáticamente, a veces manualmente), y es necesario reconstruir el índice para recuperar el rendimiento.

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

¿Se pueden utilizar índices de texto completo para buscar en columnas tipo JSON o XML?

No, la mayoría de los motores de texto completo no ofrecen soporte directo para estructuras JSON/XML; esos datos deben extraerse a un campo de texto o utilizar parsers/especiales herramientas externas (como Elasticsearch).

Errores típicos y anti-patrones

Uso del operador LIKE '%word%' en tablas grandes — rendimiento catastrófico
No se realiza reindexación, la búsqueda se vuelve irrelevante
No se consideran las particularidades de los idiomas y las stop-words
Se indexan varios gigabytes de datos sin recursos adicionales

Ejemplo de la vida real

Caso negativo

Una empresa almacenaba decenas de millones de registros de artículos. Se utilizaba LIKE '%palabra%' para la búsqueda. El departamento de TI se quejaba de tiempos de espera regulares, los usuarios esperaban por resultados durante más de 10 minutos.

Ventajas:

No es necesario licencias adicionales ni configuraciones
Implementación simple

Desventajas:

Rendimiento débil, especialmente con grandes volúmenes
Tiempos de respuesta del sistema poco realistas
Resultados de búsqueda incorrectos (sin considerar las formas de la palabra)

Caso positivo

Se implementó Búsqueda de Texto Completo (FULLTEXT INDEX en MySQL). La búsqueda comenzó a devolver resultados hasta 100 veces más rápido, se agregó la capacidad de buscar palabras y frases “similares” y se añadió la clasificación.