mysql filesort

Usando index, using temporary, using filesort-¿cómo arreglar esto?

Question

Estoy trabajando en un sistema de seguimiento de eventos que utiliza un puñado de tablas de búsqueda, así como la tabla de registro principal. En un informe que estoy escribiendo, se puede seleccionar un objeto para ver las estadísticas en contra. La interfaz muestra todos los objetos en orden de importancia decreciente (es decir, hits).

El esquema para las dos tablas (ligeramente recortado, pero obtienes la esencia):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

La consulta con la que estoy teniendo problemas está debajo. Funciona bien con mi tabla de ~ 100 entradas, pero el EXPLAIN me preocupa un poco.

    explain SELECT 
            el.object_id, 
            leo.object_desc, 
            COUNT(el.object_id) as count_rows
        FROM 
            event_log el 
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY 
            el.object_id
        ORDER BY 
            count_rows DESC,
            leo.object_desc ASC

Devoluciones: Using index; Using temporary; Using filesort Using index; Using temporary; Using filesort

Entonces, ¿qué hay de malo en mi esquema y / o consulta para que MySQL filesort temporary y filesort ? ¿O está tan optimizado como puede obtenerse mediante ORDER BY?

Answer 1

Actualizado para MySQL 5.7 ( src ):

El servidor crea tablas temporales bajo condiciones como estas:

• Evaluación de las declaraciones de la UNIÓN, con algunas excepciones que se describen más adelante.

• Evaluación de algunas vistas, como las que usan el algoritmo TEMPTABLE, UNION o agregación.

• Evaluación de tablas derivadas (subconsultas en la cláusula FROM).

• Tablas creadas para la materialización de subconsultas o de semifusiones (consulte la Sección 8.2.2, "Optimización de subconsultas, tablas derivadas y referencias de vista").

• Evaluación de sentencias que contienen una cláusula ORDER BY y una cláusula GROUP BY diferente, o para las cuales ORDER BY o GROUP BY contienen columnas de tablas distintas a la primera tabla en la cola de unión.

• La evaluación de DISTINCT combinada con ORDER BY puede requerir una tabla temporal.

• Para consultas que usan el modificador SQL_SMALL_RESULT, MySQL usa una tabla temporal en memoria, a menos que la consulta también contenga elementos (descritos más adelante) que requieren almacenamiento en disco.

• Para evaluar las sentencias INSERT ... SELECT que se seleccionan e insertan en la misma tabla, MySQL crea una tabla temporal interna para contener las filas de SELECT, luego inserta esas filas en la tabla de destino. Consulte la Sección 13.2.5.1, "INSERTAR ... SELECCIONAR Sintaxis".

• Evaluación de las instrucciones UPDATE de múltiples tablas.

• Evaluación de expresiones GROUP_CONCAT () o COUNT (DISTINCT).

Answer 2

Bueno, el documento da los motivos exactos cuando aparecerá "Uso temporal":

Las tablas temporales se pueden crear en condiciones como las siguientes:

Si hay una cláusula ORDER BY y una cláusula GROUP BY diferente, o si el ORDER BY o GROUP BY contiene columnas de tablas distintas a la primera tabla en la cola de unión, se crea una tabla temporal.

DISTINCT combinado con ORDER BY puede requerir una tabla temporal.

Si usa la opción SQL_SMALL_RESULT, MySQL usa una tabla temporal en memoria, a menos que la consulta también contenga elementos (descritos más adelante) que requieren almacenamiento en disco.

Un escaneo rápido muestra que usted sufre de # 1.

Y este blog de 2009 dice que "usar filesort" significa que el ordenamiento no se puede realizar con un índice. Ya que ordena por un campo calculado, eso también será cierto.

Entonces, eso es lo que está "mal".

Answer 3

Estas son las siguientes condiciones bajo las cuales se crean las tablas temporales. Las consultas de UNION usan tablas temporales.

Algunas vistas requieren tablas temporales, como las que se evalúan utilizando el algoritmo TEMPTABLE, o que usan UNION o agregación.

Si hay una cláusula ORDER BY y una cláusula GROUP BY diferente, o si el ORDER BY o GROUP BY contiene columnas de tablas distintas a la primera tabla en la cola de unión, se crea una tabla temporal.

DISTINCT combinado con ORDER BY puede requerir una tabla temporal.

Si usa la opción SQL_SMALL_RESULT, MySQL usa una tabla temporal en memoria, a menos que la consulta también contenga elementos (descritos más adelante) que requieren almacenamiento en disco.

Siga este enlace por mysql: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html