maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, dE!

On Jan 02, dE wrote:
> Hi!
>      I was playing around with indexes after we faced problems with 
> mariadb choosing the wrong index because of high average value group size.
> 
> So I created a table with 4 text columns and indexed the 4th column with 
> a prefix length of 1.
> 
> Following are the stats in brief --
> 
> The index has 35 unique values
> There are around 1040000 rows in total.
> this will give an average value group size of 29714
> Cardinality is calculated as <no. of records in the index>/<average size 
> of value groups>, thus it's value must be 35
> 
> BUT from show indexes, it's value is shown as 72 
> (innodb_stats_persistent_sample_pages = 9999999).

It's because InnoDB lies about the cardinality. It reports twice the
actual value to the server. If MyISAM would report the cardinality as
36, InnoDB will say it's 72.

You can enable the Engine Independent Table Statistics in MariaDB:
https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/
This will show the correct value.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx
-- 
Vote for my Percona Live 2016 talks:
https://www.percona.com/live/data-performance-conference-2016/sessions/mariadb-connectors-fast-and-smart-new-protocol-optimizations#community-voting
https://www.percona.com/live/data-performance-conference-2016/sessions/mariadb-101-security-validation-authentication-encryption#community-voting