Using Composite Indexes

The optimizer can use a composite index (one that covers more than one column) in several ways. The database server can use an index on columns a, b, and c (in that order) in the following ways:

• To locate a particular row

  The database server can use a composite index when the first filter is an equality filter and subsequent columns have range (<, <=, >, >=) expressions. The following examples of filters use the columns in a composite index:

  WHERE a=1
  WHERE a>=12 AND a<15
  WHERE a=1 AND b < 5
  WHERE a=1 AND b = 17 AND c >= 40

  The following examples of filters cannot use that composite index:

  WHERE b=10
  WHERE c=221
  WHERE a>=12 AND b=15

• To replace a table scan when all of the desired columns are contained within the index

  A scan that uses the index but does not reference the table is called a key-only search.

• To join column a, columns ab, or columns abc to another table
• To implement ORDER BY or GROUP BY on columns a, ab, or abc but not on b, c, ac, or bc

Execution is most efficient when you create a composite index with the columns in order from most to least distinct. In other words, the column that returns the highest count of distinct rows when queried with the DISTINCT keyword in the SELECT statement should come first in the composite index.

If your application performs several long queries, each of which contains ORDER BY or GROUP BY clauses, you can sometimes improve performance by adding indexes that produce these orderings without requiring a sort. For example, the following query sorts each column in the ORDER BY clause in a different direction:

SELECT * FROM t1 ORDER BY a, b DESC;

To avoid using temporary tables to sort column a in ascending order and column b in descending order, you must create a composite index on (a, b DESC) or on (a DESC, b). You need to create only one of these indexes because of the bidirectional-traverse capability of the database server. For more information on bidirectional traverse, see the IBM Informix: Guide to SQL Syntax.

On the other hand, it can be less expensive to perform a table scan and sort the results instead of using the composite index when the following criteria are met:

• Your table is well ordered relative to your index.
• The number of rows that the query retrieves represents a large percentage of the available data.