Re: purpose of KEY->rec_per_key

Hello Sergei,

Thanks for the reply. Which versions of MySQL and MariaDB will have
these persistent statistics?

-Zardosht

On Wed, Oct 10, 2012 at 4:02 PM, Sergei Golubchik <[email protected]> wrote:
> Hi, Zardosht!
>
> On Oct 10, Zardosht Kasheff wrote:
>> Thanks for the explanation, Sergei.
>>
>> For joins, what does the optimizer do if these values are not present?
>> Just guess?
>
> Yes. Like, "okay, let's assume that it'll match 1% of the rows". Or
> "we'll prefer a shorter key". Or something. There are different
> heuristics in different places in the code.
>
> Better to have this statistics calculated, even if it's a bit outdated.
>
> On the other hand, in new MySQL and MariaDB versions there are
> "persistent statistics" tables, where the statistics is stored in the
> mysql database, and the engine is not required to maintain it anymore.
> So, you can simply rely on the "persistent statistics" feature and don't
> bother about rec_per_key at all.
>
>> > These values are used by the optimizer to prefer an index with the
>> > best selectivity. E.g, in SELECT ... WHERE a=5 AND b=6, if the
>> > optimizer has to choose between an index on 'a', with rec_per_key=2,
>> > and an index on 'b' with rec_per_key=20, it would prefer an index on
>> > 'a'. Because it would expect that a=5 will match only 2 rows, while
>> > b=6 will match 20 rows.
>
> Regards,
> Sergei

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals