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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
