maria-developers team mailing list archive

[Axel Schwenke <axel@xxxxxxxxxxxx>]

Igor Babaev wrote:
> On 04/12/2013 09:00 AM, Axel Schwenke wrote:
>> Hi Igor, Timour,
>>
>> when I wrote the script to translate MariaDB table statistics to MySQL,
>> I found one problem. Imagine this simple table:
>>
>> create table t1 (c1 int, c2 int, c3 int,
>>                  primary key (c1, c2),
>>                  index `secondary`(c3));
>>
>> Here the secondary index has 3 parts, it can be used as (c3), (c3, c1)
>> and (c3, c1, c2). Obviously the avg_frequency is 1 for (c3, c1, c2), but
>> it is not obvious how it is for (c3, c1). Such data I'd expect to see in
>> the index_stats table under prefix_arity=2, but it's not there.
> 
> To get this you have to run analyze with
> set optimizer_switch='extended_keys=on';

Thanks Igor, this is a piece I was missing.

While I see the point to obey the extended_keys optimizer switch in ANALYZE
TABLE, I wonder if this is a good idea. The optimizer switch is dynamic, so
a user can change it, even for a single query. But when the statistics for
the extended key is not available, this makes not much sense.

I suggest to always collect extended keys statistics, regardless of the
setting of the extended_keys optimizer switch. Rationale: it is easy to
simply not use the data when extended_keys=off. The only counter argument
would be that this is costly - but I cannot see this.


XL