SQL Processing - Relational Database Index Design and the Optimizers - page 41

MAKE,
MODEL,
YEAR

MC = 1, FF = 1%

10,000 index rows
(1% of 1,000,000)

CAR

Figure 3.8 One matching
column—a thicker slice.

1,000,000 rows

SQL 3.4

SELECT PRICE, COLOR, DEALERNO

FROM AUTO

WHERE MAKE = :MAKE

AND
YEAR = :YEAR

Some textbooks recommend that index columns should be ordered according
to descending cardinality (the number of distinct values). Taken literally, this
advice would lead to absurd indexes, such as CNO, HEIGHT, WEIGHT, SEX,
but in certain situations—assuming the order of the columns doesn't adversely
affect the performance of SELECT statements [e.g., with WHERE A

=

:A AND

=

B :B, indexes (A, B) and (B, A) would be equivalent] nor that of updates—this

is a reasonable recommendation. It increases the probability that the index will
be useful for other SELECTs.

SQL 3.5

SELECT LNAME, FNAME, CNO

FROM CUST

WHERE SEX = 'M'

AND
(WEIGHT > 90
OR
HEIGHT > 190)

ORDER BY LNAME, FNAME