typestar

search_index.sql en SQL

Una búsqueda que funciona: un índice sobre una tabla existente, sincronizado y con ranking.

-- Búsqueda de texto completo sobre posts, de vacío a resultados con ranking.

DROP TRIGGER IF EXISTS posts_search_insert;
DROP TRIGGER IF EXISTS posts_search_delete;
DROP TRIGGER IF EXISTS posts_search_update;
DROP TABLE IF EXISTS posts_search;

CREATE VIRTUAL TABLE posts_search USING fts5(
    title,
    body,
    content = 'posts',
    content_rowid = 'id',
    tokenize = 'porter unicode61'
);

CREATE TRIGGER posts_search_insert
AFTER INSERT ON posts
BEGIN
    INSERT INTO posts_search (rowid, title, body)
    VALUES (NEW.id, NEW.title, NEW.body);
END;

CREATE TRIGGER posts_search_delete
AFTER DELETE ON posts
BEGIN
    INSERT INTO posts_search (posts_search, rowid, title, body)
    VALUES ('delete', OLD.id, OLD.title, OLD.body);
END;

CREATE TRIGGER posts_search_update
AFTER UPDATE ON posts
BEGIN
    INSERT INTO posts_search (posts_search, rowid, title, body)
    VALUES ('delete', OLD.id, OLD.title, OLD.body);
    INSERT INTO posts_search (rowid, title, body)
    VALUES (NEW.id, NEW.title, NEW.body);
END;

INSERT INTO posts (id, title, body, published_at)
VALUES (1, 'Indexes and plans',
        'An index the planner never chooses is only a write cost.',
        '2026-07-01'),
       (2, 'Window functions',
        'A window sees the rows around it without collapsing them.',
        '2026-07-14'),
       (3, 'Full-text search',
        'FTS5 indexes words, so a search does not scan every row.',
        '2026-07-22');

-- Poblar desde la tabla de contenido de una sola vez.
INSERT INTO posts_search (posts_search)
VALUES ('rebuild');

-- Resultados con ranking y vista previa; title pesa más que body.
SELECT
    p.id,
    p.title,
    p.published_at,
    ROUND(BM25(posts_search, 8.0, 1.0), 3) AS score,
    SNIPPET(posts_search, 1, '[', ']', '...', 10) AS preview
FROM posts_search
JOIN posts AS p
    ON p.id = posts_search.rowid
WHERE posts_search MATCH 'index* OR search'
ORDER BY rank
LIMIT 10;

Cómo funciona

  1. content= apunta el índice a la tabla para que el texto se guarde una sola vez.
  2. Tres triggers lo mantienen al día; rebuild lo recupera si alguno falla.
  3. La consulta final pesa el título, ordena por bm25 y devuelve un snippet.

Palabras clave y builtins usados aquí

El intento, en números

Líneas
66
Caracteres a escribir
1786
Tokens
316
Ritmo de tres estrellas
100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 190 segundos.

Escribe este fragmento

Paso 1 de 2 en Bis; paso 13 de 14 en JSON y búsqueda.

← Anterior Siguiente →