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@mariadb.org -- Vote for my Percona Live 2016 talks: https://www.percona.com/live/data-performance-conference-2016/sessions/maria... https://www.percona.com/live/data-performance-conference-2016/sessions/maria...