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