Hi!
>>>>> "Rick" == Rick James <rjames@stripped> writes:
Rick> Let's look at "select * from foo where a=1 and b = 1" in multiple contexts:
Rick> (I am assuming there an index containing both a and b.)
Rick> * Neither a nor b is clustered: index_merge could be the most efficient way to
> perform the query... Find all PKs for a=1, find all PKs for b=1, take the intersection;
> then fetch the rows.
Rick> * One of a or b is clustered: As mentioned, use the clustered key only. The
> other key buys nothing.
This is true for the given query, but not in general.
Assume you have in an InnoDB table and an index on (b,c) and 'a' is
the primary key. In this case:
select c from foo where a=1 and b = 1
I't better to scan the index (b,c) than scan the primary key as you
get all key parts from this index, but it's much less to scan as you
don't have to access all the other columns from the table.
Rick> * Both a and b are clustered: Again, the other key is useless; simply use the
> clustered key.
How can you have something clustered on to different key?
(Normally you can only cluster on one key at a time)
Regards,
Monty
