List:General Discussion« Previous MessageNext Message »
From:Alex Schaft Date:October 11 2011 12:30pm
Subject:Re: Index question
View as plain text  
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?

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