# Create indexes, and make sure they are being used

Indexes speed up queries dramatically if the table is much larger than the number of rows retrieved.

By creating indexes on columns by which you often search a table, you can reduce the number of rows that Derby has to scan, thus improving performance. Depending on the size of the table and the number of rows returned, the improvement can be dramatic.

Indexes work best when the number of rows returned from the query is a fraction of the number of rows in the table.

There are some trade-offs in using indexes: indexes speed up searches but slow down inserts and updates. As a general rule, every table should have at least a primary key constraint.

See Always create indexes for more information.

Parent topic: Performance tips and tricks
Related concepts
Use prepared statements with substitution parameters
Ensure that table statistics are accurate
Increase the size of the data page cache
Tune the size of database pages
Avoid expensive queries
Use the appropriate getXXX and setXXX methods for the type
Tune database booting/class loading
Avoid inserts in autocommit mode if possible
Improve the performance of table functions
Configure Derby to use an in-memory database
Increase the concurrency of sequences
Shut down the system properly
Put Derby first in your classpath