Forzando índices en MySQL – MariaDB

Dentro de un proyecto de investigación en la Universidad de Córdoba, trabajamos con una base de datos en MySQL que tiene unos cuantos registros, en torno a 9 Gb, de los que el 95% se concentran en una sola tabla “datos“.

En esta tabla se guardan datos con una frecuencia de 5 minutos, pero necesitamos calcular los datos agregados horarios. Para año y medio de datos, tenemos unos cuantos millones de datos, concreamente 57 millones.

Nos metemos en faena, pero comprobamos los índices disponibles:

La consulta que tenemos prevista es la siguiente:

SELECT  DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00') AS fecha, dc.dia, dc.anno, dc.idparametro, dc.idinstalacioninversor,
AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor)  AS maximo, MIN(dc.valor) AS minimo, AVG(dc.valor) as valor
FROM solar_i10.datos dc
INNER JOIN solar_comun.parametros p using (idparametro)

WHERE p.agregado < 5 AND dc.anulado = 0 AND  dc.idinstalacioninversor = 417 AND dc.anno = 2011
GROUP BY dc.idinstalacioninversor, dc.idparametro, dc.dia, dc.hora
	 ORDER BY  DATE_FORMAT( fecha,'%Y-%m-%d %H:00:00');

Vamos a analizar la consulta con EXPLAIN:

A priori, los criterios que aplica MySQL son bastante buenos. En este caso, ha seleccionado el índice fecha_idparametro_idinstalacioninversor dato que la fecha es usado en la cláusula ORDER BY y los campos idparametro e idinstalacioninversor son usados en la cláusula WHERE. En total, estamos trabajando con 3.914.722 registros!

Sin embargo, la ejecución de la misma se hace eterna, alcanzando los 70 minutos (en la siguiente imagen vemos que va por los 1269 segundos, esto es unos 20 minutos …. y decidimos parar la ejecución!).

Si observamos la consulta, la cláusula WHERE incluye 3 campos (anulado, idinstalacioninversor y anno) que constituyen un índice de la tabla, así que … ¿por qué no usarlo en lugar del que me propone MySQL?

Modificamos la consulta SQL, pero ahora hay que indicarle que queremos utilizar un índice concreto, usando FORCE INDEX:

SELECT DATE_FORMAT( fecha,’%Y-%m-%d %H:00:00′) AS fecha, dc.dia, dc.anno, dc.idparametro, dc.idinstalacioninversor,
AVG(dc.valor) AS prom, SUM(dc.valor) AS suma, MAX(dc.valor) AS maximo, MIN(dc.valor) AS minimo, AVG(dc.valor) as valor
FROM solar_comun.parametros p
INNER JOIN solar_i10.datos dc FORCE INDEX (anno_idinstalacioninversor_anulado) using (idparametro)
WHERE p.agregado < 5 AND dc.anulado = 0 AND dc.idinstalacioninversor = 417 AND dc.anno = 2011
GROUP BY dc.idinstalacioninversor, dc.idparametro, dc.dia, dc.hora
ORDER BY DATE_FORMAT( fecha,’%Y-%m-%d %H:00:00′);

La nueva consulta tiene algunas modificaciones respecto a la original, marcadas en rojo por cambios en los INNER JOIN (esto no afecta), pero ahora sí incluimos la cláusula FORCE INDEX y el índice que queremos utilizar (en este caso, anno_idinstalacioninversor_anulado).

Al igual que antes, verificamos qué nos cuenta MySQL cuando ejecutamos un EXPLAIN de la nueva consulta:

La información de MySQL para la nueva consulta selecciona el índice que le hemos obligado a tomar, pero nos toma más registros a analizar (4.313.310 registros). Hay una diferencia de más de 360.000 registros de diferencia entre uno y otro, habiendo más en esta nueva versión de la consulta.

Sin embargo, el tiempo de ejecución de la consulta baja a solo 10-12 minutos!!

Esto supone un ahorro bastante considerable en tiempo, por lo que en este caso concreto, MySQL no ha seleccionado el mejor índice!

Comprobamos los tiempos ejecutando ambas consultas desde MySQL WorkBench:

Comprobamos que la consulta modificada aquí solo tarda 224 segundos (porque solo devuelve 1000 registros), mientras que la consulta original la cortamos cuando vamos por 20 minutos (empezó a las 11:38 y abajo a la derecha marca ya las 11:57)!!

Por último, comentar que es muy importante la explicación (EXPLAIN) que nos da MySQL de las consultas, y … si se alargan en el tiempo es porque hay algo que se puede mejorar, y he aquí un caso concreto de mejora del rendimiento de una consulta forzando a MySQL a usar un índice que es seleccionado por el usuario.

Espero os haya sido de utilidad, y espero vuestras consultas y comentarios.