Índices parciales en SQLite: CREATE INDEX ... WHERE

Un índice parcial cubre solo algunas filas

Un índice normal en SQLite guarda una entrada por cada fila de la tabla. En cambio, un índice parcial en SQLite solo indexa las filas que cumplen una condición WHERE que tú defines al crearlo. Resultado: un índice más pequeño, menos páginas que recorrer y menos trabajo en cada INSERT o UPDATE que no toque esa porción indexada.

La sintaxis es un CREATE INDEX de toda la vida, pero con un WHERE al final:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending solo guarda entradas para las filas donde status = 'pending'. Los pedidos enviados, cancelados o reembolsados ni siquiera aparecen. Si el 95% de tu tabla orders es histórico y casi siempre consultas los pedidos abiertos, te queda un índice 20× más pequeño con la misma velocidad de consulta.

Cuándo lo va a usar realmente el planificador

Un índice parcial solo es aprovechable cuando SQLite puede demostrar que tu consulta se restringe a las mismas filas que cubre el índice. La forma más limpia de conseguirlo es repetir la cláusula WHERE del índice dentro de la consulta:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

El plan debería mencionar USING INDEX idx_orders_pending. Si quitas status = 'pending' de la consulta, el planificador vuelve a hacer un escaneo completo de la tabla: no tiene forma de saber que la consulta se mantiene dentro del subconjunto indexado.

Regla práctica: el WHERE de la consulta debe implicar el WHERE del índice. La igualdad sobre la misma columna y el mismo valor es el caso seguro y evidente. Las desigualdades y los OR se vuelven más enredados; verifícalo con EXPLAIN QUERY PLAN.

Por qué vale la pena: las tres ventajas

Tres razones concretas por las que los índices parciales valen la pena:

1. Ocupan menos en disco. Solo se almacenan las filas que coinciden. Si tu caso es "el 1% de la tabla es el caliente", el índice pesa más o menos el 1% de uno completo.
2. Escrituras más baratas. Los INSERT y UPDATE solo tocan el índice cuando la fila cumple el filtro. Un insert con status = 'shipped' en la tabla anterior no toca idx_orders_pending para nada.
3. Misma velocidad de búsqueda. Una búsqueda en B-tree es logarítmica respecto al tamaño del índice. Índice más pequeño, búsquedas algo más rápidas, pero la ganancia mayor está en todo lo demás: menos fallos de caché, menos I/O.

Si una columna está muy sesgada (la mayoría de filas tienen un mismo valor y solo te importan los raros), ese es el caso de manual para un índice parcial.

Índice único parcial en SQLite (la función estrella)

Las restricciones UNIQUE normales se aplican a todas las filas. Eso se vuelve un problema en cuanto introduces borrado lógico (soft delete):

-- Falla: hay dos filas con email = 'a@x.com', aunque una está eliminada.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Un índice único parcial te permite imponer la unicidad únicamente sobre las filas que realmente importan:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- borrado lógico
    ('a@x.com', '2026-01-05'),  -- borrado lógico de nuevo
    ('a@x.com', NULL);          -- una fila activa, permitida

SELECT email, deleted_at FROM users;

Tres filas, mismo correo y ninguna violación de restricción: solo la fila con deleted_at IS NULL entra en la comprobación de unicidad. Si intentas insertar una segunda fila activa con el mismo correo, SQLite te lanza UNIQUE constraint failed.

Este patrón aparece por todos lados: una suscripción activa por cliente, una dirección principal por usuario, una factura abierta por pedido. Los índices únicos parciales lo expresan de forma directa.

Indexar ignorando los NULL

Los NULL se llevan raro con los índices. Un caso típico es querer "ignorar los NULL del todo": imagina una columna external_id poco poblada, donde la mayoría de filas son NULL pero las que sí tienen valor deben ser únicas:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Artilugio',  'EXT-001'),
    ('Cacharro',  NULL),
    ('Chisme', NULL),
    ('Engranaje', 'EXT-002');

SELECT name, external_id FROM products;

Dos NULL conviven sin problema, y las filas EXT-001 y EXT-002 siguen siendo únicas garantizadas. Además, el índice ocupa menos espacio (las filas con NULL ni siquiera se almacenan), así que las búsquedas por external_id siguen siendo rápidas aunque la tabla crezca.

Qué puede referenciar el filtro

La cláusula WHERE de un índice parcial es bastante restrictiva. Puede hacer referencia a:

• Columnas de la tabla que se está indexando.
• Constantes literales.
• Un conjunto reducido de funciones integradas deterministas.

No puede referenciar:

• Otras tablas.
• Subconsultas.
• Funciones no deterministas como random() o CURRENT_TIMESTAMP.
• Parámetros ni variables.

Y tiene su lógica: SQLite debe evaluar el filtro cada vez que se inserta o actualiza una fila, y el resultado tiene que ser estable. Por eso esto sí funciona:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

En cambio, WHERE created_at > date('now') no funcionaría: date('now') cambia con el tiempo, así que el conjunto de filas indexadas se movería bajo los pies de SQLite.

Flujo para validar tu índice parcial

Cada vez que añadas un índice parcial, conviene pasar por tres comprobaciones:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. ¿El planificador elige el índice?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. ¿Ignora las consultas fuera del filtro?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. ¿Omite las consultas que no incluyen el filtro?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

La consulta 1 debería usar idx_jobs_runnable. Las consultas 2 y 3 deberían caer en un escaneo (o usar otro índice, si lo tienes). Si el planificador elige el índice parcial en una consulta que no esperabas, vuelve a leer el filtro: probablemente abarque más de lo que crees.

Cuándo no conviene usar un índice parcial

Los índices parciales son una herramienta afilada. Razones para no usarlos:

• El filtro abarca la mayor parte de la tabla. Si el 90% de tus filas están "activas", un índice parcial no es más que un índice normal con pasos extra. Indexa la columna y ya está.
• Tus consultas no incluyen el filtro de forma literal. Si tu código usa un ORM que arma WHERE status IN (?, ?, ?), o calcula el filtro de forma dinámica, el planificador muchas veces no reconoce la coincidencia. Compruébalo con EXPLAIN QUERY PLAN, no lo des por hecho.
• El subconjunto activo cambia con el tiempo. Un índice parcial sobre "pedidos de los últimos 30 días" suena atractivo, pero no se puede expresar: el filtro tiene que ser determinista. Tendrías que reconstruir el índice, o replantear el esquema (una tabla aparte recent_orders, o un booleano archived que actualices cada noche).

Cuando el filtro es estable y selecciona una porción pequeña de una tabla grande, los índices parciales están entre los ajustes con mejor relación esfuerzo/beneficio que puedes aplicar en SQLite.

Lo que viene: leer planes de ejecución

Buena parte de esta página se apoyó en EXPLAIN QUERY PLAN para confirmar que un índice realmente se estaba usando. Esa herramienta merece su propia página: cómo interpretar su salida, qué significa cada palabra clave y cómo distinguir una búsqueda por índice limpia de un escaneo completo disfrazado. Eso lo vemos a continuación.