maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

Hi, Michael!

On May 19, Michael Widenius wrote:
> 
> >> +++ b/sql/sql_select.cc	2011-05-18 16:26:30 +0000
> >> @@ -16525,9 +16527,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
> >> */
> >> DBUG_ASSERT (ref_key != (int) nr);
> >> 
> >> -        bool is_covering= table->covering_keys.is_set(nr) ||
> >> -                          (nr == table->s->primary_key &&
> >> -                          table->file->primary_key_is_clustered());
> >> +        bool is_covering= (table->covering_keys.is_set(nr) ||
> >> +                           (table->file->index_flags(nr, 0, 1) &
> >> +                            HA_CLUSTERED_INDEX));
> 
> > See, that's what I said. It make sense to set covering_keys bitmap
> > for all clustered indexes once, when the table is opened, and then
> > you not only get this condition simpler, but probably will also
> > cover other cases where table->covering_keys is checked (or may be
> > checked in the future).
> 
> Looking at how covering keys is used in many places, I am not sure
> that change is straightforward or will not cause wrong timing
> results.  This is because in many cases we don't want to use a
> clustered key when we are trying to do index-only reads as the
> covering key is much slower than any other key.

Precisely, it is used in many places. So if HA_CLUSTERED_INDEX will
affect it, clustered keys will be taken into account more than now.

We usually want to *consider*  clustered keys for index-only reads.
Clustered keys aren't much slower. I mean, they are not "slower, because
they are clustered", they're "slower because they're long". But any long
key, not only clustering, will be slower than a short one. That is,
optimizer - when deciding on a keyread - needs to use the shortest
possible key, and generally take key length into account. This problem
has very little to do with clustering keys (although it manifests itself
on clustering keys).

> I agree that your idea has some merits, but I would not like to try to
> do it just now.  Something you can look at when you are ready with
> 5.5...

Agree, that's what I said on the phone.

Regards,
Sergei