Details
Medium Description
Similar to https://jira.percona.com/browse/PS-4297, index cardinality is equal for all columns. In this case the table is partitioned and last partition is empty and using TokuDB.
How to repeat:
CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `d` date NOT NULL, PRIMARY KEY (`id`,`d`), KEY `a` (`a`), KEY `b` (`b`), KEY `c` (`c`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p20200427 VALUES LESS THAN (737908) ENGINE = TokuDB, PARTITION p20200428 VALUES LESS THAN (737909) ENGINE = TokuDB, PARTITION p20200429 VALUES LESS THAN (737910) ENGINE = TokuDB) */ Populate with some data: insert into tt values(null, rand()*1000, rand()*10000, "hello", "2020-04-28"); repeat until we have say 2049 rows: insert into tt select null,rand()*1000, rand()*10000, "hello", "2020-04-28" from tt;
This has inserted 2049 rows to the middle partition (p20200428). Now index statistics are wrong:
mysql [localhost:5728] {msandbox} (test) > show keys from tt;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tt | 0 | PRIMARY | 1 | id | A | 2050 | NULL | NULL | | BTREE | | |
| tt | 0 | PRIMARY | 2 | d | A | 2050 | NULL | NULL | | BTREE | | |
| tt | 1 | a | 1 | a | A | 2050 | NULL | NULL | YES | BTREE | | |
| tt | 1 | b | 1 | b | A | 2050 | NULL | NULL | YES | BTREE | | |
| tt | 1 | c | 1 | c | A | 2050 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
Using tokudb_analyze_time=0; and ANALYZE TABLE tt does not fix it like it did in PS-4297.
Neither does{{ ALTER TABLE tt ANALYZE PARTITION p20200428;}}
Dropping the last (empty) partition gets proper index statistics:
mysql [localhost:5728] {msandbox} (test) > alter table tt drop partition p20200429;
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql [localhost:5728] {msandbox} (test) > show keys from tt;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tt | 0 | PRIMARY | 1 | id | A | 2049 | NULL | NULL | | BTREE | | |
| tt | 0 | PRIMARY | 2 | d | A | 2049 | NULL | NULL | | BTREE | | |
| tt | 1 | a | 1 | a | A | 1025 | NULL | NULL | YES | BTREE | | |
| tt | 1 | b | 1 | b | A | 2049 | NULL | NULL | YES | BTREE | | |
| tt | 1 | c | 1 | c | A | 1 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.02 sec)
I believe biggest partition should be used, but last one is in fact being used.
