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.