# Always create indexes

Useful indexes prevent Derby from having to read an entire table to retrieve the data you need.

Have you ever thought what it would be like to look up a phone number in the phone book of a major metropolitan city if the book were not indexed by name? For example, to look up the phone number for John Jones, you could not go straight to the "J" page. You would have to read the entire book. That is what a table scan is like. Derby has to read the entire table to retrieve what you are looking for unless you create useful indexes on your table.

- Create useful indexes
  Indexes are useful when a query contains a WHERE clause.
- Make sure indexes are being used, and rebuild them
  If an index is useful for a query, Derby is probably using it. However, you need to make sure. Analyze the way Derby is executing your application's queries.
- Think about index order
  Derby allows you to create index columns in descending order in addition to creating them in ascending order, the default. Descending indexes provide performance benefits for the kinds of queries that require sorting data in descending order.
- Think about join order
  For some queries, join order can make the difference between a table scan (expensive) and an index scan (cheap).
- Decide whether a descending index would be useful
  Derby allows you to create an index that uses the descending order for a column. Such indexes improve the performance of queries that order results in descending order or that search for the minimum or maximum value of an indexed column.

Parent topic: Avoiding table scans of large tables
Related concepts
Prevent the user from issuing expensive queries