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
