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:
- When you define a primary key, unique, or foreign key constraint on a
table. See "CONSTRAINT clause" in the Java DB 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.
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
Java DB Reference Manual for details on creating indexes,
including restrictions on key size.
Note: Derby does
not support indexing on columns with data types like BLOB, CLOB, and XML.