List:General Discussion« Previous MessageNext Message »
From:Alex Schaft Date:October 11 2011 12:35pm
Subject:Re: Index question
View as plain text  
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]
Thread
Index questionAlex Schaft11 Oct
  • Re: Index questionJohan De Meersman11 Oct
    • Re: Index questionNeil Tompkins11 Oct
      • Re: Index questionRik Wasmus11 Oct
        • Re: Index questionAlex Schaft11 Oct
          • Re: Index questionAlex Schaft11 Oct
          • Re: Index questionRik Wasmus11 Oct
            • Re: Index questionNeil Tompkins11 Oct
              • Re: Index questionRik Wasmus11 Oct
                • Re: Index questionJohan De Meersman11 Oct
                  • Re: Index questionTompkins Neil11 Oct
                    • Re: Index questionJohan De Meersman12 Oct
  • Re: Index questionArthur Fuller11 Oct