Contenido de esta guía
Respuesta rápida
Una FOREIGN KEY no implica siempre que debas crear otro índice manualmente. Lo primero es distinguir la clave referenciada de las columnas que guardan la referencia. La clave del padre ya necesita una garantía de unicidad; el índice sobre la clave hija depende del motor y del esquema existente.
CREATE INDEX idx_pedidos_cliente_fk
ON pedidos (cliente_id);En PostgreSQL y SQL Server declarar la clave foránea no crea automáticamente ese índice hijo. MySQL/InnoDB exige un índice adecuado y puede crearlo si falta. SQLite no lo exige para la columna hija, pero su propia documentación recomienda indexarla en la mayoría de sistemas reales para evitar búsquedas lineales durante comprobaciones referenciales.
Primero separa los dos lados de la relación
Supón que pedidos.cliente_id referencia clientes.id. Son dos necesidades distintas:
| Lado | Columna | Por qué importa |
|---|---|---|
| Tabla padre | clientes.id | Debe identificar de forma única el valor que puede ser referenciado. |
| Tabla hija | pedidos.cliente_id | Sirve para localizar rápidamente las filas hijas de un cliente y suele participar en JOIN y comprobaciones referenciales. |
Confundir ambos lados lleva a una regla demasiado simple como “toda FOREIGN KEY ya está indexada”. Eso no es portable entre motores. La pregunta útil es: ¿existe ya un índice aprovechable sobre las columnas que guardan la referencia?
Ejemplo comprobado: pedidos por cliente
La base educativa contiene pedidos.cliente_id como clave foránea hacia clientes.id. En la copia usada para esta prueba no existe inicialmente un índice explícito sobre pedidos(cliente_id).
EXPLAIN QUERY PLAN
SELECT id, fecha, total
FROM pedidos
WHERE cliente_id = 1
ORDER BY id;| detalle |
|---|
SCAN pedidos |
Ahora añadimos un índice que comienza por la columna de la clave foránea y repetimos exactamente la misma consulta.
CREATE INDEX idx_pedidos_cliente_fk
ON pedidos (cliente_id);
EXPLAIN QUERY PLAN
SELECT id, fecha, total
FROM pedidos
WHERE cliente_id = 1
ORDER BY id;| detalle |
|---|
SEARCH pedidos USING INDEX idx_pedidos_cliente_fk (cliente_id=?) |
La consulta devuelve los mismos tres pedidos de la clienta 1: 101, 103 y 111. El plan demuestra que SQLite puede usar el índice para localizar las filas por cliente_id. No demuestra que una tabla de 18 pedidos sea más rápida de forma significativa; para rendimiento real necesitas datos y mediciones representativas.
Por qué el índice hijo puede ayudar aunque la consulta sea correcta sin él
Una clave foránea protege integridad, pero el motor también necesita encontrar filas relacionadas. Ese acceso aparece en varios escenarios:
- JOIN frecuentes: por ejemplo, unir clientes con sus pedidos por
cliente_id. - Filtros por la relación: recuperar todos los pedidos de un cliente concreto.
- Cambios en la fila padre: al borrar o actualizar una clave referenciada, el motor puede necesitar localizar las filas hijas que todavía apuntan a ella.
Qué hace cada motor
| Motor | ¿Se crea automáticamente? | Qué comprobar |
|---|---|---|
| PostgreSQL | No | La clave referenciada tiene respaldo único; el índice de la tabla hija se decide aparte según consultas y mantenimiento. |
| MySQL 8.4 / InnoDB | Sí, si falta uno adecuado | InnoDB requiere índices en claves foráneas y referenciadas; evita crear después otro índice redundante con el mismo prefijo. |
| SQL Server | No | Microsoft indica que suele ser útil porque las columnas FK aparecen en JOIN y comprobaciones de cambios del padre. |
| SQLite | No es obligatorio en la hija | SQLite lo recomienda en la mayoría de sistemas reales para evitar recorridos lineales al buscar filas hijas. |
Por eso una migración portable no debería asumir que el mismo CREATE INDEX es necesario en todos los motores. Primero inspecciona los índices que ya existen y después crea solo el que falte.
Si la FOREIGN KEY tiene varias columnas
Con una clave foránea compuesta, las columnas funcionan como una unidad. Si una relación usa (pais_id, cliente_numero), un índice candidato natural en la tabla hija comienza por esas columnas en un orden compatible con la relación y con las consultas que quieres acelerar.
CREATE INDEX idx_pedidos_cliente_compuesto
ON pedidos_internacionales (pais_id, cliente_numero);No añadas automáticamente un índice de una sola columna si ya existe un índice compuesto que empieza por las columnas de la relación y el motor puede aprovecharlo para ese patrón. Un índice redundante también ocupa espacio y añade trabajo a INSERT, UPDATE y DELETE.
Una secuencia práctica para decidir
- Identifica la tabla hija. Localiza las columnas que contienen la FOREIGN KEY.
- Inspecciona índices existentes. No dupliques un índice que ya cubra el mismo prefijo útil.
- Mira las consultas reales. Comprueba JOIN, filtros y operaciones sobre la tabla padre.
- Observa el plan. Usa EXPLAIN o la herramienta equivalente.
- Mide también escrituras. Un índice acelera ciertos accesos, pero tiene coste de almacenamiento y mantenimiento.
Errores frecuentes
Dar por hecho que la FOREIGN KEY siempre crea un índice
Es falso como regla general. PostgreSQL y SQL Server no crean automáticamente el índice sobre las columnas hijas; MySQL/InnoDB sí tiene un requisito distinto.
Crear dos índices equivalentes
Antes de añadir INDEX(cliente_id), comprueba si ya existe uno como INDEX(cliente_id, fecha) que satisface tu necesidad. Mantener ambos puede ser innecesario.
Confundir integridad con rendimiento
Que una relación sea válida no significa que todas sus consultas sean rápidas. Y que un índice exista tampoco garantiza que el optimizador vaya a utilizarlo para cada consulta.
Comprueba lo aprendido
Indexa el detalle de cada pedido
detalle_pedido.pedido_id referencia pedidos.id. Crea un índice explícito sobre la columna hija y consulta el detalle del pedido 101.
Criterio de éxito: el índice empieza por pedido_id y la consulta sigue devolviendo tres filas: productos 1, 2 y 5, con cantidad 1 en cada caso.
detalle_pedido.pedidos(id) es pedido_id.Solución razonada
CREATE INDEX idx_detalle_pedido_pedido_fk
ON detalle_pedido (pedido_id);
SELECT producto_id, cantidad
FROM detalle_pedido
WHERE pedido_id = 101
ORDER BY producto_id;| producto_id | cantidad |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 5 | 1 |
El índice no cambia el resultado ni la relación. Añade una ruta de acceso sobre la columna hija. En la copia de SQLite usada para comprobarlo, el plan pasa de SCAN detalle_pedido a una búsqueda mediante idx_detalle_pedido_pedido_fk para el filtro por pedido_id.
Qué debes recordar
- La clave referenciada y la clave hija tienen necesidades de indexación distintas.
- PostgreSQL y SQL Server no crean automáticamente el índice de la FOREIGN KEY hija.
- MySQL/InnoDB exige un índice adecuado y puede crearlo automáticamente si no existe.
- SQLite no exige el índice hijo, pero lo recomienda para evitar búsquedas lineales en bases no triviales.
- Comprueba índices existentes antes de crear otro: un índice compuesto puede volver redundante uno más simple.
- Usa planes y mediciones para justificar rendimiento; la presencia de una FOREIGN KEY no sustituye esa evaluación.