mysql - optimizar - Consulta lenta al usar ORDENAR POR

mysql query lento

Question

Aquí está la consulta (la tabla más grande tiene aproximadamente 40,000 filas)

SELECT
  Course.CourseID,
  Course.Description,
  UserCourse.UserID,
  UserCourse.TimeAllowed,
  UserCourse.CreatedOn,
  UserCourse.PassedOn,
  UserCourse.IssuedOn,
  C.LessonCnt
FROM
  UserCourse
INNER JOIN
  Course
USING(CourseID)
INNER JOIN
(
  SELECT CourseID, COUNT(*) AS LessonCnt FROM CourseSection GROUP BY CourseID
) C
USING(CourseID)
WHERE 
  UserCourse.UserID = 8810

Si ejecuto esto, se ejecuta muy rápidamente (.05 segundos aproximadamente). Devuelve 13 filas.

Cuando agrego una cláusula ORDER BY al final de la consulta (ordenando por cualquier columna) la consulta demora aproximadamente 10 segundos.

Estoy usando esta base de datos en producción ahora, y todo funciona bien. Todas mis otras consultas son rápidas.

¿Alguna idea de lo que podría ser? Ejecuté la consulta en el buscador de consultas de MySQL y desde la línea de comandos. Ambos lugares fue muerto lento con el ORDER BY .

EDITAR: la solución ALBAYRAK de Tolgahan funciona, pero ¿alguien puede explicar por qué funciona?

Answer 1

¿Has actualizado las estadísticas en tu base de datos? Me encontré con algo similar en el mío, donde tuve 2 consultas idénticas en las que la única diferencia era una letra mayúscula y una regresó en 1/2 por segundo y la otra tardó casi 5 minutos. La actualización de las estadísticas resolvió el problema.

Answer 2

¿La columna que estás ordenando está indexada?

La indexación acelera drásticamente los pedidos y el filtrado.

Answer 3

Está seleccionando de " UserCourse ", que supongo que es una tabla de unión entre cursos y usuarios (muchos a muchos). Debe indexar la columna que necesita ordenar en la tabla "Curso de usuario".

Supongamos que desea " ordenar por CourseID ", entonces necesita indexarlo en la tabla UserCourse .

El ordenamiento por cualquier otra columna que no esté presente en la tabla de unión (es decir, UserCourse) puede requerir una desnormalización adicional y la indexación en la tabla de unión para optimizar la velocidad; En otras palabras, debe tener una copia de esa columna en la tabla de unión e indexarla.

PS La respuesta dada por Tolgahan Albayrak, aunque correcta para esta pregunta, no produciría el resultado deseado, en los casos en que uno está haciendo una consulta "LIMIT x".

Answer 4

Hoy me encontré con un mismo tipo de problema. Tan pronto como estaba ordenando el conjunto de resultados por un campo de una tabla unida, toda la consulta fue terriblemente lenta y tomó más de cien segundos.

El servidor ejecutaba MySQL 5.0.51a y, por casualidad, noté que la misma consulta se ejecutaba tan rápido como debería haber hecho siempre en un servidor con MySQL 5.1. Al comparar las explicaciones para esa consulta, vi que obviamente el uso y manejo de los índices ha cambiado mucho (al menos desde 5.0 -> 5.1).

Entonces, si encuentra un problema así, tal vez su resolución sea simplemente actualizar su MySQL

Answer 5

La respuesta es demasiado tarde, sin embargo, acabo de tener un problema similar, agregando orden al aumentar el tiempo de consulta de segundos a 5 minutos y, después de haber intentado la mayoría de las otras sugerencias para acelerarlo, noté que los archivos / tmp eran 12G para esta consulta Se modificó la consulta para que un campo varchar (20000) que se devolviera se "recortara (" ed y el rendimiento mejoró dramáticamente (de nuevo a segundos). Así que creo que vale la pena comprobar si está devolviendo varchars grandes como parte de su consulta y, en ese caso, procesarlos (tal vez una subcadena (x, 1, longitud (x))? si no desea recortarlos. La consulta devolvió 500k filas y el archivo / tmp indicó que cada fila estaba usando aproximadamente 20k de datos.

Answer 6

Una pregunta similar se hizo antes here.

Te podría ayudar también. Básicamente describe el uso de índices compuestos y cómo funciona el orden por obras.

Answer 7

tal vez esto ayude

SELECT * FROM (    
     SELECT
      Course.CourseID,
      Course.Description,
      UserCourse.UserID,
      UserCourse.TimeAllowed,
      UserCourse.CreatedOn,
      UserCourse.PassedOn,
      UserCourse.IssuedOn,
      C.LessonCnt
    FROM
      UserCourse
    INNER JOIN
      Course
    USING(CourseID)
    INNER JOIN
    (
      SELECT CourseID, COUNT(*) AS LessonCnt FROM CourseSection GROUP BY CourseID
    ) C
    USING(CourseID)
    WHERE 
      UserCourse.UserID = 8810
) ORDER BY CourseID