Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
An index on more than one column is called a composite index.
For example, the following statement creates a two-column composite index:
CREATE INDEX name ON Employees ( Surname, GivenName );
A composite index is useful if the first column alone does not provide high selectivity. For example, a composite index on Surname and GivenName is useful when many employees have the same surname. A composite index on EmployeeID and Surname would not be useful because each employee has a unique ID, so the column Surname does not provide any additional selectivity.
Additional columns in an index can allow you to narrow down your search, but having a two-column index is not the same as having two separate indexes. A composite index is structured like a telephone book, which first sorts people by their surnames, and then all the people with the same surname by their given names. A telephone book is useful if you know the surname, even more useful if you know both the given name and the surname, but worthless if you only know the given name and not the surname.
When you create composite indexes, think carefully about the order of the columns. Composite indexes are useful for doing searches on all the columns in the index or on the first columns only; they are not useful for doing searches on any of the later columns alone.
If you are likely to do many searches on one column only, that column should be the first column in the composite index. If you are likely to do individual searches on both columns of a two-column index, consider creating a second index that contains the second column only.
For example, suppose you create a composite index on two columns. One column contains employee's given names, the other their surnames. You could create an index that contains their given name, then their surname. Alternatively, you could index the surname, then the given name. Although these two indexes organize the information in both columns, they have different functions.
CREATE INDEX IX_GivenName_Surname ON Employees ( GivenName, Surname ); CREATE INDEX IX_Surname_GivenName ON Employees ( Surname, GivenName );
Suppose you then want to search for the given name John. The only useful index is the one containing the given name in the first column of the index. The index organized by surname then given name is of no use because someone with the given name John could appear anywhere in the index.
If you are more likely to look up people by given name only or surname only, consider creating both of these indexes.
Alternatively, you could make two indexes, each containing only one of the columns. However, remember that the database server only uses one index to access any one table while processing a single query. Even if you know both names, it is likely that the database server needs to read extra rows, looking for those with the correct second name.
When you create an index using the CREATE INDEX statement, as in the example above, the columns appear in the order shown in your statement.
By default, the columns of an index are sorted in ascending order, but they can optionally be sorted in descending order by specifying DESC in the CREATE INDEX statement.
The database server can choose to use an index to optimize an ORDER BY query as long as the ORDER BY clause contains only columns included in that index. In addition, the columns in the index must be ordered in exactly the same way, or in exactly the opposite way, as the ORDER BY clause. For single-column indexes, the ordering is always such that it can be optimized, but composite indexes require slightly more thought. The table below shows the possibilities for a two-column index.
|Index columns||Optimizable ORDER BY queries||Not optimizable ORDER BY queries|
|ASC, ASC||ASC, ASC or DESC, DESC||ASC, DESC or DESC, ASC|
|ASC, DESC||ASC, DESC or DESC, ASC||ASC, ASC or DESC, DESC|
|DESC, ASC||DESC, ASC or ASC, DESC||ASC, ASC or DESC, DESC|
|DESC, DESC||DESC, DESC or ASC, ASC||ASC, DESC or DESC, ASC|
An index with more than two columns follows the same general rule as above. For example, suppose you have the following index:
CREATE INDEX idx_example ON table1 ( col1 ASC, col2 DESC, col3 ASC );
In this case, the following queries can be optimized:
SELECT col1, col2, col3 FROM table1 ORDER BY col1 ASC, col2 DESC, col3 ASC;
SELECT col1, col2, col3 FROM example ORDER BY col1 DESC, col2 ASC, col3 DESC;
The index is not used to optimize a query with any other pattern of ASC and DESC in the ORDER BY clause. For example, the following statement is not optimized:
SELECT col1, col2, col3 FROM table1 ORDER BY col1 ASC, col2 ASC, col3 ASC;