Algoritmos de procesamiento

MySQL puede procesar una vista con tres algoritmos diferentes: MERGE, TEMPTABLE y UNDEFINED. El algoritmo determina si MySQL integra la consulta de la vista con la consulta exterior o si primero materializa la vista en una tabla temporal. Esto tiene un impacto directo en el rendimiento y en si la vista es actualizable.

Los tres algoritmos

Sintaxis

CREATE ALGORITHM = MERGE VIEW nombre AS SELECT ...;
CREATE ALGORITHM = TEMPTABLE VIEW nombre AS SELECT ...;
CREATE ALGORITHM = UNDEFINED VIEW nombre AS SELECT ...;  -- Por defecto

MERGE

Con MERGE, MySQL combina la consulta de la vista con la consulta que la usa, creando una sola consulta optimizada:

CREATE ALGORITHM = MERGE VIEW v_productos_caros AS
SELECT id, nombre, precio, stock
FROM productos
WHERE precio > 500;
 
-- Cuando ejecutas:
SELECT * FROM v_productos_caros WHERE stock > 20;
 
-- MySQL lo transforma internamente en:
-- SELECT id, nombre, precio, stock FROM productos WHERE precio > 500 AND stock > 20;

MERGE es el algoritmo más eficiente porque permite al optimizador trabajar con la consulta completa y usar índices de las tablas subyacentes.

TEMPTABLE

Con TEMPTABLE, MySQL ejecuta la consulta de la vista primero, almacena los resultados en una tabla temporal, y luego ejecuta la consulta exterior sobre esa tabla temporal:

CREATE ALGORITHM = TEMPTABLE VIEW v_stats_productos AS
SELECT
    categoria_id,
    COUNT(*) AS total,
    AVG(precio) AS precio_medio,
    MAX(precio) AS precio_max
FROM productos
GROUP BY categoria_id;
 
SELECT * FROM v_stats_productos WHERE total > 3;

MySQL no puede usar MERGE aquí porque la vista tiene GROUP BY y funciones de agregación. La tabla temporal no tiene índices, así que filtrar sobre ella puede ser lento con muchos datos.

UNDEFINED

UNDEFINED es el algoritmo por defecto. MySQL elige automáticamente entre MERGE y TEMPTABLE:

-- MySQL elige MERGE para vistas simples
CREATE VIEW v_simple AS
SELECT nombre, precio FROM productos WHERE activo = TRUE;
 
-- MySQL elige TEMPTABLE para vistas con agregación
CREATE VIEW v_agregada AS
SELECT categoria_id, COUNT(*) AS total FROM productos GROUP BY categoria_id;

MySQL prefiere MERGE siempre que sea posible porque es más eficiente.

Cuándo MySQL usa TEMPTABLE

MySQL se ve forzado a usar TEMPTABLE cuando la vista contiene:

• Funciones de agregación: COUNT, SUM, AVG, MIN, MAX
• DISTINCT
• GROUP BY
• HAVING
• UNION o UNION ALL
• Subconsultas en la lista SELECT

-- Ver qué algoritmo usa una vista
SELECT TABLE_NAME, VIEW_DEFINITION, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'tienda_mysql';

Impacto en rendimiento

-- MERGE: MySQL puede usar el índice de categoria_id
EXPLAIN SELECT * FROM v_productos_caros WHERE stock > 20;

-- TEMPTABLE: MySQL crea tabla temporal sin índices
EXPLAIN SELECT * FROM v_stats_productos WHERE total > 3;

La vista TEMPTABLE aparece como derived en el EXPLAIN, indicando que se materializa como tabla temporal.

Recomendaciones

1. Usa ALGORITHM = UNDEFINED y deja que MySQL elija
2. Evita TEMPTABLE explícito a menos que necesites garantizar que la vista no sea actualizable
3. Para vistas de rendimiento crítico, prefiere diseños que permitan MERGE
4. Las vistas con JOIN simples generalmente usan MERGE
5. Si necesitas agregar datos, considera si una tabla resumen es mejor que una vista

Limpieza

DROP VIEW IF EXISTS v_productos_caros;
DROP VIEW IF EXISTS v_stats_productos;
DROP VIEW IF EXISTS v_simple;
DROP VIEW IF EXISTS v_agregada;

En el siguiente artículo veremos WITH CHECK OPTION, que previene modificaciones que harían desaparecer filas de la vista.