I noticed a very strange problem with innodb. Using ‘show index from xyz’ to check cardinality, we noticed the cardinality keep change. The table is not written to at the time. I cannot explain it other than treat it as a bug. The server has been up for 83+ days. Below are the background info. Please let me know if you see the same problem and/or know the cause.
Thanks.
Server:
mysql> \s
mysql Ver 14.7 Distrib 4.1.18, for pc-linux-gnu (i686) using readline 4.3
Connection id: 1505933
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: 4.1.18-standard-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 83 days 4 hours 13 min 2 sec
mysql> show create table x\G
*************************** 1. row ***************************
Table: x
Create Table: CREATE TABLE x (
id int(10) unsigned NOT NULL auto_increment,
type varchar(255) default NULL,
ref_id bigint(20) default NULL,
vf_voicesession_id bigint(20) NOT NULL default ‘0’,
PRIMARY KEY (id),
KEY vf_voicesession_id (vf_voicesession_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select count() from x;
±---------+
| count() |
±---------+
| 5858 |
±---------+
mysql> show index from x; (the cardinality changes among 5689, 5477, 6069). It is in the range of the the number of rows but it keep changing.
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| x | 0 | PRIMARY | 1 | id | A | 5689 | NULL | NULL | | BTREE | |
| x | 1 | vf_voicesession_id | 1 | vf_voicesession_id | A | 5689 | NULL | NULL | | BTREE | |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
2 rows in set (0.00 sec)
mysql> show index from x;
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| x | 0 | PRIMARY | 1 | id | A | 6069 | NULL | NULL | | BTREE | |
| x | 1 | vf_voicesession_id | 1 | vf_voicesession_id | A | 6069 | NULL | NULL | | BTREE | |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
2 rows in set (0.00 sec)
mysql> show index from x;
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| x | 0 | PRIMARY | 1 | id | A | 5477 | NULL | NULL | | BTREE | |
| x | 1 | vf_voicesession_id | 1 | vf_voicesession_id | A | 5477 | NULL | NULL | | BTREE | |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
2 rows in set (0.00 sec)
mysql> show index from x;
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
| x | 0 | PRIMARY | 1 | id | A | 6069 | NULL | NULL | | BTREE | |
| x | 1 | vf_voicesession_id | 1 | vf_voicesession_id | A | 6069 | NULL | NULL | | BTREE | |
±------±-----------±-------------------±-------------±- ------------------±----------±------------±---------±— ----±-----±-----------±--------+
2 rows in set (0.00 sec)