Adding a point query with different indexing requirements

The index choice for the range query on price produced a clear performance choice when all possibly useful indexes were considered. Now, assume this query also needs to run against titles:

select price
from titles
where title = "Looking at Leeks"

You know that there are very few duplicate titles, so this query returns only one or two rows.

Considering both this query and the previous query, Table 6-3 shows four possible indexing strategies and estimate costs of using each index. The estimates for the numbers of index and data pages were generated using a fillfactor of 75 percent with sp_estspace:

sp_estspace titles, 1000000, 75

The values were rounded for easier comparison.

Table 6-3: Comparing index strategies for two queries

Possible index choice

Index pages

Range query on price

Point query on title

1

Nonclustered on title Clustered on price

36,800 650

Clustered index, about 26,600 pages (135,000 *.19)

With 16K I/O: 3,125 I/Os

Nonclustered index, 6 I/Os

2

Clustered on title Nonclustered on price

3,770 6,076

Table scan, 135,000 pages

With 16K I/O: 17,500 I/Os

Clustered index, 6 I/Os

3

Nonclustered on title, price

36,835

Nonmatching index scan, about 35,700 pages

With 16K I/O: 4,500 I/Os

Nonclustered index, 5 I/Os

4

Nonclustered on price, title

36,835

Matching index scan, about 6,800 pages (35,700 *.19)

With 16K I/O: 850 I/Os

Nonmatching index scan, about 35,700 pages

With 16K I/O: 4,500 I/Os

Examining the figures in Table 6-3 shows that:

The best indexing strategy for a combination of these two queries is to use two indexes:

You may need additional information to help you determine which indexing strategy to use to support multiple queries. Typical considerations are: