Optimización IS NULL

Un mito común es que MySQL no puede usar índices para consultas con IS NULL. En realidad, MySQL maneja NULL en los índices de forma eficiente. Las columnas NULL se incluyen en los índices B-Tree y las consultas con IS NULL e IS NOT NULL pueden aprovecharlos.

IS NULL usa índices

-- Crear índice en una columna que permite NULL
CREATE INDEX idx_supervisor ON empleados (supervisor_id);
 
-- IS NULL usa el índice
EXPLAIN SELECT * FROM empleados WHERE supervisor_id IS NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	empleados	ref	idx_supervisor	idx_supervisor	1	Using index condition

type: ref confirma que MySQL usa el índice para encontrar las filas con supervisor_id IS NULL. Solo Ricardo (Director General) no tiene supervisor.

SELECT nombre, puesto FROM empleados WHERE supervisor_id IS NULL;

nombre	puesto
Ricardo	Director General

IS NOT NULL usa índices

EXPLAIN SELECT * FROM empleados WHERE supervisor_id IS NOT NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	empleados	range	idx_supervisor	idx_supervisor	9	Using index condition

type: range indica que MySQL usa el índice para un rango de valores (todos los que no son NULL).

NULL en la tabla clientes

Algunos clientes tienen datos NULL en teléfono o dirección:

-- Crear índice en teléfono
CREATE INDEX idx_telefono ON clientes (telefono);
 
-- Buscar clientes sin teléfono
EXPLAIN SELECT nombre, apellidos FROM clientes WHERE telefono IS NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	clientes	ref	idx_telefono	idx_telefono	2	NULL

SELECT nombre, apellidos FROM clientes WHERE telefono IS NULL;

nombre	apellidos
Paula	Ortega Serrano
Marta	Blanco Castro

NULL en foreign keys

Las claves foráneas que permiten NULL ya tienen índice automático. Las consultas con IS NULL lo aprovechan:

-- Empleados sin supervisor (supervisor_id IS NULL)
-- Ya tiene índice por la FOREIGN KEY
 
-- Categorías sin padre (categorías raíz)
EXPLAIN SELECT * FROM categorias WHERE categoria_padre_id IS NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	categorias	ref	categoria_padre_id	categoria_padre_id	5	Using index condition

SELECT nombre FROM categorias WHERE categoria_padre_id IS NULL;

nombre
Electrónica
Ropa
Hogar
Deportes
Libros

Optimización ref_or_null

MySQL tiene una optimización especial llamada ref_or_null para consultas que combinan un valor específico con IS NULL:

EXPLAIN SELECT * FROM empleados
WHERE supervisor_id = 2 OR supervisor_id IS NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	empleados	ref_or_null	idx_supervisor	idx_supervisor	4	Using index condition

type: ref_or_null es una optimización específica que busca un valor concreto y también los NULL en una sola pasada del índice.

NULL en índices compuestos

-- Índice compuesto con columna nullable
CREATE INDEX idx_ciudad_telefono ON clientes (ciudad, telefono);
 
-- Buscar clientes de Madrid sin teléfono
EXPLAIN SELECT nombre, apellidos
FROM clientes
WHERE ciudad = 'Madrid' AND telefono IS NULL;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	clientes	ref	idx_ciudad_telefono	idx_ciudad_telefono	1	Using index condition

El índice compuesto funciona correctamente con valores NULL en las columnas.

COUNT y NULL

COUNT(columna) excluye los valores NULL automáticamente, y esta operación puede usar un índice:

SELECT
    COUNT(*) AS total_clientes,
    COUNT(telefono) AS con_telefono,
    COUNT(*) - COUNT(telefono) AS sin_telefono
FROM clientes;

total_clientes	con_telefono	sin_telefono
20	18	2

NOT NULL vs NULL: impacto en índices

Aspecto	Columna NOT NULL	Columna NULL
Uso del índice	Sí	Sí
IS NULL optimizable	No aplica	Sí
Espacio en índice	Ligeramente menor	1 byte extra por fila
Rendimiento	Marginalmente mejor	Prácticamente igual

La diferencia de rendimiento entre columnas NULL y NOT NULL es mínima en la práctica. La decisión de usar NULL debe basarse en el modelo de datos, no en el rendimiento.

Limpieza

DROP INDEX idx_supervisor ON empleados;
DROP INDEX idx_telefono ON clientes;
DROP INDEX idx_ciudad_telefono ON clientes;

Con esto completamos la sección de índices. Hemos cubierto cómo crear, examinar y eliminar índices, los diferentes tipos (prefijo, compuesto, único, invisible, clustered, funcional), cómo forzar el uso de índices, y cómo MySQL optimiza las consultas con NULL. En la siguiente sección exploraremos la búsqueda full-text en MySQL.

Escrito por Eduardo Lázaro