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