ASA SQL User's Guide
  Query Optimization and Execution
    Indexes
      Improving index performance

Composite indexes

An index can contain one, two, or more columns. An index on two or more columns is called a composite index. For example, the following statement creates a two-column composite index:

CREATE INDEX name
ON employee (emp_lname, emp_fname)

A composite index is useful if the first column alone does not provide high selectivity. For example, a composite index on emp_lname and emp_fname is useful when many employees have the same last name. A composite index on emp_id and emp_lname would not be useful because each employee has a unique ID, so the column emp_lname does not provide any additional selectivity.

Additional columns in an index can allow you to narrow down your search, but having a two-column index is not the same as having two separate indexes. A composite index is structured like a telephone book, which first sorts people by their last names, and then all the people with the same last name by their first names. A telephone book is useful if you know the last name, even more useful if you know both the first name and the last name, but worthless if you only know the first name and not the last name.

The compressed B-tree index method substantially improves performance for composite indexes.

Column order 

When you create composite indexes, you should think carefully about the order of the columns. Composite indexes are useful for doing searches on all of the columns in the index or on the first columns only; they are not useful for doing searches on any of the later columns alone.

If you are likely to do many searches on one column only, that column should be the first column in the composite index. If you are likely to do individual searches on both columns of a two-column index, you may want to consider creating a second index that contains the second column only.

Primary keys that have more than one column are always automatically indexed as composite indexes with their columns in the order that they appear in the table definition, not in the order that they are specified in the primary key definition. You should consider the searches that you will execute involving the primary key to determine which column should come first. Consider adding an extra index on any later column of the primary key that is frequently searched.

For example, suppose you create a composite index on two columns. One column contains employee's first names, the other their last names. You could create an index that contains their first name, then their last name. Alternatively, you could index the last name, then the first name. Although these two indexes organize the information in both columns, they have different functions.

CREATE INDEX fname_lname
   ON employee emp_fname, emp_lname;
CREATE INDEX lname_fname
   ON employee emp_lname, emp_fname;

Suppose you then want to search for the first name John. The only useful index is the one containing the first name in the first column of the index. The index organized by last name then first name is of no use because someone with the first name John could appear anywhere in the index.

If you think it likely that you will need to look up people by first name only or second name only, then you should consider creating both of these indexes.

Alternatively, you could make two indexes, each containing only one of the columns. Remember, however, that Adaptive Server Anywhere only uses one index to access any one table while processing a single query. Even if you know both names, it is likely that Adaptive Server Anywhere will need to read extra rows, looking for those with the correct second name.

When you create an index using the CREATE INDEX command, as in the example above, the columns appear in the order shown in your command.

Primary key indexes and column order 

The order of the columns in a primary key index is enforced to be the same as the order in which the columns appear in the table's definition, regardless as to the ordering of the columns specified in the PRIMARY KEY constraint. Moreover, Adaptive Server Anywhere enforces an additional constraint that a table's primary key columns must be at the beginning of each row. Hence if a primary key is added to an existing table the server may rewrite the entire table to ensure that the key columns are at the beginning of each row.

In situations where more than one column appears in a primary key, you should consider the types of searches needed. If appropriate, switch the order of the columns in the table definition so the most frequently searched-for column appears first, or create separate indexes, as required, for the other columns.

Composite indexes and ORDER BY 

By default, the columns of an index are sorted in ascending order, but they can optionally be sorted in descending order by specifying DESC in the CREATE INDEX statement.

Adaptive Server Anywhere can choose to use an index to optimize an ORDER BY query as long as the ORDER BY clause contains only columns included in that index. In addition, the columns in the index must be ordered in exactly the same way, or in exactly the opposite way, as the ORDER BY clause. For single-column indexes, the ordering is always such that it can be optimized, but composite indexes require slightly more thought. The table below shows the possibilities for a two-column index.

An index with more than two columns follows the same general rule as above. For example, suppose you have the following index:

CREATE INDEX idx_example
ON table1 (col1 ASC, col2 DESC, col3 ASC)

In this case, the following queries can be optimized:

SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 DESC, col3 ASC

and

SELECT col1, col2, col3 from example
ORDER BY col1 DESC, col2 ASC, col3 DESC

The index is not used to optimize a query with any other pattern of ASC and DESC in the ORDER BY clause. For example:

SELECT col1, col2, col3 from table1
ORDER BY col1 ASC, col2 ASC, col3 ASC

is not optimized.