Re: index on multiple columns vs multiple indexes on single column

cumin (jkilbourne_at_gmail.com) wrote:
: Could someone explain (or point out the relevant documentation about)
: situations in which you would want to index several columns vs making
: multiple indexes, one for each column? Also, I understood that the
: order of columns in a multi-column index was important, but I do not
: understand why.

Speaking genericly (since individual situations need to be examined individualy) and by example...

E.g. if you had an index on a name column, then the index might look like this

        NAME-INDEX

	alan
	fred
	hannah
	hannah
	ian
	nugent
	nugent
	peter
	sally
	sally
	zoro

You can see that because it is sorted then will be easy to find any particular name.

A similar, one column index on the age might look like this (age because it's easy to type)

        AGE-INDEX

	1
	4
	7
	9
	11
	23
	43
	44
	56
	67
	71

And here again, I think you can see that because it's sorted it would be easy to find any particular age in the list.

Now consider a two column index. It might look like this...

        NAME-AGE-INDEX

        alan        11
        fred        44
        hannah      4
        hannah      67
        ian         56
        nugent      23
        nugent      43
        peter       7
        sally       9
        sally       71
        zoro        1

You will notice that it would still be easy to find a name in this list because the names are sorted (just like the NAME-INDEX). _But_ this index won't help you find any particular age beause the ages are not sorted. I hope you can see that the order of the columns in the index makes a difference on what it helps to find. If the columns were reversed then the two column index would help to find the age (but not the name).

The ages in the above list are not sorted, HOWEVER, if you look at each name individually, then you can see that the age _is_ sorted within each name. THEREFORE, what I said is slightly wrong - the age can be found easily, but only when you already know which person to look at. In other words, if you want to find a particular person who is a particular age then suddenly the two column index is the best index. That is because it is easy to find each name, and then within each name it is easy to find each age (for that name).

How would you do that with the two indiviual indexes. You could find the name you want, or the age you want, but not the two together.

So in SQL terms, the two individual indexes would be best if you had the following queries

	select * from people where name = 'hannah';
	select * from people where age  = 23;

but the two individual indexes wouldn't help much if you had

        select * from people where name = 'hannah' and age > 7;

because only one of the indexes can be used. The name index can help find all the hannahs, but the age index doesn't know which ages are for hannah so it won't help at this stage - you're going to have to simply examine all the hannahs to find their ages.

On the other hand, the one double column index would be best if you had queries such as

	select * from people where name = 'hannah';
	select * from people where name = 'hannah' and age > 7;

but it wouldn't help if you had

        select * from people where age = 23;

--

This programmer available for rent.