Re: Optimization on JOIN

On 22 Jan 2010, at 7:59, Yan Cheng Cheok wrote:

> SELECT measurement_type.value, measurement.value, measurement_unit.value
>    FROM
>    measurement_type INNER JOIN
>        (measurement_unit INNER JOIN
>            (measurement INNER JOIN
>                (lot INNER JOIN unit ON (lot_id = fk_lot_id) WHERE lot_id = 7)
>            ON (fk_unit_id = unit_id))
>        ON (fk_measurement_unit_id = measurement_unit_id))
>    ON (fk_measurement_type_id = measurement_type_id);


Yeah, you can't use WHERE in the middle of an expression. You can just add your extra constraint to the ON-clause here
though:ON (lot_id = fk_lot_id AND lot_id = 7) 

You don't need to nest all those joins btw, and it doesn't help to read your query. I turned it inside out into this to
makeit a bit more readable, I hope I got it right: 

SELECT measurement_type.value, measurement.value, measurement_unit.value
FROM lot INNER JOIN unit ON (lot_id = fk_lot_id)
INNER JOIN measurement ON (fk_unit_id = unit_id)
INNER JOIN measurement_unit ON (fk_measurement_unit_id = measurement_unit_id)
INNER JOIN measurement_type ON (fk_measurement_type_id = measurement_type_id)
WHERE lot_id = 7

Since you don't seem to need any data from lot in your results you can remove that table from your query like this:

SELECT measurement_type.value, measurement.value, measurement_unit.value
FROM measurement ON (fk_unit_id = unit_id)
INNER JOIN measurement_unit ON (fk_measurement_unit_id = measurement_unit_id)
INNER JOIN measurement_type ON (fk_measurement_type_id = measurement_type_id)
WHERE fk_lot_id = 7

Notice that we now use a different column in the WHERE clause, namely measurement.fk_lot_id instead of lot.lot_id.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b5995db10606279919623!