Buscar en el texto

En resumen: La búsqueda de texto completo encuentra palabras en el texto con un índice invertido que guarda, para cada palabra, las filas que la contienen, en lugar de leerlo todo con LIKE.

Problema

El cuadro de búsqueda

El sitio recibe un cuadro de búsqueda para las reseñas. La primera idea es LIKE. Un cliente busca «cafe», como lo escribe casi todo el mundo, sin acento:

SQLbase de datos: tienda

SELECT id, body FROM reviews WHERE body LIKE '%cafe%';

SELECT id, body FROM reviews WHERE body LIKE '%cafe%';

▶ EjecutarRestaurar la consultaBase nueva

Nada, aunque hay reseñas que dicen «café». En SQLite LIKE ignora la diferencia entre mayúsculas y minúsculas solo en el alfabeto latino básico, y para él e y é son letras distintas. Otra búsqueda:

SQLbase de datos: tienda

SELECT id, body FROM reviews WHERE body LIKE '%sal%';

SELECT id, body FROM reviews WHERE body LIKE '%sal%';

▶ EjecutarRestaurar la consultaBase nueva

«sal» encuentra «salado», porque LIKE busca un trozo del texto, no una palabra. Y el % del principio impide usar un índice: con un millón de reseñas la base de datos lee un millón de textos en cada búsqueda.

Más precisamente, para curiosos

Lo mismo pasa con las mayúsculas acentuadas: LIKE '%CAFÉ%' no encuentra «café», porque É y é son distintas para él, y lo mismo ocurre con Ñ y ñ. El índice de texto completo de abajo pasa todas las letras a minúsculas, también esas, y además quita los acentos.

La idea

El índice invertido

La búsqueda de texto completo hace lo contrario que la tabla. La tabla dice «la reseña 3 contiene las palabras el, mejor, café, del, barrio». El índice invertido dice «la palabra café está en las reseñas 3, 6 y 13». Buscar una palabra es una lectura en el índice, no un recorrido por los textos.

El texto se divide primero en palabras (un tokenizador). unicode61 corta en los espacios y en los signos de puntuación, pasa todas las letras a minúsculas, en cualquier alfabeto, y quita los acentos. SQLite guarda el índice en una tabla virtual con fts4:

SQLbase de datos: tienda

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61);
INSERT INTO review_search (docid, body) SELECT id, body FROM reviews;

SELECT docid, body FROM review_search WHERE review_search MATCH 'cafe';

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61); INSERT INTO review_search (docid, body) SELECT id, body FROM reviews; SELECT docid, body FROM review_search WHERE review_search MATCH 'cafe';

▶ EjecutarRestaurar la consultaBase nueva

docid es el número de la fila en el índice. Aquí es el número de la reseña, para poder unirlo con reviews. MATCH es la búsqueda en el índice, no = ni LIKE. Y ahora sí se encuentran las tres reseñas con «café» que LIKE no veía: «cafe», «café» y «Café» son la misma palabra en el índice.

Así se ven las palabras del índice (fts4aux es una tabla que muestra las palabras y en cuántos documentos está cada una):

SQLbase de datos: tienda

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search);

SELECT term, documents
FROM review_terms
WHERE col = '*' AND term LIKE 'ca%'
ORDER BY term;

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search); SELECT term, documents FROM review_terms WHERE col = '*' AND term LIKE 'ca%' ORDER BY term;

▶ EjecutarRestaurar la consultaBase nueva

Fíjate en cafe, sin acento, y en calienta y caliente: son dos palabras distintas.

La idea

Formas de las palabras

En el índice «cuchillo» y «cuchillos» son dos palabras distintas. La ayuda más sencilla es un prefijo: cuchill* significa cualquier palabra que empiece por «cuchill»:

SQLbase de datos: tienda

SELECT docid, body FROM review_search WHERE review_search MATCH 'cuchillo';
SELECT docid, body FROM review_search WHERE review_search MATCH 'cuchill*';

SELECT docid, body FROM review_search WHERE review_search MATCH 'cuchillo'; SELECT docid, body FROM review_search WHERE review_search MATCH 'cuchill*';

▶ EjecutarRestaurar la consultaBase nueva

Un prefijo es tosco: cuchill* también encontraría «cuchillería». Los buscadores de verdad tienen analizadores para cada idioma que reducen las palabras a su raíz (stemming): «tomates» pasa a ser «tomat». SQLite solo lo tiene para el inglés (tokenize=porter), y Elasticsearch y OpenSearch lo tienen para muchos idiomas. Las formas irregulares y los géneros («fresco», «fresca») hacen tropezar incluso a esos.

Pruébalo

Lo fresco

Encuentra en el índice todas las reseñas que llaman fresca a una cosa (fresco, fresca), sea cual sea la terminación de la palabra (las columnas docid y body).

SQLbase de datos: tienda

SELECT docid, body FROM review_search LIMIT 3;

SELECT docid, body FROM review_search LIMIT 3;

Comprobar▶ Ejecutar

La idea

Más de una palabra

Dos palabras seguidas significan AND: la reseña tiene que tener las dos. OR significa al menos una de ellas:

SQLbase de datos: tienda

-- Las dos palabras (AND va implícito)
SELECT docid, body FROM review_search WHERE review_search MATCH 'cafe entrega';
-- Una u otra
SELECT docid, body FROM review_search WHERE review_search MATCH 'manzanas OR tomates';

-- Las dos palabras (AND va implícito) SELECT docid, body FROM review_search WHERE review_search MATCH 'cafe entrega'; -- Una u otra SELECT docid, body FROM review_search WHERE review_search MATCH 'manzanas OR tomates';

▶ EjecutarRestaurar la consultaBase nueva

Una frase entre comillas busca las palabras una junto a otra y en ese orden. NEAR/3 busca las dos palabras con 3 palabras de distancia como máximo:

SQLbase de datos: tienda

-- Justo esta frase, las palabras una junto a otra
SELECT docid, body FROM review_search WHERE review_search MATCH '"como nueva"';
-- Las dos palabras con 3 palabras de distancia como máximo
SELECT docid, body FROM review_search WHERE review_search MATCH 'sartén NEAR/3 buena';

-- Justo esta frase, las palabras una junto a otra SELECT docid, body FROM review_search WHERE review_search MATCH '"como nueva"'; -- Las dos palabras con 3 palabras de distancia como máximo SELECT docid, body FROM review_search WHERE review_search MATCH 'sartén NEAR/3 buena';

▶ EjecutarRestaurar la consultaBase nueva

NOT quita los documentos con una palabra dada: café NOT caducado.

Más precisamente, para curiosos

FTS4 tiene dos sintaxis de consulta. En la estándar «sin la palabra» se escribe con un menos (café -caducado), y en la ampliada con NOT. sql.js, que ejecuta SQLite en el navegador, usa la ampliada, y allí -caducado es simplemente la palabra «caducado». La ampliada también admite paréntesis: (manzanas OR tomates) NOT roto. En Elasticsearch lo mismo es una consulta bool con must, should y must_not.

Pruébalo

Café sin quejas

Encuentra las reseñas sobre el café que no mencionan «caducado» (las columnas docid y body).

SQLbase de datos: tienda

SELECT docid, body FROM review_search WHERE review_search MATCH 'café';

SELECT docid, body FROM review_search WHERE review_search MATCH 'café';

Comprobar▶ Ejecutar

La idea

Fragmentos y orden

Un buscador muestra un fragmento con la palabra marcada. snippet lo hace solo:

SQLbase de datos: tienda

SELECT docid, snippet(review_search, '[', ']', '…') AS found
FROM review_search
WHERE review_search MATCH 'pan';

SELECT docid, snippet(review_search, '[', ']', '…') AS found FROM review_search WHERE review_search MATCH 'pan';

▶ EjecutarRestaurar la consultaBase nueva

Los primeros resultados deberían ser los más relevantes. Los buscadores de verdad los ordenan por BM25: cuanto más rara es la palabra en todos los documentos y cuanto más a menudo está en este, más arriba, y un texto corto con la palabra gana a uno largo. La tabla fts4aux de la tarjeta del índice da los números para eso: en cuántos documentos está cada palabra. FTS4 no ordena por sí solo (FTS5 y Elasticsearch llevan BM25 incorporado), así que aquí el orden sale de ORDER BY.

Ejercicio

Buscar con los productos

1. El índice guarda solo el texto. El nombre del producto y la valoración están en las tablas.

Encuentra las reseñas sobre el café con el nombre del producto y la valoración (las columnas name, rating y body), de la valoración más alta hacia abajo.

SQLbase de datos: tienda

SELECT docid FROM review_search WHERE review_search MATCH 'café';

SELECT docid FROM review_search WHERE review_search MATCH 'café';

Comprobar▶ Ejecutar

2. El índice es una tabla aparte y no sabe por sí solo cuándo cambia reviews. Un trigger lo actualiza con cada reseña nueva:

SQLbase de datos: tienda

CREATE TRIGGER review_added AFTER INSERT ON reviews
BEGIN
  INSERT INTO review_search (docid, body) VALUES (new.id, new.body);
END;

INSERT INTO reviews (product_id, customer_id, rating, body, created_at)
VALUES (17, 4, 5, 'El café llegó recién tostado.', '2026-10-25');

SELECT docid, body FROM review_search WHERE review_search MATCH 'café';

CREATE TRIGGER review_added AFTER INSERT ON reviews BEGIN INSERT INTO review_search (docid, body) VALUES (new.id, new.body); END; INSERT INTO reviews (product_id, customer_id, rating, body, created_at) VALUES (17, 4, 5, 'El café llegó recién tostado.', '2026-10-25'); SELECT docid, body FROM review_search WHERE review_search MATCH 'café';

▶ EjecutarRestaurar la consultaBase nueva

Escribe un trigger review_removed que quite la reseña de review_search cuando se borre de reviews.

SQLbase de datos: tienda

SELECT COUNT(*) FROM review_search;

SELECT COUNT(*) FROM review_search;

Comprobar▶ Ejecutar

La idea

Cuándo un buscador aparte

Para buscar en unos miles o millones de textos basta el índice de texto completo de la base de datos: FTS en SQLite, tsvector y un índice GIN en PostgreSQL, FULLTEXT en MySQL. Un buscador aparte (Elasticsearch, OpenSearch, Meilisearch, Typesense) compensa cuando la búsqueda es la tarea principal del sitio: analizadores para muchos idiomas, corrección de errores de escritura («cafee» → «café»), filtros por categoría con los recuentos al lado, sugerencias mientras escribes y muchas máquinas. Entonces los datos se copian allí, normalmente desde un flujo de eventos, y la verdad se queda en la base de datos.

Más precisamente, para curiosos

En PostgreSQL la misma búsqueda es WHERE to_tsvector('spanish', body) @@ to_tsquery('spanish', 'café') con el índice CREATE INDEX ON reviews USING gin (to_tsvector('spanish', body)). En Elasticsearch el campo recibe "analyzer": "spanish", y la búsqueda es {"query": {"match": {"body": "café"}}}.

Test

Ponte a prueba

1. ¿Por qué LIKE '%café%' es lento con un millón de reseñas?

El % del principio impide usar un índice, y la base de datos lee cada texto de principio a finPorque el café es popularPorque LIKE es un comando antiguo

2. En el índice de texto completo, MATCH 'cuchillo' no encuentra «Los cuchillos cortan de maravilla». ¿Por qué?

La reseña es malaLa mayúscula de «Los»«cuchillos» es otra palabra en el índice, no «cuchillo»

3. ¿Qué hace un índice invertido?

Ordena las reseñas por valoraciónGuarda los textos al revésPara cada palabra guarda en qué documentos (y en qué lugar de ellos) aparece

4. Las reseñas se guardan en reviews, y la búsqueda en review_search. ¿Qué pasa si alguien borra una reseña y no hay un trigger?

Nada, el índice se da cuenta soloLa búsqueda sigue encontrando la reseña borradaLa tabla reviews vuelve

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: INSERT: insertar filas · DELETE: borrar filas · Triggers · Índices
• Continúa en: Buscar por significado · Cómo elegir una base de datos
• Ejercicios sobre el tema: Tipos de bases de datos