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. (More about that in 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:
Note: Derby does not support indexing on columns with data types like BLOB, CLOB, and XML.
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