jon_neve schrieb am 30.08.2012 um 09:34 (-0000):

> I am wondering, is there a difference in performance between creating
> a multi-segment index (on two fields for example), and creating two
> separate indices (for the same two fields)?

I'd expect the multi-column index to perform better because there's less
processing for the engine to do: just one index to consult instead of
two; no merge of results for the two indices required. I'd verify that
expectation by testing for the specific data I have.

> The obvious drawback of the double-segment index being that it's less
> versatile, as it can only be used in queries where both fields are
> used as search criteria...

Not quite. Multi-column indices can be used whenever you can supply
some leftmost set of columns; so for an INDEX(A,B,C) it can be used
for (A,B,C), for (A,B) and for just (A); it is useless when you can't
supply (A) for the query. Whether a given leftmost part is good enough
is another question.
--
Michael Ludwig