From: Date: July 3 2009 3:22pm
Subject: Re: query optimization and multiple clustering keys
List-Archive: http://lists.mysql.com/internals/37087
Message-Id: <20090703132241.GA4595@janus.mylan>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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,
>    we have :            bool is_covering= table->covering_keys.is_set(nr) ||
>                           nr == table->s->primary_key &&
> 	                  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

  bool is_covering= table->covering_keys.is_set(nr) ||
                    table->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üßen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring