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