An index is a database structure that provides quick lookup of data in
a column or columns of a table.
For example, a Flights
table in a travelDB
database has three indexes:
- An index on the orig_airport column (called OrigIndex)
- An index on the dest_airport column (called DestIndex)
- An index enforcing the primary key constraint
on the flight_id and segment_number columns (which has a system-generated name)
This means there are three separate structures that provide shortcuts into
the Flights table. Let's look at one of those
stores every value in the orig_airport
column, plus information on how to retrieve the entire corresponding
row for each value.
- For every row in Flights, there is an entry in OrigIndex that includes the value of the orig_airport column and the address of the row itself. The entries are
stored in ascending order by the orig_airport values.
When an index includes more than one column, the first column is the main
one by which the entries are ordered. For example, the index on (flight_id
) is ordered first by flight_id
. If there is more than one flight_id
of the same value, those entries are then ordered by segment_number
. An excerpt from the entries in the index might look like
Indexes are helpful only sometimes. This particular index is useful when
a statement's WHERE clause is looking for rows for which the value of orig_airport is some specific value or range of values.
SELECTs, UPDATEs, and DELETEs can all have WHERE clauses.
For example, OrigIndex
is helpful for statements
such as the following:
WHERE orig_airport = 'SFO'
WHERE orig_airport < 'BBB'
WHERE orig_airport >= 'MMM'
is helpful for statements such as the
WHERE dest_airport = 'SCL'
The primary key index (on flight_id
) is helpful for statements such as the following:
WHERE flight_id = 'AA1111'
WHERE flight_id BETWEEN 'AA1111' AND 'AA1115'
FROM FlightAvailability AS fa, Flights AS fts
WHERE flight_date > CURRENT_DATE
AND fts.flight_id = fa.flight_id
AND fts.segment_number = fa.segment_number
The next section discusses why the indexes are helpful for these statements
but not for others.