I have a problem with a query, it takes me too long to give me the results approximately 10 to 15 minutes and if I select a very old date it takes me 40 to 60 minutes and sometimes it doesn't show me anything
I have the customers table which from there I use the IdCliente field and the RazonSocial field
I have another containerTypes table which from there I use the idTypeContainer field and the TypeContainer field
Another table would be trips in which the fields to be added are stored
Here I have the query I hope and you can help me to debug it
SELECT V.idCliente, C.RazonSocial, V.Pagado,
IFNULL((SELECT SUM(CantCont) FROM viajes WHERE idTipoContenedor=1 and idCliente=V.idCliente and FechaAlta>='".$fechaInicio." 00:00:00' and FechaAlta<='".$fechaFin." 23:59:59'),0) AS Seco20,
(SELECT Precio FROM Servicios WHERE idTipoContenedor=1 and idCliente=V.idCliente) AS PrecioSeco20,
IFNULL((SELECT SUM(CantCont) FROM viajes WHERE idTipoContenedor=4 and idCliente=V.idCliente and FechaAlta>='".$fechaInicio." 00:00:00' and FechaAlta<='".$fechaFin." 23:59:59'),0) AS Isotanque,
(SELECT Precio FROM Servicios WHERE idTipoContenedor=4 and idCliente=V.idCliente) AS PrecioIsotanque,
IFNULL((SELECT SUM(CantCont) FROM viajes WHERE idTipoContenedor=3 and idCliente=V.idCliente and FechaAlta>='".$fechaInicio." 00:00:00' and FechaAlta<='".$fechaFin." 23:59:59'),0) AS Seco40,
(SELECT Precio FROM Servicios WHERE idTipoContenedor=3 and idCliente=V.idCliente) AS PrecioSeco40,
IFNULL((SELECT SUM(CantCont) FROM viajes WHERE idTipoContenedor=2 and idCliente=V.idCliente and FechaAlta>='".$fechaInicio." 00:00:00' and FechaAlta<='".$fechaFin." 23:59:59'),0) AS Refrigerado,
(SELECT Precio FROM Servicios WHERE idTipoContenedor=2 and idCliente=V.idCliente) AS PrecioRefrigerado FROM viajes V
INNER JOIN clientes C ON V.idCliente=C.idCliente WHERE V.FechaAlta>='".$fechaInicio." 00:00:00' and V.FechaAlta<='".$fechaFin." 23:59:59'
GROUP BY V.idCliente ORDER BY V.idCliente
This is the result of the query
Shows the Company Name of the customer talb , the total number of containers from the trips table , the price from the Services table that relates the idTypeContainer from the TiposContenedores table , it gives me a total sum of the total number of containers together with the sum of the price
Here I leave result withEXPLAIN
Here the result of CASE
Unmodified code shows all costs
Modified code does not show most costs