Google it! http://www.google.com/search?q=why+index+database+tables ...

yielded this nugget.

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Essentially performance for the single combined index vs two separate indices (not sure why they are referred to as indexes) is the same when you query using the first key or both keys.

The combined index is only effective if you don't plan on attempting to query using only the second key. Order of keys is important in a combined index.

If you sort or search on both fieldA and fieldB frequently, a single index on the both of them will work better than one index for each of them. Also, if you have an index which covers (A, B, C), that can also be used just like an index on (A, B) or an index on A alone, but not B alone or C alone.

If you sort or search on both fieldA and fieldB frequently, a single index on the both of them will work better than one index for each of them

I've found out the hard way that this isn't always true -- if you're using inequality matching on both columns, and they both vary greatly, then you're often better using two separate indexes.

In my particular case, I was testing for overlap of the time of a series of telescope images. (about 10M records). Because of the nature of the fields being searched on (start time and send time; essentially infinitely variable), the compound index was useless for the second field and was ignored. Two indexes gave better results, but depending on the size of the range being asked for, no indexes were better for excessively large matches.

(although, I got even better performance by using the knowledge that the end date was always within 1 minute of the start date, so isolating the range using just a single field's index, and then _not_ having an index on the second field for the second weeding.)

...

Anyway, the point was -- nothing's absolute when it comes to tuning. Use whatever benchmarking tools you have, test a few indexing strategies -- sometimes, you'll want to use hints or the equivalent to avoid indexes for some queries. And as nothing's absolute -- in many cases, you won't need to do this unless things are taking too long.

It was too much OT for 10 years ;)

;-)