Search code examples
mysqlleft-join

Optimize subqueries with LEFT JOINS MYSQL

I hope you can help me! I currently have this query:

SELECT servicio.*,
c.num_cliente,c.nombre,c.operador,
mdl.modelo_marca,mdl.modelo_telcel,
col.colores,
prob.problema,
diag.notas,diag.solucion,diag.tipo_servicios AS tipo_servicios_diagnostico,diag.nuevo_imei AS nuevo_imei_diagnostico,
diag.nivel_repar,diag.notasqc AS diagnos_notasqc,diag.fecha AS diagnos_fecha,diag.notas AS diagnos_notas,
user_name.nombre AS name_user,user_name.apellido,
(SELECT revision.status2_ser FROM revision WHERE revision.status2_ser IN('ENTREGADO') AND servicio.id = revision.id_servicio  ORDER BY revision.id DESC LIMIT 1) AS status2_ser,
(SELECT revision.fecha_status FROM revision WHERE revision.status2_ser IN('ENTREGADO') AND servicio.id = revision.id_servicio  ORDER BY revision.id DESC LIMIT 1) AS fecha_status_revision,
(SELECT revision.status2_ser FROM revision WHERE revision.status2_ser IN('REPARADO') AND servicio.id = revision.id_servicio  ORDER BY revision.id DESC LIMIT 1) AS status2_ser_repadado,
(SELECT revision.fecha_status FROM revision WHERE revision.status2_ser IN('REPARADO') AND servicio.id = revision.id_servicio  ORDER BY revision.id DESC LIMIT 1) AS revi2_fecha_status_revision,
(SELECT env.guia_entrega FROM envios AS env WHERE JSON_CONTAINS(env.envio_grupal, JSON_ARRAY(CAST(servicio.id AS CHAR))) OR env.id_servicio = servicio.id ORDER BY env.id DESC LIMIT 1) as guia_entrega_envio,
(SELECT env2.fecha_envio FROM envios AS env2 WHERE JSON_CONTAINS(env2.envio_grupal, JSON_ARRAY(CAST(servicio.id AS CHAR))) OR env2.id_servicio = servicio.id ORDER BY env2.id DESC LIMIT 1) as guia_entrega_envio_fecha
FROM servicio
LEFT JOIN usuarios AS user_name ON servicio.id_user = user_name.id
LEFT JOIN clientes AS c ON servicio.id_cac = c.id
LEFT JOIN modelos AS mdl ON servicio.id_modelo = mdl.id
LEFT JOIN colores AS col ON servicio.id_color = col.id
LEFT JOIN problemas_genericos AS prob ON CAST(servicio.problema_generico AS UNSIGNED) = prob.id
LEFT JOIN diagnostico AS diag ON diag.id = (SELECT id FROM diagnostico AS diag WHERE diag.id_servicio = servicio.id AND diag.tipo_servicios <> '' ORDER BY diag.id DESC LIMIT 1)
WHERE
servicio.fecha_ingreso >= '2022-03-07 00:00:00' AND servicio.fecha_ingreso <= '2022-03-16 23:59:59' AND servicio.status_ser IN('ENTREGADO') AND servicio.id_marca = 1
ORDER BY servicio.id DESC

the query works, but the performance is not as expected, it sometimes takes up to 10 seconds to retrieve more than 1000 records, the main table where I consult this data has approximately 210,000 records, could someone help me to make it more optimal please?

This is my explain:

EXPLAIN

Update my query but not there changes in the performance:

SELECT servicio.*,
c.num_cliente,c.nombre,c.operador,
mdl.modelo_marca,mdl.modelo_telcel,
col.colores,
prob.problema,
diag.notas,diag.solucion,diag.tipo_servicios AS tipo_servicios_diagnostico,diag.nuevo_imei AS nuevo_imei_diagnostico,
diag.nivel_repar,diag.notasqc AS diagnos_notasqc,diag.fecha AS diagnos_fecha,diag.notas AS diagnos_notas,
user_name.nombre AS name_user,user_name.apellido,
(SELECT env.guia_entrega FROM envios AS env WHERE JSON_CONTAINS(env.envio_grupal, JSON_ARRAY(CAST(servicio.id AS CHAR))) OR env.id_servicio = servicio.id ORDER BY env.id DESC LIMIT 1) as guia_entrega_envio,
(SELECT env2.fecha_envio FROM envios AS env2 WHERE JSON_CONTAINS(env2.envio_grupal, JSON_ARRAY(CAST(servicio.id AS CHAR))) OR env2.id_servicio = servicio.id ORDER BY env2.id DESC LIMIT 1) as guia_entrega_envio_fecha,
(CASE WHEN x.sta = 'ENTREGADO' THEN x.sta END) AS status2_ser,
(CASE WHEN x.sta = 'ENTREGADO' THEN x.g_fecha_status END) AS fecha_status_revision,
(CASE WHEN w.sta = 'REPARADO' THEN w.sta END) AS revi2_fecha_status_revision,
(CASE WHEN w.sta = 'REPARADO' THEN w.g_fecha_status END) AS revi2_fecha_status_revision
FROM servicio
LEFT JOIN usuarios AS user_name ON servicio.id_user = user_name.id
LEFT JOIN clientes AS c ON servicio.id_cac = c.id
LEFT JOIN modelos AS mdl ON servicio.id_modelo = mdl.id
LEFT JOIN colores AS col ON servicio.id_color = col.id
LEFT JOIN problemas_genericos AS prob ON CAST(servicio.problema_generico AS UNSIGNED) = prob.id
LEFT JOIN diagnostico AS diag ON diag.id = (SELECT id FROM diagnostico AS diag WHERE diag.id_servicio = servicio.id AND diag.tipo_servicios <> '' ORDER BY diag.id DESC LIMIT 1)

LEFT JOIN
(SELECT revision.status2_ser AS sta, revision.id_servicio,max(revision.fecha_status) AS g_fecha_status
    FROM revision
    WHERE revision.status2_ser IN("ENTREGADO")
    GROUP BY revision.id_servicio) x ON servicio.id = x.id_servicio
    
    LEFT JOIN
(SELECT revision.status2_ser AS sta, revision.id_servicio,max(revision.fecha_status) AS g_fecha_status
    FROM revision
    WHERE revision.status2_ser IN("REPARADO")
    GROUP BY revision.id_servicio) w ON servicio.id = w.id_servicio

WHERE
servicio.fecha_ingreso >= '2022-03-07 00:00:00' AND servicio.fecha_ingreso <= '2022-03-16 23:59:59' AND servicio.status_ser IN('ENTREGADO') AND servicio.id_marca = 1
ORDER BY servicio.id DESC
Solution

    • These indexes may help:

      servicio:  INDEX(id_marca, fecha_ingreso, status_ser)
servicio:  INDEX(id_marca, status_ser, fecha_ingreso)
revision:  INDEX(status2_ser, id_servicio, id,  fecha_status)
envios:  INDEX(envio_grupal, id_servicio, id,  guia_entrega)
envios:  INDEX(envio_grupal, id_servicio, id,  fecha_envio)
diag:  INDEX(id_servicio, tipo_servicios, id)

    When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

    • I see some cases of the same subquery being performed twice because you needed two columns. Recommend you use a LEFT JOIN to get both columns at the same time.

    • If you regularly need to test things inside a JSON column, especially if the types are need CASTing, consider adding an extra column to the table to make it easily testable without all the Json overhead.

    • (This may eliminate a sort without changing the effect.) Replace ORDER BY servicio.id DESC with ORDER BY fecha_ingreso DESC, servicio.id DESC

    • ON CAST(servicio.problema_generico AS UNSIGNED) = prob.id probably prevents use of an index. See if you can fix the datatypes to avoid the need for CAST.