> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
