Create useful indexes

Indexes are useful when a query contains a WHERE clause.

Without a WHERE clause, Derby is supposed to return all the data in the table, and so a table scan is the correct (if not desirable) behavior. (For more information, see Prevent the user from issuing expensive queries.)

Derby creates indexes on tables in the following situations:

For an index to be useful for a particular statement, one of the columns in the statement's WHERE clause must be the first column in the index's key.

Note: For a complete discussion of how indexes work and when they are useful, see What is an index? and Index use and access paths.

Indexes provide some other benefits as well:

See "CREATE INDEX statement" in the Derby Reference Manual for details on creating indexes, including restrictions on key size.

Related concepts
Make sure indexes are being used, and rebuild them
Think about index order
Think about join order
Decide whether a descending index would be useful