On 2011/10/11 02:22 PM, Rik Wasmus wrote:
>> Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
> They DO.
>
> See it as lookup table which starts with 'a' in the first case, and 'b' in the
> second one. Looking for anything that matches 'b' for an index (a,b) requires
> a full scan as you don't know 'a', likewise searching for 'a' in an index
> (b,a) requires a full scan. See it as looking through a phonebook trying to
> locate someone by first- rather then lastname. It's in there, just not easily
> accessible.
>
> However, if you have an index on (a,b) and DO know which 'a' you want
> ('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
> only an index on (a).
>
> Johan was trying to explain this distinction:
>
> - index (a,b) is good for searches on ONLY a or BOTH a& b, but bad for ONLY
> b
> - index (b,a) is good for searches on ONLY b or BOTH a& b, but bad for ONLY
> a
> - index (a)& index (b) is good for searches on ONLY b or ONLY a, and is
> suboptimal for searching for BOTH a,b (although, faster then no index, but the
> query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two
queries, one for a and one for b. If you then get a list of unique id's
of both, would it be faster to create an intersection yourself rather
than have the server do the legwork?
