Mysql Blog en Español

Una de las principales ayudas en el examen de código que sea ineficiente funcionamiento, es utilizar el comando EXPLAIN.
mysql manual

La sentencia EXPLAIN puede utilizarse como una manera para obtener información acerca de cómo Mysql ejecuta una sentencia SELECT. Podemos determinar si los resultados de Mysql ha elegido una forma óptima de obtener los datos. Dos de las columnas son particularmente útil de examinar.
El primero es la Type columna que muestra el tipo de acceso Mysql utiliza para la consulta. En orden de menos un acceso eficiente a la más eficiente, estos son los valores que deben aparecer en la columna Type:
All - index - range - index_subquery - unique_subquery - index_merge - ref_or_null - ref - eq_ref - const - system

La siguiente columna es la columna Extra (este muestra información adicional relativa a esta estrategia de acceso).

Ahora, si usamos el comando Explain en una consulta (mirando a un conjunto de datos de registros de 2921, ligando con 50 registros de ciudades) tenemos esto:

Ejemplo de script
explain
select a.* from city a
where a.city_id in
(select b.city_id
from people b
group by b.city_id
having max(b.income) > 68500) \G

*************** 1. row ****
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using where
*************** 2. row ****
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: index
possible_keys: NULL
key: city_id
key_len: 3
ref: NULL
rows: 2921
Extra: Using filesort
2 rows in set (0.06 sec)

Ten en cuenta que la columna Extra de la subconsulta se señala que "using filesort" es necesario. Si se muestra Filesort significa que el ordenación no se pueden utilizar de un índice, es decir el ordenación se lleva a cabo en la memoria (y, a veces, continuó en el disco si hay problemas de memoria). Esto es ineficiente, y cada vez ocupan más recursos en la tabla aumenta. Siquiera con ordenar un pequeño conjunto de datos, solo de 2921 filas, puede tener un efecto adverso.

En este caso, ya hay un índice sobre el campo city_id en la tabla people. De hecho, no es el índice que está haciendo esta consulta lento - es el hecho de que el Mysql hace subconsultas muy ineficiente. (De acuerdo con el siguiente enlace en ingles, parece que la versión 6.0.x de Mysql es mucho más eficiente en el procesamiento de subconsultas a través del "IN" cláusula

bigdbahead blog

En el ínterin, la mejor manera de optimizar este tipo de consulta es por escrito en un formato de Join.

explain
select a.state, a.capital, a.largest, a.city_id
from city a
inner join people p on a.city_id = p.city_id
group by a.state, a.capital, a.largest, a.city_id
having max(p.income) > 68500 \G

Ahora, si ejecutamos el comando de Explain de nuevo:

**************** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using temporary; Using filesort
**************** 2. row ***
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: city_id
key: city_id
key_len: 3
ref: cookbook.a.city_id
rows: 40
Extra: Using where

¡Vaya! - la primera fila sigue utilizando filesort! Sin embargo, esto ya no se realiza en la subconsulta ineficiente; además, sólo se aplica a las 50 filas de nuevo en la agrupación del campo city_id. (En la declaración inicial de la ordenacion de filesort se realizó en 2921 en las filas de la subconsulta). Por cierto, esta consulta tuvo una media de tan sólo .2 de un segundo más rápido, pero en términos de base de datos un tamaño de registros de 2921 es relativamente pequeño, y cuanto mayor sea el conjunto de datos, mayor será la ganancia de velocidad mediante el uso de las Join de tabla.

Por supuesto, aunque re-escribir tus preguntas a utilizar Join ayudará inmensamente, no significa que la función de los índices que se ha olvidado. Por el contrario, la importancia de utilizar correctamente los índices es crucial. Espero que mire con más detalle en esta en mi siguiente artículo.