ASA Getting Started
  Selecting Data from Database Tables
    Ordering query results

Using indexes to improve ORDER BY performance

Sometimes there is more than one possible way for the Adaptive Server Anywhere database server to execute a query with an ORDER BY clause. You can use indexes to enable the database server to search the tables more efficiently.

Queries with WHERE and ORDER BY clauses 

An example of a query that can be executed in more than one possible way is one that has both a WHERE clause and an ORDER BY clause.

SELECT *
FROM customer
WHERE id > 300
ORDER BY company_name

In this example, Adaptive Server Anywhere must decide between two strategies:

1. Go through the entire customer table in order by company name, checking each row to see if the customer id is greater than 300.

2. Use the key on the id column to read only the companies with id greater than 300. The results would then need to be sorted by company name.

If there are very few id values greater than 300, the second strategy is better because only a few rows are scanned and quickly sorted. If most of the id values are greater than 300, the first strategy is much better because no sorting is necessary.

Solving the problem 

Creating a two-column index on id and company_name could solve the example above. Adaptive Server Anywhere can use this index to select rows from the table in the correct order. However, keep in mind that indexes take up space in the database file and involve some overhead to keep up to date. Do not create indexes indiscriminately.

For more information, see Using indexes.