[thelist] query takes too long

[Jacques Capesius]

thelist-bounces at lists.evolt.org wrote:
> I have this query that takes way too long How can i improve its
> performance? 
> 
> SELECT
> inventario.id_inventario,inventario.Contenedor,(semaforos.SEM1_status
> + semaforos.SEM2_status + semaforos.SEM3_status +
> semaforos.SEM4_status + semaforos.SEM5_status +
> semaforos.SEM6_status) AS total,inventario.FechaOut,navieras.nombre
> AS naviera,contenedor.Size,tipocontenedor.Nombre AS tipo FROM
> inventario INNER JOIN semaforos ON (inventario.id_inventario =
> semaforos.id_inventario) INNER JOIN contenedor ON
> (inventario.Contenedor = contenedor.Contenedor) INNER JOIN
> tipocontenedor ON (contenedor.`Type` = tipocontenedor.`Type`) INNER
> JOIN navieras ON (inventario.id_naviera = navieras.id_naviera) WHERE
> inventario.id_naviera = 1           

Another thing to consider is that joins suck up a lot of processor power, so you want to minimize the rows that are being joined.

With this in mind, place your joins toward the end of the query, and the other limiting criteria towards the front. That being said, your query might become this:

SELECT 
inventario.id_inventario,
inventario.Contenedor,(semaforos.SEM1_status + semaforos.SEM2_status + 
	semaforos.SEM_status + semaforos.SEM4_status + semaforos.SEM5_status +
	semaforos.SEM6_status) AS total,
inventario.FechaOut,
navieras.nombre AS naviera,
contenedor.Size,
tipocontenedor.Nombre AS tipo 
FROM inventario, semaforos, contenedor,navieras
WHERE nventario.id_naviera = 1  
AND inventario.id_inventario = semaforos.id_inventario
AND contenedor.`Type` = tipocontenedor.`Type`
AND inventario.Contenedor = contenedor.Contenedor
AND inventario.id_naviera = navieras.id_naviera

hopefully that'll speed things up a little, or a lot. it tends to work for me.

-jacques :)