From: Zardosht Kasheff
Date: July 3 2009 1:57pm
Subject: Re: query optimization and multiple clustering keys
List-Archive: http://lists.mysql.com/internals/37077
Message-Id: <2f9663ba0907030657l52fc6199i8bbadb8914793ed8@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello Sergei,

Thank you for your reply.

My strategy for answering this question was to look for all places
where the optimizer checks if the primary key is clustered, and see if
a generalization needs to be done for all keys. I am still working on
that part, and maybe the strategy of replacing
"primary_key_is_clustered" with table->file->index_flags(nr) &
HA_CLUSTERED_KEY would help solve the problems.

What concerns me is if there are places where there is the assumption
that there is ONLY ONE clustered key. I think this may be what is
going on with the code that determines to use an index_merge (I sent
an email about this on June 23rd to internals).

-Zardosht

On Fri, Jul 3, 2009 at 9:22 AM, Sergei Golubchik<serg@stripped> wrote:
> Hi, Zardosht!
>
> On Jun 18, Zardosht Kasheff wrote:
>>
>> What I am trying to figure out is what changes need to be done to the
>> query optimizer to properly support this feature.
> ...
>> Here is my failed attempt at answer the question:
>> I looked at places where handler::primary_key_is_clustered is called.
>> I figured that if there are locations where the optimizer needs to
>> know if the primary key is clustered, they may also be places where
>> the optimizer needs to know if another key is clustered. I only saw
>> one potential location where this might be necessary:
>>
>> in sql_select.cc, in function test_if_skip_sort_order,
>> =A0 =A0we have : =A0 =A0 =A0 =A0 =A0 =A0bool is_covering=3D table->cover=
ing_keys.is_set(nr) ||
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 nr =3D=3D table->s->=
primary_key &&
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 table->file->primary_key=
_is_clustered();
>>
>> I figure that this will also need to take into account if the key is
>> clustered, correct?
>
> Yes. I'd generalize the above as
>
> =A0bool is_covering=3D table->covering_keys.is_set(nr) ||
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0table->file->index_flags(nr) & HA_=
CLUSTERED_KEY;
>
> alternatively, we could simply initialize table->covering_keys bitmap to
> include all clustered keys. This should be even better, I suppose - it
> will cover more code paths in the optimizer, not only
> test_if_skip_sort_order().
>
>> So, in conclusion, given everything above, I have one general question
>> and one specific question.
>> The general question: can anyone think of any other place where the
>> optimizer needs to be tweaked to be aware of clustered keys?
>
> I think yes.
> In partitioning, in mrr (DsMrr_impl::choose_mrr_impl), in many places in
> the range optimizer.
>
> Simple grep for the word 'clustered' returns lots of matches :(
>
>> The specific question: does any tweaking need to happen in
>> test_if_skip_sort_order.
>
> Yes, see above.
>
> Regards / Mit vielen Gr=FC=DFen,
> Sergei
>
> --
> =A0 __ =A0___ =A0 =A0 ___ ____ =A0__
> =A0/ =A0|/ =A0/_ __/ __/ __ \/ / =A0 Sergei Golubchik <serg@stripped>
> =A0/ /|_/ / // /\ \/ /_/ / /__ =A0Principal Software Engineer/Server Arch=
itect
> /_/ =A0/_/\_, /___/\___\_\___/ =A0Sun Microsystems GmbH, HRB M=FCnchen 16=
1028
> =A0 =A0 =A0 <___/ =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sonnenallee 1, 85551=
 Kirchheim-Heimstetten
> Gesch=E4ftsf=FChrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
> Vorsitzender des Aufsichtsrates: Martin H=E4ring
>