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:

When you define a primary key, unique, or foreign key constraint on a table. See "CONSTRAINT clause" in the Derby Reference Manual for more information.
When you explicitly create an index on a table with a CREATE INDEX statement.

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:

If all the data requested are in the index, Derby does not have to go to the table at all. (See Covering indexes.)
For operations that require a sort (ORDER BY), if Derby uses the index to retrieve the data, it does not have to perform a separate sorting step for some of these operations in some situations. (See About the optimizer's choice of sort avoidance.)

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