# Performance and optimization

Optimization means that a DBMS makes the best (optimal) choice of access paths, join order, and join strategy.

A DBMS often has a choice about the access path for retrieving data. For example, the DBMS can use an index (fast lookup for specific entries) or scan the entire table to retrieve the appropriate rows. In addition, in statements in which two tables are joined, the DBMS can choose which table to examine first (join order) and how to join the tables (join strategy).

True query optimization means that the DBMS will usually make a good choice regardless of how the query is written. The optimizer does not necessarily make the best choice, just a good one.

Derby can use indexes to improve the performance of DML (data manipulation language) statements such as queries, updates, and deletes. The query optimizer can make decisions about whether to use an index for a particular table (access path) and also makes decisions about join order, type of join, and a few other matters.

This section gives an overview of the Derby optimizer and discusses performance issues in the execution of DML statements.

- Index use and access paths
  If you define an index on a column or columns, the query optimizer can use the index to find data in the column more quickly.
- Joins and performance
  Joins, SQL statements in which Derby selects data from two or more tables using one or more key columns from each table, can vary widely in performance. Factors that affect the performance of joins are join order, indexes, and join strategy.
- Derby's cost-based optimization
  The query optimizer makes a number of cost-based decisions.

Parent topic: DML statements and performance
Related concepts
Locking and performance
Non-cost-based optimizations
Overriding the default optimizer behavior