From: Date: July 4 2009 4:11pm
Subject: Re: feedback/review requested for fix to MySQL bug #45458
List-Archive: http://lists.mysql.com/internals/37090
Message-Id: <2f9663ba0907040711y23d8fae2l2e8c219768dabf66@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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-index=
es/,
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=3D39653) 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=3D table->covering_keys.is_set(nr) ||
                            (nr =3D=3D 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 wa=
y 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 _a=
ll_
> 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.
>
> =A0- Kristian.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dzardosht@=
gmail.com
>
>