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
Even without knowing the explain plan, structure of tables or indexes, I can risk telling you that your "query" has a very poor performance pattern: the use of many subqueries at the column level. Sometimes there is no other way, but in your case you have a way to solve it much more easily and surely with better performance. Let's see by part:
You have the above repeated 4 times to add, I understand, by type of container and it is basically information that can be obtained from the main query, through a conditional addition:
The other subquery:
it's also basically the same, all 4 times, only the container changes, so why not incorporate it
Servicios
into the main query and resolve everything the same way as above:In this case, where we do a grouping, we need an aggregation function, so we use
MAX
, I understand, from your code, that the price per container/client is the same, so itMAX
always assures us of the price of each container.Other additional comments:
where
group by
with all the columns of theselect
, not doing it is an aberration that allows you for nowmysql
but leaves any standard.Having said all this, the idea is something like this:
Could it be that even making these changes, you do not perceive improvements? yes, it can happen, that's where you have to struggle with the query plan, index definition, etc.