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.