List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:July 16 2009 5:34pm
Subject:RE: help with index_merge and clustering keys
View as plain text  
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
Thread
help with index_merge and clustering keysZardosht Kasheff23 Jun
  • Re: help with index_merge and clustering keysZardosht Kasheff9 Jul
    • Re: help with index_merge and clustering keysSergey Petrunya10 Jul
      • Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
        • RE: help with index_merge and clustering keysRick James10 Jul
          • RE: help with index_merge and clustering keysMichael Widenius16 Jul
            • Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
            • RE: help with index_merge and clustering keysRick James16 Jul
              • RE: help with index_merge and clustering keysMichael Widenius19 Jul
        • Re: help with index_merge and clustering keysSergey Petrunya12 Jul
          • Re: help with index_merge and clustering keysZardosht Kasheff12 Jul
            • Re: help with index_merge and clustering keysSergey Petrunya15 Jul
              • RE: help with index_merge and clustering keysRick James15 Jul
              • Re: help with index_merge and clustering keysZardosht Kasheff24 Jul
      • Re: help with index_merge and clustering keysMichael Widenius16 Jul
Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
  • RE: help with index_merge and clustering keysRick James16 Jul
    • Re: help with index_merge and clustering keysZardosht Kasheff16 Jul