ASA SQL User's Guide
  Monitoring and Improving Performance

Using keys to improve query performance

Primary keys and foreign keys, while used primarily for validation purposes, can also improve database performance.

Example 

The following example illustrates how primary keys can make queries execute more quickly.

SELECT *
FROM employee
WHERE emp_id = 390

The simplest way for the server to execute this query would be to look at all 75 rows in the employee table and check the employee ID number in each row to see if it is 390. This does not take very long since there are only 75 employees, but for tables with many thousands of entries a sequential search can take a long time.

The referential integrity constraints embodied by each primary or foreign key are enforced by Adaptive Server Anywhere through the help of an index, implicitly created with each primary or foreign key declaration. The emp_id column is the primary key for the employee table. The corresponding primary key index permits the retrieval of employee number 390 quickly. This quick search takes almost the same amount of time whether there are 100 rows or 1,000,000 rows in the employee table.

Using primary keys to improve query performance
Using foreign keys to improve query performance
Separate primary and foreign key indexes