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
