On Thu, Jul 09, 2009 at 12:06:03PM -0400, Zardosht Kasheff wrote:
> Does anyone have any pointers on any functions to look at for a hint
> of where to look here?
> For the example query, select * from foo where a = 1 and b = 1, if we
> have a primary key of a, the optimizer knows that an intersect is not
> necessary. Where is this code so that I may try to extend that logic
> to clustering keys?
Do I understand it correctly (from reading
each key with HA_CLUSTERED_KEY flag has an invisble 'tail' which has all table
columns that were not explicitly specified in the key?
And the said 'tail' is not present in table->key_info[index_no].key_parts
array? (like it is for InnoDB's clustered primary key)
In that case, I see three possible solutions:
Solution1: Make the 'tail' be shown in table->key_info[index_no].key_parts.
Then the optimizer won't need to care about clustered keys having some
implicitly-present columns at the end.
Solution2: Make the range/index_merge optimizer be aware of the implicitly-
present 'tail' so that it uses tail members as regular key parts (one of
the things the range optimizer will need will be the order of elements in
#1 and #2 will require relatively big changes/reviews etc, although they will
provide complete solutions (the fact that the key is clustered will be used to
the fullest extent).
If you're not after the broadest solution and just need to make the optimizer
not to construct index_merge/interesect(..., clustered_key, ...), here is patch
that should do exactly that:
=== modified file 'sql/opt_range.cc'
--- sql/opt_range.cc 2009-03-24 13:58:52 +0000
+++ sql/opt_range.cc 2009-07-10 09:22:49 +0000
@@ -4535,8 +4535,16 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
for (idx= 0, cur_ror_scan= tree->ror_scans; idx < param->keys; idx++)
- if (!tree->ror_scans_map.is_set(idx))
+ Ignore non-ROR scans or 'clustered' (in Tokutek's sense) keys.
+ uint keyno= param->real_keynr[idx];
+ if (!tree->ror_scans_map.is_set(idx) ||
+ (keyno != cpk_no &&
+ param->table->key_info[keyno].flags & HA_HA_CLUSTERED_KEY))
if (!(scan= make_ror_scan(param, idx, tree->keys[idx])))
if (param->real_keynr[idx] == cpk_no)
(about the special treatment of clustered primary key in index_merge/intersect: it is
included in index_merge but it's not really a scan. We get columns of clustered primary
key from secondary key scans, and then use clustered primary key scan condition
only for filtering)
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org