Description:
The MySql does not maintain cardinality of the table after few transactions. Also, the cardinality becomes Null because of unknown reason and this hampers the query performance as wrong/no indexes are used afterward. I have noticed this behaviour even after performing checks like, myisamchk --analyze, analyze table and check table commands.
Please look at the text captured while execution:
*************************************************
mysql> show table status like 'rCamBr%';
+---------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+---------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| rCamBro | MyISAM | Dynamic | 561 | 50 | 28424 | 4294967295 | 20480 | 0 | NULL | 2003-12-02 15:20:24 | 2003-12-03 18:53:49 | 2003-12-03 18:53:49 | | |
| rCamBrv | MyISAM | Dynamic | 578 | 57 | 33440 | 4294967295 | 19456 | 0 | NULL | 2003-12-02 15:20:24 | 2003-12-03 18:53:49 | 2003-12-03 18:53:49 | | |
+---------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
2 rows in set (0.01 sec)
mysql> analyze table rCamBro;
+---------------------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+-----------------------------+
| arundOAS561.rCamBro | analyze | status | Table is already up to date |
+---------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | NULL | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | NULL | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | NULL | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 80 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> check table rCamBro;
+---------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| arundOAS561.rCamBro | check | status | OK |
+---------------------+-------+----------+----------+
1 row in set (0.01 sec)
mysql>
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 7 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 140 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 561 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 80 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> insert into rCamBro select * from Oas512.rCamBro;
Query OK, 561 rows affected (0.45 sec)
Records: 561 Duplicates: 0 Warnings: 0
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 14 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 280 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 1122 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 160 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> analyze table rCamBro;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| arundOAS561.rCamBro | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 7 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 124 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 561 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 86 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> check table rCamBro;
+---------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| arundOAS561.rCamBro | check | status | OK |
+---------------------+-------+----------+----------+
1 row in set (0.01 sec)
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 7 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 124 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 561 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 86 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> insert into rCamBro select * from Oas512.rCamBro;
Query OK, 561 rows affected (0.45 sec)
Records: 561 Duplicates: 0 Warnings: 0
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 10 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 187 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 841 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 129 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> analyze table rCamBro;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| arundOAS561.rCamBro | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 7 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 129 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 561 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 84 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> insert into rCamBro select * from Oas512.rCamBro;
Query OK, 561 rows affected (0.46 sec)
Records: 561 Duplicates: 0 Warnings: 0
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 9 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 172 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 748 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 112 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql>
mysql> analyze table rCamBro;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| arundOAS561.rCamBro | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show index from rCamBro;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| rCamBro | 1 | I0 | 1 | Account | A | 6 | NULL | NULL | |
| rCamBro | 1 | I0 | 2 | RmWhen | A | 124 | NULL | NULL | |
| rCamBro | 1 | I0 | 3 | Campaign | A | 561 | NULL | NULL | |
| rCamBro | 1 | I1 | 1 | RmWhen | A | 86 | NULL | NULL | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
4 rows in set (0.00 sec)
How to repeat:
The steps mentioned in the bug description can be generalized and the behaviour is more or less same for any significant sizes table.
