List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:October 11 2011 12:22pm
Subject:Re: Index question
View as plain text  
> 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).
-- 
Rik Wasmus
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