List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:July 9 2009 4:06pm
Subject:Re: help with index_merge and clustering keys
View as plain text  
Hello,

Does anyone have any pointers on any functions to look at for a hint
of where to look here?

For the example query, select * from foo where a = 1 and b = 1, if we
have a primary key of a, the optimizer knows that an intersect is not
necessary. Where is this code so that I may try to extend that logic
to clustering keys?

Thanks
-Zardosht

On Tue, Jun 23, 2009 at 2:10 PM, Zardosht Kasheff<zardosht@stripped> wrote:
> Hello,
>
> Our storage engine, TokuDB, allows multiple keys to be clustering
> (include all columns in the index). For context, an short explanation
> of the feature is here:
> http://blogs.tokutek.com/tokuview/introducing_multiple_clustering_indexes
>
> To get the optimizer to properly know when to use a clustering key, we
> reimplemented handler::read_time to return a better estimate in the
> case of clustering keys (similar to what InnoDB does in
> ha_innobase::read_time for the primary key). This helps the optimizer
> in most cases.
>
> The problem I have now run into is getting index_merge to work
> properly. Because MySQL does not know that an arbitrary key can be
> clustering, it will proceed to use it like a normal key, and this can
> lead to a sub-optimal query plan.
>
> Suppose we have the following query:
> select * from foo where a=1 and b=1;
>
> If we have a normal secondary key on "a" and a key on "b", then MySQL
> gets the values from the keys, does an intersect, and then uses
> handler::rnd_pos to retrieve the entire row. This is the right thing
> to do here.
>
> Unfortunately, at the moment, if we have clustering keys on "a" and
> "b", it does the same thing. This is not the ideal behavior. We want
> the optimizer to select either the key on "a" or "b" (suppose we
> select "a"), retrieve all of the rows where "a = 1", and from this
> result set, filter out the rows where "b != 1". This is what the
> optimizer would do if "a" were the primary key.
>
> Does anyone have any idea how one could get this to properly?
>
> Thanks
> -Zardosht
>
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