On 2011/10/11 02:30 PM, Alex Schaft wrote:
> 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?
>
>
>
Then there's index merge optimizations too I suppose
Attachment: [text/html]
Attachment: [text/html]
