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 make it 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!