# 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
- Multiple-column index example

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