# Covering indexes

Even when there is no definite starting or stopping point for an index scan, an index can speed up the execution of a query if the index covers the query. An index covers the query if all the columns specified in the query are part of the index.

These are the columns that are all columns referenced in the query, not just columns in a WHERE clause. If so, Derby never has to go to the data pages at all, but can retrieve all data through index access alone. For example, in the following queries, `OrigIndex` covers the query:

    SELECT orig_airport
    FROM Flights

    SELECT DISTINCT lower(orig_airport) FROM Flights
    FROM Flights

Derby can get all required data out of the index instead of from the table.

Note: If the query produces an updatable result set, Derby will retrieve all data from the data pages even if there is an index that covers the query.

- Single-column index examples
  The following queries do not provide start and stop conditions for a scan of OrigIndex, the index on the orig_airport column in Flights. However, some of these queries allow Derby to do an index rather than a table scan because the index covers the query.
- 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.

Parent topic: Index use and access paths
Related concepts
What is an index?
What's optimizable?
Useful indexes can use qualifiers
When a table scan is better
Indexes have a cost for inserts, updates, and deletes