Use composite indexes
The optimizer can use a composite index (one that covers more than one column) in several 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.
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 about bidirectional traverse, see the IBM® Informix® Guide to SQL: Syntax.
- 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.