Multiple-column index example

The following queries do provide start and stop conditions for a scan of the primary key index on the flight_id and segment_number columns in Flights.

-- the where clause compares both columns with valid
-- operators to constants
SELECT *
FROM Flights
WHERE flight_id = 'AA1115'
AND segment_number < 2
-- the first column is in a valid comparison
SELECT *
FROM Flights
WHERE flight_id < 'BB'
-- LIKE is transformed into >= and <=, providing
-- start and stop conditions
SELECT *
FROM Flights
WHERE flight_id LIKE 'AA%'

The following queries do not:

-- segment_number is in the index, but it's not the first column;
-- there's no logical starting and stopping place
SELECT *
FROM Flights
WHERE segment_number = 2
-- Derby  would scan entire table; comparison of first column
-- is not with a constant or column in another table
-- and no covering index applies
SELECT *
FROM Flights
WHERE orig_airport = dest_airport
AND segment_number < 2