Cálculos con MySQL muy lento

[planmental]

Buenas tardes foreros.

Vengo hoy con un problema que me está volviendo loco.

Pues estuve casi todo el día tratando de resolver un problema que se resolvió poniéndole un simple GROUP BY dentro de una subconsulta para que me diera el resultado esperado.

Pero cuando pensé que todo estaba bien me topo con un problema que no he podido resolver pues el consulta se volvió súper extremadamente lenta, tanto así que ya van diez minutos de una consulta y aún no me muestra el resultado.

Bueno sin tanto rollo aquí va la parte de código:

Código MySQL:

SELECT * FROM traslado
    
    INNER JOIN servicio 
    ON traslado.servicio_id = servicio.servicio_id
 
    INNER JOIN cliente
    ON servicio.cliente_id = cliente.cliente_id
    INNER JOIN localidad t1
    ON traslado.localidad_destino = t1.localidad_id
    INNER JOIN estado_traslado
    ON traslado.ultimo_estado_id = estado_traslado.estado_traslado_id
    
    WHERE cliente.alias = '".$f['alias']."' 
    AND t1.alias != 'BASE'
    AND remolque_otros not like '%".'/'."%'
    AND remolque_otros not like '%".'q'."%'
    AND remolque_otros not like '%".'PT'."%'
    AND estado_traslado.estado_traslado_id != 'false'
    AND estado_traslado.estado_traslado_id != 'cancel'
    AND traslado_id in (SELECT MAX(traslado_id) FROM traslado GROUP BY traslado.remolque_otros )  <------ esta subconsulta me volvio lento todo 
    GROUP BY traslado.remolque_otros
    ORDER BY traslado.traslado_id DESC;
 

Lo que quiero es que me digan cómo poder simplificar este código para que no se vuelva tan lento.

Y pues muchas gracias por su atencion

[planmental]

buenos días foreros.

Tengo un problema con la manera de hacer cálculos en MySQL con una tabla de más de medio millón de registros.

Necesito que me den unos consejos de cómo optimizar esas consultas pues se tarda horrores en darme el resultado.

PD: llevo dos días sin poder resolverlo

Por su atención gracias

[gnzsoloyo]

No abras dos hilos para la misma pregunta. Las PDU de FDW lo prohíben expresamente.

Por lo demás, empecemos por el lado de que esa consulta es un espanto de ineficiencia. Como mínimo, semejante abuso de LIKEs, que es una de las peores cláusulas en cuanto performance, conspira contra cualquier intento de mejora.
Además los GROUP BY y ORDER BY son, por definición, asesinos de performance. Sólo se deberían usar cuando tienes un conjunto de resultados muy, pero muy acotado. Hacerlo sonre una tabla grande, con una consulta que hace tantos tablescan, es una muy mala idea.

[Libras]

y ya probaste con esto:

Código SQL:

DECLARE @variable INT
 
SELECT @variable=MAX(traslado_id) FROM traslado GROUP BY traslado.remolque_otros
 
SELECT * FROM traslado
    
    INNER JOIN servicio 
    ON traslado.servicio_id = servicio.servicio_id
 
    INNER JOIN cliente
    ON servicio.cliente_id = cliente.cliente_id
    INNER JOIN localidad t1
    ON traslado.localidad_destino = t1.localidad_id
    INNER JOIN estado_traslado
    ON traslado.ultimo_estado_id = estado_traslado.estado_traslado_id
    
    WHERE cliente.alias = '".$f['alias']."' 
    AND t1.alias != 'BASE'
    AND remolque_otros NOT LIKE '%".'/'."%'
    AND remolque_otros NOT LIKE '%".'q'."%'
    AND remolque_otros NOT LIKE '%".'PT'."%'
    AND estado_traslado.estado_traslado_id != 'false'
    AND estado_traslado.estado_traslado_id != 'cancel'
    AND traslado_id IN (@variable)  <------ esta subconsulta me volvio lento todo 
    GROUP BY traslado.remolque_otros
    ORDER BY traslado.traslado_id DESC
 

Ojo el codigo esta en sql server...seria que lo acomodaras para mysql, otra cosa, que indices tienen tus tablas???

[planmental]

Buenos días y gracias por sus respuestas.

gnzsoloyo tienes razón es un desastre por culpa de esos like pero es que pensé que haría más corta la consulta excluyéndome ciertos resultados pero veo que no. Pensé que el group by optimizaba la consulta

Libras pues trate de usar tu código pero no supe acoplarlo y pues los índices que tiene la tabla son

traslado_ id = como llave primaria
idx_fecha_servicio
servicio_fk =
idx_remolque_otros
remolque_fk

Y otros muchos más.

[gnzsoloyo]

El problema de los LIKE, en principio, consiste en que estás usando los comodines al principio y al final de la cadena al mismo tiempo. Eso le indica a la base que devuelva todo lo que comience con cualquier cosa, y termine con cualquier cosa, en tanto contenga la cadena bsucada...
Ahora bien, la base no puede distingir en ese caso nada, porque se pide todo lo qu eexista, y que contenga esa cadena. Eso simplemente significa que debe leer uno po uno todos los registros y comaprar el contenido.
¿Te das cuenta el problema: Tiene que leer TODO...
Ese tipo de uso del LIKE no es optimizable de ninguna forma. Incluso si crearas índices, MySQL los descartaría, porque cualquier indice que pueda tener que leerse en un 50% le llevaría el mismo tiempo o mas que leer la tabla. Por eso simplemente lo descarta.
A nivel práctico, lo mejor que puedes hacer es comparar obligar al usuario a ingresar palabras completas, y usar indices FULLTEXT.

[planmental]

Hola gnzsoloyo.

Pues sí sería una solución pero me gustaría usar este tipo de consulta obviamente quitando los like y sobre todo me gustaría una alternativa para el "in" en la subconsulta porque es la que me vuelve lento toda la consulta.

[Libras]

prueba con lo que te dije usando variables en mysql o en su defecto usar exists......

[planmental]

Hola Libras.

Ya utilicé el código que me diste y me sale este error:

Parse error: syntax error, unexpected 'i' (T_STRING), expecting '(' in C:\xampp\htdocs\inv\clientes.php on line 77

sustitui la "i" en la primera línea

DECLARE i INT

[gnzsoloyo]

Vamos ahacer un intento:

Código MySQL:

SELECT * 
    (SELECT T.servicio_id, remolque_otros,  S.cliente_id, L.localidad_id, ET.estado_traslado_id
    FROM  traslado T 
          INNER JOIN servicio S ON T.servicio_id = S.servicio_id
          INNER JOIN cliente C ON S.cliente_id = C.cliente_id
          INNER JOIN localidad L1 ON T.localidad_destino = L.localidad_id
          INNER JOIN estado_traslado ET ON T.ultimo_estado_id = ET.estado_traslado_id
    WHERE C.alias = '".$f['alias']."' 
      AND L.alias != 'BASE'
      AND remolque_otros not like '%".'/'."%'
      AND remolque_otros not like '%".'q'."%'
      AND remolque_otros not like '%".'PT'."%'
      AND ET.estado_traslado_id != 'false'
      AND ET.estado_traslado_id != 'cancel'
    ORDER BY T.traslado_id DESC) T1
GROUP BY remolque_otros;
 

[gnzsoloyo]

Iniciado por planmental

Hola Libras.

Ya utilicé el código que me diste y me sale este error:

Parse error: syntax error, unexpected 'i' (T_STRING), expecting '(' in C:\xampp\htdocs\inv\clientes.php on line 77

sustitui la "i" en la primera línea

DECLARE i INT

Eso es un error de PHP, y no se tratan temas de programacion en los foros de Bases de DAtos.

En todo caso, ten en cuenta que esa sintaxis de SQL Server no funcionará en MySQL por varias razones.

[planmental]

Hola gnzsoloyo muchas gracias por tu apoyo.

Utilice el código que me diste y me sale el siguiente error:

Error en: : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT T.servicio_id, remolque_otros, S.cliente_id, L.localidad_id, ET.estado_' at line 1

Ojala y no sea por la versión de mi MySQL

[Libras]

no creo que sea por la version mas bien es porque te falto el from.....

Código MySQL:

SELECT * from
    (SELECT T.servicio_id, remolque_otros,  S.cliente_id, L.localidad_id, ET.estado_traslado_id
    FROM  traslado T 
          INNER JOIN servicio S ON T.servicio_id = S.servicio_id
          INNER JOIN cliente C ON S.cliente_id = C.cliente_id
          INNER JOIN localidad L1 ON T.localidad_destino = L.localidad_id
          INNER JOIN estado_traslado ET ON T.ultimo_estado_id = ET.estado_traslado_id
    WHERE C.alias = '".$f['alias']."' 
      AND L.alias != 'BASE'
      AND remolque_otros not like '%".'/'."%'
      AND remolque_otros not like '%".'q'."%'
      AND remolque_otros not like '%".'PT'."%'
      AND ET.estado_traslado_id != 'false'
      AND ET.estado_traslado_id != 'cancel'
    ORDER BY T.traslado_id DESC) T1
GROUP BY remolque_otros;
 

[gnzsoloyo]

Exacto... Me olvidé del FROM...

[planmental]

Enhorabuena.

Me funcionó el código con el FROM pero no me da el resultado que busco pues necesito solo los remolques_otros donde su max traslado_id diga el nombre del cliente a buscar.

Por eso escribía la subconsulta:

(SELECT MAX(traslado_id) FROM traslado GROUP BY traslado.remolque_otros )

Pero tiene razón gnzsoloyo pues cuando le escribo GROUP BY la consulta se vuelve lenta.

Y en el código de Libras escribo la subconsulta y se pone lenta, cual sería la solución para sustituir esa subconsulta.

PD: ya intenté con exists y me sale el siguiente error:

Error en: : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT MAX(T.traslado_id) FROM traslado GROUP BY T.remolque_otros ) ' at line 15

[Libras]

como usaste el exists???

[planmental]

Lo utilicé de esta forma

Código:

    $sql = "SELECT * from
    (SELECT T.servicio_id, remolque_otros,  S.cliente_id, L.localidad_id, ET.estado_traslado_id, MAX(T.traslado_id) traslado_id
    FROM  traslado T 
          INNER JOIN servicio S ON T.servicio_id = S.servicio_id
          INNER JOIN cliente C ON S.cliente_id = C.cliente_id
          INNER JOIN localidad L ON T.localidad_destino = L.localidad_id
          INNER JOIN estado_traslado ET ON T.ultimo_estado_id = ET.estado_traslado_id
    WHERE C.alias = '".$f['alias']."' 
      AND L.alias != 'FLETES SOTELO'
      AND remolque_otros not like '%".'/'."%'
      AND remolque_otros not like '%".'q'."%'
      AND remolque_otros not like '%".'PT'."%'
      AND ET.estado_traslado_id != 'false'
      AND ET.estado_traslado_id != 'cancel'
      AND T.traslado_id EXISTS (SELECT MAX(T.traslado_id) FROM traslado GROUP BY T.remolque_otros ) <------------------------ aquí está
    ORDER BY T.traslado_id DESC) T1
GROUP BY remolque_otros;

[Libras]

Asi no se usa el exists, ni siquiera revisas los manuales en linea antes de hacer un cambio???

Código MySQL:

SELECT * from
    (SELECT T.servicio_id, remolque_otros,  S.cliente_id, L.localidad_id, ET.estado_traslado_id, MAX(T.traslado_id) traslado_id
    FROM  traslado T 
          INNER JOIN servicio S ON T.servicio_id = S.servicio_id
          INNER JOIN cliente C ON S.cliente_id = C.cliente_id
          INNER JOIN localidad L ON T.localidad_destino = L.localidad_id
          INNER JOIN estado_traslado ET ON T.ultimo_estado_id = ET.estado_traslado_id
    WHERE C.alias = '".$f['alias']."' 
      AND L.alias != 'FLETES SOTELO'
      AND remolque_otros not like '%".'/'."%'
      AND remolque_otros not like '%".'q'."%'
      AND remolque_otros not like '%".'PT'."%'
      AND ET.estado_traslado_id != 'false'
      AND ET.estado_traslado_id != 'cancel'
      AND EXISTS (SELECT MAX(T.traslado_id) FROM traslado GROUP BY T.remolque_otros ) <------------------------ aquí está
    ORDER BY T.traslado_id DESC) T1
GROUP BY remolque_otros;
 

[planmental]

Ups sorry

Y ya probé el código con EXISTS y ya no me muetra error pero se sigue tardando.

[Libras]

pregunta, cuanto es tardado para ti? cuantos registros regresa tu consulta, cuantas columnas???

[planmental]

Hola Libras.

Mira estoy haciendo la prueba con dos clientes uno que tiene muy pocos remolques y otro que tiene muchos. Y con el codigo lo que saco es el número de remolques que en su último id tengan el nombre del cliente.

El cliente que tiene pocos me regresa un resultado de 1 y se tarda como 20 segundos.

El cliente que tiene muchos remolques ya lleva 3 minutos y aún no me da el resultado

He estado pensando que los cálculos son los que están perjudicando la consulta pues es la única parte que se pone lenta de todo lo que estoy programando.

Porque he estado consultando los más de medio millón de registros (que cada día aumentan ) de la tabla traslado y no va muy lento a excepción de esta parte.

Eso supongo, la verdad es la primera vez en la que programo algo que lleve cálculos en una base de datos tan grande.

PD: no soy el único que usa el servidor y me acaban de decir que yo lo puse lento, es eso posible?

[Libras]

si es posible alentar un servidor con una consulta mal echa, medio millon de registros es una nada, pero creo que deberias de filtrar mas la informacion, o vas a presentar el reporte con el millon y medio??? creo que deberias de agregar un filtro fecha o algo mas.....

[planmental]

Hola Libras.

Pues la verdad puede ser una solución pero me gustaría saber si hay una forma de decirle que me encuentre el max id del campo remolque_otros y meterlo en una variable.

[Libras]

Código MySQL:

SELECT @variable:=MAX(traslado_id) FROM traslado GROUP BY traslado.remolque_otros
 
SELECT * FROM traslado
    
    INNER JOIN servicio 
    ON traslado.servicio_id = servicio.servicio_id
 
    INNER JOIN cliente
    ON servicio.cliente_id = cliente.cliente_id
    INNER JOIN localidad t1
    ON traslado.localidad_destino = t1.localidad_id
    INNER JOIN estado_traslado
    ON traslado.ultimo_estado_id = estado_traslado.estado_traslado_id
    
    WHERE cliente.alias = '".$f['alias']."' 
    AND t1.alias != 'BASE'
    AND remolque_otros NOT LIKE '%".'/'."%'
    AND remolque_otros NOT LIKE '%".'q'."%'
    AND remolque_otros NOT LIKE '%".'PT'."%'
    AND estado_traslado.estado_traslado_id != 'false'
    AND estado_traslado.estado_traslado_id != 'cancel'
    AND traslado_id =@variable  <------ esta subconsulta me volvio lento todo 
    GROUP BY traslado.remolque_otros
    ORDER BY traslado.traslado_id DESC
 

[gnzsoloyo]

Iniciado por planmental

Hola Libras.

Pues la verdad puede ser una solución pero me gustaría saber si hay una forma de decirle que me encuentre el max id del campo remolque_otros y meterlo en una variable.

En MySQL no se puede, porque no existen los tipos de datos, record, array, table o collections en general.
La solución propuesta por Libras aplica únicamente a SQL Server y dispararía un error en MYSQL.

Por favor, tengan eso en cuenta.

[Libras]

Iniciado por gnzsoloyo

En MySQL no se puede, porque no existen los tipos de datos, record, array, table o collections en general.
La solución propuesta por Libras aplica únicamente a SQL Server y dispararía un error en MYSQL.

Por favor, tengan eso en cuenta.

Le propuse que usara una variable porque veo que su query nada mas retornaria un valor(el max id) por lo que creo que si seria posible guardar ese valor en una variable, en caso contrario le habria sugerido que usara una tabla temporal.....

[gnzsoloyo]

Estás agrupando en esa query:

Código SQL:

SELECT MAX(traslado_id) FROM traslado GROUP BY traslado.remolque_otros
 

por lo que a menos que remolque_otros contenga un único valor en tod la tabla, se requeriría un array para lograr eso.
En caso de obtener 1+N valores, ya no sirve y habría que usar una tabla temporal (de las de MySQL, no de SQL Server), o bien GROUP_CONCAT() para obtener una lista separada por comas en un campo de tipo VARCHAR y luego usar FIND_IN_SET() en la siguiente query.
de todos modos resutlaría algo ineficiente...

Son las limitaciones de cada DBMS.

[planmental]

Bueno veré cómo modificarlo para que quede lo más óptimo posible pues estoy alentando el servidor con cada consulta que hago con el código

Gracias a los dos, si me funciona publicaré el código que utilicé.

[planmental]

Hola gnzsoloyo y Libras

Para comentarles que he podido hacer algo mas o menos óptimo que por lo menos me muestra los resultados filtrando las fechas como me dijo Libras

El código es:

Código MySQL:

SELECT *
FROM (SELECT traslado_id, remolque_otros, fecha_servicio
  FROM 
    (SELECT traslado_id, remolque_otros, fecha_servicio
    FROM traslado 
    INNER JOIN servicio 
    ON traslado.servicio_id = servicio.servicio_id
 
    INNER JOIN cliente
    ON servicio.cliente_id = cliente.cliente_id
    
    INNER JOIN localidad 
ON traslado.localidad_destino = localidad.localidad_id
 
   WHERE cliente.alias = '".$_GET['c']."'
   AND localidad.alias != 'BASE'
  ) T1
  WHERE   TIMESTAMPDIFF(DAY, STR_TO_DATE(fecha_servicio,'%Y-%m-%d %T'),CURDATE()) BETWEEN 0 AND 10
    AND traslado_id in (SELECT MAX(traslado.traslado_id) FROM traslado GROUP BY remolque_otros)
  ) T2 GROUP BY remolque_otros
 

No sé si se pueda optimizar mejor con un limit o un order by.

Bueno aún sigo con el problema pero es menor. Gracias por su atención

[Libras]

Te recomendaria un indice en tu tabla traslados, y que este indice este sobre le columna traslado_id