Key order and performance in composite indexes

Covered queries can provide excellent response time for specific queries when the leading columns are used.

With the composite nonclustered index on au_lname, au_fname, au_id, this query runs very quickly:

select au_id
    from authors
where au_fname = "Eliot" and au_lname = "Wilk"

This covered point query needs to read only the upper levels of the index and a single page in the leaf-level row in the nonclustered index of a 5000-row table.

This similar-looking query (using the same index) does not perform quite as well. This query is still covered, but searches on au_id:

select au_fname, au_lname 
    from authors
where au_id = "A1714224678" 

Since this query does not include the leading column of the index, it has to scan the entire leaf level of the index, about 95 reads.

Adding a column to the select list in the query above, which may seem like a minor change, makes the performance even worse:

select au_fname, au_lname, phone
    from authors
where au_id = "A1714224678" 

This query performs a table scan, reading 222 pages. In this case, the performance is noticeably worse. For any search argument that is not the leading column, Adaptive Server has only two possible access methods: a table scan, or a covered index scan.

It does not scan the leaf level of the index for a non-leading search argument and then access the data pages. A composite index can be used only when it covers the query or when the first column appears in the where clause.

For a query that includes the leading column of the composite index, adding a column that is not included in the index adds only a single data page read. This query must read the data page to find the phone number:

select au_id, phone
    from authors
where au_fname = "Eliot" and au_lname = "Wilk"

Table 6-2 shows the performance characteristics of different where clauses with a nonclustered index on au_lname, au_fname, au_id and no other indexes on the table.

Table 6-2: Composite nonclustered index ordering and performance

Columns in the where clause

Performance with the indexed columns in the select list

Performance with other columns in the select list

au_lname

or au_lname, au_fname

or au_lname, au_fname, au_id

Good; index used to descend tree; data level is not accessed

Good; index used to descend tree; data is accessed (one more page read per row)

au_fname

or au_id

or au_fname, au_id

Moderate; index is scanned to return values

Poor; index not used, table scan

Choose the ordering of the composite index so that most queries form a prefix subset.