# Index use and access paths

If you define an index on a column or columns, the query optimizer can use the index to find data in the column more quickly.

Derby automatically creates indexes to back up primary key, foreign key, and unique constraints, so those indexes are always available to the optimizer, as well as those that you explicitly create with the CREATE INDEX command. The way Derby gets to the data -- through an index or directly through the table -- is called the access path.

- What is an index?
  An index is a database structure that provides quick lookup of data in a column or columns of a table.
- What's optimizable?
  As you learned in the previous section, Derby might be able to use an index on a column to find data more quickly. If Derby can use an index for a statement, that statement is said to be optimizable.
- 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.
- Useful indexes can use qualifiers
  Matching index scans can use qualifiers that further restrict the result set.
- When a table scan is better
  Sometimes a table scan is the most efficient way to access data, even if a potentially useful index is available.
- Indexes have a cost for inserts, updates, and deletes
  Derby has to do work to maintain indexes.

Parent topic: Performance and optimization
Related concepts
Joins and performance
Derby's cost-based optimization