Creating Covering Indexes
If you take multicolumn index awareness a step further, you can greatly boost performance in some situations: when all the data you need is in the index, the optimizer may retrieve it from the index without going to the data pages. This index is sometimes called a "covering" index and can be a big plus. Why? Because index entries are shorter than data rows and there are more of them on a page; the SQL engine can get to the covering index faster than to the data.
To use a covering index, you must meet the following conditions.
Every column in the SELECT list and every column in every other clause (WHERE, ORDER BY, GROUP BY, HAVING) must be in the index.
The index must not be disabled by functions or math or conversions—or any of the constructions covered earlier in this chapter.
The WHERE clause must use columns in the order in which they appear in the index (it can go directly to a specific ordnum or combination of ordnum and prodnum but not to prodnum alone, as discussed in "Using Multicolumn Indexes" at the end of the previous section).
For example, to resolve a query including only the orderdetail ordnum and prodnum columns, everything you're asking for (prodnum and ordnum) is in the ordprodix index in ordnum order. If you specify ordnum or ordnum and prodnum in the WHERE clause, the SQL engine can find the index rows that contain that value and return results from there, never touching the data pages.
Adaptive Server Anywhere
select prodnum, ordnum
from orderdetail
where ordnum = 84
prodnum ordnum
=========== ===========
1099 84
1255 84
2050 84
[3 rows]
A less efficient variation results when all the columns in the query are in the index but you specify only a nonleading column (prodnum) in the WHERE clause. The SQL engine may resolve the query by reading the entire index sequentially. In some performance-monitoring tools, this is identified as an index scan, parallel to a table scan but faster.
Adaptive Server Anywhere
select prodnum, ordnum
from orderdetail
where prodnum = 1099
prodnum ordnum
=========== ===========
1099 84
1099 89
[2 rows]
But back to the leading column situation: if you add a column that is not part of the index (here unit), the optimizer will still be able to use the index, but it'll have to get the nonindex values from the data pages (Figure 6–16). The same thing happens when you add nonindex conditions to the WHERE clause. The index no longer "covers" the query.
)
Figure 6–16. Covering Indexes
You can cover queries by understanding your indexes and avoiding adding extra columns to your SELECT list. Don't do a SELECT * if all you want is the product number! Remember, the index covers the query only if everything in the SELECT and WHERE clauses is in the index, if you haven't disabled the index, and if you pay attention to order in multicolumn indexes.