Optimizar consultas MySQL utilizando índices

Introducción

En este artículo vamos a tratar de examinar como optimizar consultas MySQL utilizando índices.

Para ello vamos a tener un log activado en nuestro sistema que nos indicará aquellas consultas que tardan más de 3 segundos. Es lo que consideramos como consultas lentas.

Está claro que dependiendo de la consulta/registros podemos considerar lenta a partir de x segundos, pero eso lo dejaremos en manos del DBA.

También aclarar que a veces hay que reformular la consulta para ganar un mejor rendimiento.

Optimizar consultas MySQL utilizando índices

Lo primero que haremos, si no está activo, es habilitar los logs para las consultas que tarden más de 3 segundos.

En el archivo de my.cnf añadiremos lo siguiente:

# Activamos las slow_query
slow_query_log = 1
# Donde guardaremos las slow_query
slow_query_log_file = /var/log/mysql/slow-queries.log
# El tiempo que consideramos que es una slow_query
long_query_time = 3
# OPCIONAL, si queremos habilitar las consultas que no tienen índices
log_queries_not_using_indexes = 1

Recordamos que después de los cambios se debe reiniciar MySQL.

Lo segundo que haremos será analizar el log (/var/log/mysql/slow-queries.log) después de un tiempo y nos dará las consultas completas. En nuestro caso usaremos una consulta de ejemplo:

SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited

Nos validamos en nuestra base de datos y vamos a usar «EXPLAIN» para que MySQL nos explique lo que hace dicha consulta:

explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
| id   | select_type | table       | type | possible_keys                          | key            | key_len | ref   | rows | Extra                       |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
|    1 | SIMPLE      | sContent | ref     | sPublish,sVisible_unlimited            | sPublish       | 2       | const | 7091 | Using where; Using filesort |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
1 row in set (0.03 sec)

Aquí hemos visto que la tabla «sYear» es donde se realiza la comparación del WHERE. También nos hemos dado cuenta de que dicho campo no aparece como si estuviera un índice (en possible_keys). Por lo tanto lo primero que haremos será agregar un índice a este campo:

ALTER TABLE `sContent` ADD INDEX (`sYear`);

Ahora volvemos a ejecutar el explain para ver el resultado de como ha quedado:

explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | sContent | ref | sPublish,sVisible_unlimited,sYear | sYear | 7 | const | 1503 | Using index condition; Using where; Using filesort |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Como se puede apreciar el rendimiento ha mejorado ya que hemos pasado de 7091 filas a 1503.

Conclusión

Como se puede ver no existe una formula mágica para todo pero podemos ir optimizando casos que nos puede ir ayudando a mejorar nuestro entorno web/aplicativo.

Las pruebas son las que nos dará los resultados para entregar un mejor rendimiento frente a los clientes.