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.
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.
