Mi servidor es un VPS DigitalOcean y tengo 60G hd, 4G ram, 4G swap file y me conecto a través de mysql workbench en una conexión a internet de 1Gig. La consulta a continuación toma más de 30 minutos antes de cancelarla. Muchas consultas toman 1-2 minutos, lo que creo que es demasiado largo para lo simple que es la consulta. Esta consulta puede no estar optimizada. ¿Hay alguna manera de optimizar la consulta? ¿Cómo puedo optimizar la velocidad de consulta?

(seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub ( curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate () ) día) Y StoreNumber = 1 AND departmentNumber = 20 orden por unidadesSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 21 ordenar por unidades Vender desc límite 30) unir todos (seleccionar * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 27 ordenar por unidades Vender desc límite 30) unir todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDA Y (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 Y número de departamento = 40 pedidos por unidades Límite de venta 30) unir todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 50 order by unitsSold desc límite 30) unir todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 60 order by unitsSold desc limit 30) union all (select * from movement donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement donde saleDate = date_sub (curdate (), intervalo) DÍA DE LA SEMANA (fecha de vencimiento ()) Y número de tienda = 1 Y número de departamento = 80 orden por unidadesS antiguo límite de desc. 30) unir todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 81 order by unitsSold límite de desc. 30) union all (select * desde el movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement donde saleDate = date_sub (datedate) , intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 90 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) día) AND StoreNumber = 1 AND departmentNumber = 95 ordenar por unidadesSold desc limit 30) union all (seleccionar * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 96 ordenar por unitSold desc limit 30) union all (seleccionar * del movimiento donde saleDate = date_sub (cu rdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 1 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate () ) día) Y StoreNumber = 3 AND departmentNumber = 10 orden por unidadesSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 11 ordenar por unidades Vender desc límite 30) unir todos (seleccionar * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 20 ordenar por unidades Vender desc límite 30) unir todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub ( curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmen tNumber = 27 orden por unidades Vendido desc límite 30) unión todos (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) día) Y StoreNumber = 3 AND departamentoNumber = 30 pedido por unidadesVendido desc límite 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 40 order by unitsSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 50 order by unitsSold desc limit 30) union all (select * from movement donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate) ()) día) Y StoreNumber = 3 AND departmentNumber = 60 orden por unidadesSold desc limit 30) union all (seleccione * del movimiento donde saleDate = date_sub (curdate (), intervalo WEEKDAY (curdate ()) day) AND StoreNumber = 3 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movemen)

Para resumir: hay casi 300 de estos UNIONd juntos:

        SELECT  *
            from  movement
            where  saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day)
              AND  StoreNumber =       -- some number
              AND  departmentNumber =  -- some other number
            order by  unitsSold desc
            limit  30
-1
Aaron Martin 5 mar. 2017 a las 09:14

2 respuestas

La mejor respuesta

Mi primer comentario es agregar un INDEX(saleDate, StoreNumber, departmentNumber) compuesto, en cualquier orden.

Pero, cavando más profundo, veo otros problemas.

No haga SELECT *, en su lugar haga solo SELECT id (suponiendo que id es el PRIMARY KEY), luego use el gran desorden de unión como una subconsulta para encontrar cualquier otra columna que necesite . Para que esto funcione, necesita este cubriendo índice en lugar de el que recomendé anteriormente: INDEX(saleDate, StoreNumber, departmentNumber, unitsSold, id).

Pregunta: Eso parece una subconsulta; hace toda la consulta es algo así como

SELECT *
        FROM ( that union mess ) AS u
    ORDER BY unitsSold DESC
    LIMIT 30

Si es así, ese se convierte en el momento perfecto para cambiar a

SELECT m.*
        FROM ( that union mess, but with only `id` ) AS u
        JOIN movement AS m  USING (id)
    ORDER BY m.unitsSold DESC
    LIMIT 30

Pero, aún mejor es hacer el LIMIT antes:

SELECT m.*
    FROM ( SELECT id
            FROM ( that union mess, but with only `id` ) AS u
            ORDER BY unitsSold DESC
            LIMIT 30 )
    JOIN movement AS m  USING (id)
    ORDER BY unitsSold DESC

Esta versión necesita obtener solo 30 filas completas, no 278 * 30, como en el caso anterior.

Una vez que asimile mis sugerencias, vuelva al uso de IN para ver si funciona lo suficientemente bien:

SELECT m.*
    FROM ( SELECT id
            FROM  movement
            WHERE  saleDate = CURDATE() - INTERVAL WEEKDAY(curdate()) DAY
              AND  StoreNumber      IN (...)
              AND  departmentNumber IN (...)
            order by  unitsSold desc
            limit  30 )
    JOIN movement AS m  USING (id)
    ORDER BY unitsSold DESC

Como es difícil predecir qué índice es mejor, recomiendo múltiples índices de cobertura para que el Optimizador elija entre:

INDEX(saleDate, StoreNumber, departmentNumber, unitsSold, id)
INDEX(saleDate, unitsSold, departmentNumber, StoreNumber, id)

saleDate es el primero porque es el único =. id es el último porque no está involucrado en WHERE o ORDER BY, sino que simplemente debe estar "cubierto". (Consulte "Uso del índice" en EXPLAIN.)

Si tiene una variante que involucra un rango de fechas en lugar de un solo saleDate, entonces todo de lo que dije necesita revisión para optimizarlo. Algunos de los principios sobrevivirán, pero los índices no .

2
marc_s 19 abr. 2017 a las 17:18

Parece que su consulta podría describirse como: 30 filas principales por unitsSold para cada StoreNumber & departmentNumber.

Lo escribiría de esta manera:

SELECT t.*
FROM (
    SELECT m.*, 
      IF(@s=StoreNumber AND @d=departmentNumber, @r:=@r+1, @r:=1) AS rowNumber,
      @s:=StoreNumber AS StoreNumber, 
      @d:=departmentNumber AS departmentNumber
    FROM (@g:=0, @r:=0) AS _init
    CROSS JOIN movement AS m
    WHERE saleDate = CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
    ORDER BY StoreNumber DESC, departmentNumber DESC, unitsSold DESC
) AS t
WHERE t.rowNumber <= 30;

Asegúrese de tener un índice en la tabla de columnas:

ALTER TABLE movement ADD KEY (saleDate, StoreNumber, departmentNumber, unitsSold);

Este es un patrón bastante común en MySQL, porque MySQL no admite funciones de ventanas SQL.

(tenga en cuenta que no he probado la consulta anterior)

Consulte también mi respuesta a Cómo SELECCIONAR el más nuevo cuatro elementos por categoría?

0
Community 23 may. 2017 a las 11:54