For some queries, join order can make the difference between a table scan
(expensive) and an index scan (cheap). Here's an example:
select ht.hotel_id, ha.stay_date, ht.depart_time
from hotels ht, Hotelavailability ha
where ht.hotel_id = ha.hotel_id and
ht.room_number = ha.room_number
and ht.bed_type = 'KING'
and ht.smoking_room = 'NO'
order by ha.stay_date
If chooses Hotels as the outer table,
it can use the index on Hotels to retrieve qualifying
rows. Then it
need only look up data in HotelAvailability three
times, once for each qualifying row. And to retrieve the appropriate rows
from HotelAvailability, it can use an index for HotelAvailability's hotel_id column
instead of scanning the entire table.
If chooses the other order, with HotelAvailability as the outer table, it will have to probe the Hotels table for every row, not just three rows, because
there are no qualifications on the HotelAvailability table.
For more information about join order, see .
usually chooses a good join order. However, as with index use,
you should make sure. Analyze the way is executing your application's
queries. See for information on
how to do this.