Índices: CREATE INDEX

Un índice es una estructura que la base de datos crea para encontrar filas más rápido. Funciona como el índice de un libro: en vez de leer página por página, vas directamente al tema que buscas.

¿Por qué importan los índices?

Sin un índice, cuando ejecutas WHERE year = 2013, la base de datos tiene que revisar cada fila de la tabla (lo que se llama un full table scan). Con un índice en la columna year, puede saltar directamente a las filas que cumplen la condición.

En tablas pequeñas la diferencia es imperceptible. Pero en tablas con miles o millones de filas, un índice puede convertir una consulta de segundos en una de milisegundos.

Crear un índice

CREATE INDEX nombre_indice ON tabla(columna);

Por ejemplo, para crear un índice en la columna year:

CREATE INDEX idx_videogames_year ON videogames(year);

La convención más habitual para nombrar índices es idx_tabla_columna.

Índices en múltiples columnas

Puedes crear un índice sobre varias columnas. El orden importa: el índice es más eficaz cuando filtras por las columnas en el mismo orden en que se definieron:

CREATE INDEX idx_videogames_dev_year ON videogames(developer, year);

Este índice acelera consultas como:

• WHERE developer = 'Nintendo' (usa la primera columna)
• WHERE developer = 'Nintendo' AND year > 2000 (usa ambas columnas)

Pero no ayuda con WHERE year > 2000 solo (porque year no es la primera columna del índice).

Índices únicos

Un índice único además de acelerar las búsquedas, impide valores duplicados en esa columna:

CREATE UNIQUE INDEX idx_videogames_title ON videogames(title);

Con este índice, intentar insertar dos juegos con el mismo título dará error. Las claves primarias (PRIMARY KEY) ya crean un índice único automáticamente.

Eliminar un índice

DROP INDEX nombre_indice;

O de forma segura:

DROP INDEX IF EXISTS nombre_indice;

¿Cuándo crear índices?

Sí conviene crear índices en:

• Columnas que usas frecuentemente en WHERE.
• Columnas de JOIN (claves foráneas).
• Columnas que usas en ORDER BY con tablas grandes.
• Columnas con muchos valores distintos (alta cardinalidad).

No conviene crear índices en:

• Tablas muy pequeñas (no hay mejora apreciable).
• Columnas con pocos valores distintos (ej: una columna booleano).
• Tablas donde haces muchos INSERT/UPDATE/DELETE y pocas lecturas, porque cada escritura también tiene que actualizar los índices.

Tip: Los índices aceleran las lecturas pero ralentizan ligeramente las escrituras. Cada INSERT o UPDATE tiene que actualizar también los índices. Encuentra el equilibrio según tu caso de uso.

Ver los índices de una tabla

En SQLite puedes consultar los índices existentes con:

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

Nuestra tabla