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
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
bool is_covering= table->covering_keys.is_set(nr) ||
(nr == table->s->primary_key &&
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.
On Sat, Jul 4, 2009 at 4:27 AM, Kristian
>> 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
> 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
> 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