User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 24. Monitoring and Improving Performance       

Sorting query results

Many queries against a database have an ORDER BY clause so that the rows come out in a predictable order. Indexes are used to accomplish the ordering quickly. For example,

SELECT *
FROM customer
ORDER BY customer.lname

can use the index on the lname column of the customer table to access the rows of the customer table in alphabetical order by last name.

Queries with WHERE and ORDER BY clauses 

A potential problem arises when a query has both a WHERE clause and an ORDER BY clause.

SELECT *
FROM customer
WHERE id > 300
ORDER BY company_name

The server 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 

The example above could be solved by creating a two-column index on id and company_name. (The order of the two columns is important.) The server could then use this index to select rows from the table and have them 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.