Índices en SQLite: CREATE INDEX y cuándo usarlos

Qué es realmente un índice en SQLite

Un índice es una estructura de datos aparte —un árbol B ordenado— que permite a SQLite localizar filas a partir del valor de una columna sin tener que recorrer la tabla entera. Sin él, una consulta como WHERE email = 'rosa@example.com' lee fila por fila y va comprobando una a una. Con un índice sobre email, SQLite recorre el árbol en aproximadamente log(n) pasos y salta directamente a la coincidencia.

Pero esa rapidez no sale gratis. El índice es una copia de la columna indexada más un puntero a la fila original. Cada INSERT, cada UPDATE que toque una columna indexada y cada DELETE tiene que actualizar también el índice. Ocupa más disco y la escritura se ralentiza un poco. El trato es claro: pagas un peaje al escribir, pero ahorras muchísimo más al leer.

Crear un índice con CREATE INDEX

Esta es la sintaxis básica:

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

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Convención de nombres: la mayoría de los equipos usan idx_<tabla>_<columna> para que se vea de un vistazo a qué pertenece el índice. Ojo: el nombre tiene que ser único en toda la base de datos, no solo dentro de la tabla — por eso conviene incluir el nombre de la tabla.

Para eliminarlo:

DROP INDEX idx_users_email;

Los índices son pura infraestructura de rendimiento. Borrar uno nunca afecta a tus datos: solo cambia la velocidad a la que se ejecutan las consultas.

Índices únicos (UNIQUE INDEX)

Un índice único cumple una doble función: acelera las búsquedas y además garantiza que no haya dos filas con el mismo valor en la columna indexada.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

El tercer INSERT falla con UNIQUE constraint failed: accounts.username. SQLite ya crea índices únicos de forma automática para las columnas PRIMARY KEY y UNIQUE; los verás con el nombre sqlite_autoindex_<tabla>_<n>. Solo necesitas escribir CREATE UNIQUE INDEX cuando la restricción no se declaró directamente en la tabla.

Qué hace realmente el planificador de consultas

Crear un índice no garantiza que SQLite vaya a usarlo. El planificador elige una estrategia para cada consulta, y puedes ver cuál escogió con EXPLAIN QUERY PLAN:

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

Busca SEARCH ... USING INDEX idx_orders_customer en la salida: eso indica que el índice se está usando. Si en cambio aparece SCAN orders, es que el planificador consideró más barato hacer un recorrido completo de la tabla (algo razonable en tablas muy pequeñas) o que la forma de tu consulta le impidió aprovechar el índice. Más adelante hay un documento entero dedicado a interpretar estos planes.

Cuándo SQLite no usa el índice

Los índices tienen varios puntos ciegos bien conocidos. Cualquiera de estos casos anula el índice sobre email:

-- La función envuelve la columna
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Comodín al inicio en LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- La discrepancia de tipos fuerza una conversión
SELECT * FROM users WHERE email = 12345;

El árbol B se ordena por el valor original de email, así que cualquier transformación sobre la columna en tiempo de consulta obliga a un escaneo completo. Hay varias soluciones: guardar el dato ya normalizado (una columna email_lower), usar un índice por expresión (CREATE INDEX idx ON users(lower(email))), o recurrir a la búsqueda de texto completo (FTS) de SQLite para coincidencias parciales.

Covering index en SQLite

Cuando un índice contiene todas las columnas que necesita la consulta, SQLite puede responder sin tocar la tabla en ningún momento: eso es un covering index (o índice de cobertura). El truco está en incluir columnas extra dentro de la definición del índice:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Como ambas columnas que pide la consulta viven dentro del índice, SQLite muestra USING COVERING INDEX. No hace falta ir a buscar la fila. Los covering index en SQLite son una de las optimizaciones con mayor retorno para rutas de lectura calientes; el precio a pagar es un índice más grande. Los índices multicolumna son tema aparte: el siguiente documento los cubre como toca.

Listar e inspeccionar índices de una tabla en SQLite

Tienes dos formas de ver lo que hay:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

Eso te devuelve todos los índices de la base de datos junto con su sentencia CREATE. Si solo te interesa una tabla, PRAGMA index_list('products'); muestra los índices de esa tabla, y PRAGMA index_info('idx_products_name'); te dice qué columnas indexa cada uno. Cualquier cosa que empiece por sqlite_autoindex_ la creó SQLite automáticamente para una restricción PRIMARY KEY o UNIQUE, y no la puedes borrar.

Cuándo no conviene crear un índice

Hay situaciones en las que añadir un índice empeora las cosas:

• Tablas pequeñas. Unos cientos de filas se recorren en microsegundos. Lo más probable es que el planificador ignore el índice de todos modos, y solo habrás añadido sobrecarga en las escrituras para nada.
• Columnas con muchas escrituras y pocas consultas. Cada escritura actualiza todos los índices. Indexar una columna por la que casi nunca filtras es puro coste.
• Columnas con baja cardinalidad por sí solas. Un índice sobre una columna status con tres valores posibles no acota gran cosa. Puede tener sentido como segunda columna de un índice compuesto, o como índice parcial, pero por sí solo casi nunca compensa.
• Ya está cubierto. Si tienes un índice sobre (a, b), no necesitas otro sobre (a). SQLite aprovecha las columnas iniciales de un índice compuesto cuando la consulta filtra solo por a.

La respuesta honesta a "¿debería añadir este índice?" casi siempre es: pruébalo, lanza EXPLAIN QUERY PLAN, mide con datos realistas y decide.

Siguiente paso: índices compuestos

Un índice de una sola columna cubre muchos casos, pero las consultas reales suelen filtrar y ordenar por varias columnas a la vez. Los índices compuestos —índices sobre (a, b, c)— resuelven eso, y el orden de las columnas importa mucho más de lo que la gente cree. Es lo que veremos en la siguiente página.