From: Rik Wasmus
Date: October 11 2011 12:22pm
Subject: Re: Index question
List-Archive: http://lists.mysql.com/mysql/226006
Message-Id: <201110111422.49223.rik@grib.nl>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit

> 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