MySQL Bugs: #54031: cardinality issues after enabling keys,wrong cardinality after analyze table

Bug #54031	cardinality issues after enabling keys,wrong cardinality after analyze table
Submitted:	27 May 2010 13:04	Modified:	6 Sep 2010 10:24
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.91, 5.1.47, 5.1.48-bzr	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
not related to !bug #34488 afaik.

after a multipart index is enabled with 'alter table .. enable keys' only the last key segment's distribution is stored.  the first segments of the key are null.

secondly, after analyze table or check table, the middle segment cardinality is still incorrect.

<inserts>
mysql> alter table t1 enable keys;
Query OK, 0 rows affected (0.00 sec)
mysql> show indexes from t1;

+----------+--------------+-------------+-------------+-
| Key_name | Seq_in_index | Column_name | Cardinality | 
+----------+--------------+-------------+-------------+-
| PRIMARY  |            1 | a           |        NULL | 
| PRIMARY  |            2 | b           |        NULL | 
| PRIMARY  |            3 | c           |           6 | 
| d        |            1 | d           |           6 | 
| e        |            1 | e           |           6 | 
+----------+--------------+-------------+-------------+-

cardinality of a,b,c should be 2, 3, 3 respectively.

How to repeat:
drop table if exists t1;
create table t1(a int,b int,c int,d int,e int,
primary key(a,b,c),key(d),key(e))engine=myisam;

alter table t1 disable keys;
insert into t1 values (1,1,1,1,1),(1,3,2,2,2),(1,2,3,3,3);
insert into t1 values (3,2,1,4,4),(1,2,2,5,5),(3,2,3,6,6);
alter table t1 enable keys;

show indexes from t1;
insert into t1 values (2,2,3,7,7),(1,1,3,8,8),(1,3,3,9,9);
show indexes from t1;
analyze table t1;
show indexes from t1;

select count(distinct(a)) a, count(distinct(b)) b, count(distinct(c)) c,
count(distinct(d)) d, count(distinct(e)) e from t1;

Thank you for the problem report. Verified with 5.1.48 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.48-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(a int,b int,c int,d int,e int,
    -> primary key(a,b,c),key(d),key(e))engine=myisam;
Query OK, 0 rows affected (0.40 sec)

mysql> 
mysql> alter table t1 disable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,1,1,1,1),(1,3,2,2,2),(1,2,3,3,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (3,2,1,4,4),(1,2,2,5,5),(3,2,3,6,6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t1 enable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | a           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            2 | b           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            3 | c           | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | d        |            1 | d           | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | e        |            1 | e           | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> insert into t1 values (2,2,3,7,7),(1,1,3,8,8),(1,3,3,9,9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | a           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            2 | b           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            3 | c           | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | d        |            1 | d           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | e        |            1 | e           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | a           | A         |           3 |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            2 | b           | A         |           4 |     NULL | NULL   |      | BTREE      |         |
| t1    |          0 | PRIMARY  |            3 | c           | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | d        |            1 | d           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | e        |            1 | e           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> 
mysql> select count(distinct(a)) a, count(distinct(b)) b, count(distinct(c)) c,
    -> count(distinct(d)) d, count(distinct(e)) e from t1;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 3 | 3 | 3 | 9 | 9 |
+---+---+---+---+---+
1 row in set (0.04 sec)

Is this really a bug? The manual says

"
Cardinality

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
"

It does say 'estimate'. And MyISAM is know for not having exact index distribution statistics.

I'm closing this one as 'not a bug' as:

1. disable and enable key commands have nothing to do with primary key cardinality counting, thus there are NULLs;
2. cardinality values in the b/c column of the multi-column index are cardinality values of key(a,b)/(a,b,c) respectively, and they are correct.