From: Zardosht Kasheff Date: October 12 2012 1:29pm Subject: Re: purpose of KEY->rec_per_key List-Archive: http://lists.mysql.com/internals/38612 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 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 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