How do composite indexes work?

Solution 1:

Composite indexes work just like regular indexes, except they have multi-values keys.

If you define an index on the fields (a,b,c) , the records are sorted first on a, then b, then c.

Example:

| A | B | C |
-------------
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 5 |

Composite index is like a plain alphabet index in a dictionary, but covering two or more letters, like this:

AA - page 1
AB - page 12

etc.

Table rows are ordered first by the first column in the index, then by the second one etc.

It's usable when you search by both columns OR by first column. If your index is like this:

AA - page 1
AB - page 12
…
AZ - page 245
BA - page 246
…

you can use it for searching on 2 letters ( = 2 columns in a table), or like a plain index on one letter:

A - page 1
B - page 246
…

Note that in case of a dictionary, the pages themself are alphabetically ordered. That's an example of a CLUSTERED index.

In a plain, non-CLUSTERED index, the references to pages are ordered, like in a history book:

Gaul, Alesia: pages 12, 56, 78
Gaul, Augustodonum Aeduorum: page 145
…
Gaul, Vellaunodunum: page 24
Egypt, Alexandria: pages 56, 194, 213, 234, 267

Composite indexes may also be used when you ORDER BY two or more columns. In this case a DESC clause may come handy.

See this article in my blog about using DESC clause in a composite index: