List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:July 4 2009 2:11pm
Subject:Re: feedback/review requested for fix to MySQL bug #45458
View as plain text  
Kristian,

You are correct in what you state. Your point about the obvious
generalization is a good one that has also been brought up by Baron
Schwartz here, http://www.xaprb.com/blog/2009/06/07/extended-covering-indexes/,
and he called it "extended covering indexes". I want to get this
extended covering indexes into MySQL. I have not had the time or
resources to really dig into the problem yet.

However, I think the specialized feature of clustering keys has the
following advantages:

1) Even if the user has extended covering indexes, users will want to
use clustering indexes as a shortcut, instead of defining a key with
all of the columns. Imagine having 50 columns in a table, and wanting
4 clustering keys. Writing out 4 indexes with 50 columns, each in a
different order, can be cumbersome.
2) This feature is a good first step towards implementing extended
covering indexes. I imagine there will be places where the optimizer
will need tweaking. MySQL bug #39653,
(http://bugs.mysql.com/bug.php?id=39653) is an example. This line of
code in sql_select.cc, function test_if_skip_sort_order, will need to
be modified:
          bool is_covering= table->covering_keys.is_set(nr) ||
                            (nr == table->s->primary_key &&
                        table->file->primary_key_is_clustered())
I believe the clustering keys feature can be used to iron out little
kinks needed to implement extended covering indexes.

So, while adding grammar for extended covering indexes is a feature
that will hopefully be done some day, I believe this grammar change
still has value.

Thanks
-Zardosht


On Sat, Jul 4, 2009 at 4:27 AM, Kristian
Nielsen<knielsen@stripped> wrote:
>
>> On Jun 25, Zardosht Kasheff wrote:
>>>
>>> This is a feature request that adds grammar for "clustering" indexes.
>>> Users can define an index to be clustering (include all of the columns
>>> in the index), and as a result, a flag is passed into the handler via
>>> a flag. It is up to the storage engine to properly implement it.
>>> Storage engines that choose to not implement it can simply ignore the
>>> flag.
>
> If I remember correctly, this enables index from which one can read all
> columns, but only do random lookups on a subset of columns. So another way to
> view them is as a restricted (but more efficient in some cases) covering
> index.
>
> There is an obvious generalisation of this idea, by not requiring that _all_
> columns can be read from the index. So we could have an index that allows
> reading (A1, A2, A3, ..., An) but only do random lookups on
> (A1, A2, ..., Am), for arbitrary m < n.
>
> This is much like a prefix index, but with the prefix being on the number of
> columns indexed, rather than on the number of characters indexed.
>
> I think if we were to extend the syntax in a general way for all storage
> engines, the extended syntax should allow the full generality.
>
>  - Kristian.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>
>
Thread
feedback/review requested for fix to MySQL bug #45458Zardosht Kasheff25 Jun
  • re: feedback/review requested for fix to MySQL bug #45458Michael Widenius28 Jun
    • Re: feedback/review requested for fix to MySQL bug #45458Zardosht Kasheff28 Jun
    • Re: feedback/review requested for fix to MySQL bug #45458Brian Aker28 Jun
    • Re: [Maria-developers] feedback/review requested for fix to MySQLbug #45458Sergei Golubchik4 Jul
  • Re: feedback/review requested for fix to MySQL bug #45458Sergei Golubchik4 Jul
    • Re: feedback/review requested for fix to MySQL bug #45458Kristian Nielsen4 Jul
      • Re: feedback/review requested for fix to MySQL bug #45458Zardosht Kasheff4 Jul
Re: feedback/review requested for fix to MySQL bug #45458Zardosht Kasheff3 Jul