# Innodb Index cardinality keep change

**URL:** <https://forums.percona.com/t/innodb-index-cardinality-keep-change/340>\
**Category:** Other MySQL® Questions\
**Created:** [June 1, 2007, 1:59am UTC](https://forums.percona.com/t/innodb-index-cardinality-keep-change/340 "2007-06-01T01:59:41Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerry](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jerry](https://forums.percona.com/u/jerry)\
**Post date:** [June 1, 2007, 1:59am UTC](https://forums.percona.com/t/innodb-index-cardinality-keep-change/340/1 "2007-06-01T01:59:41Z")

</div>

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&#64;localhost](mailto:root&#64;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)

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [June 1, 2007, 3:46am UTC](https://forums.percona.com/t/innodb-index-cardinality-keep-change/340/2 "2007-06-01T03:46:53Z")

</div>

I think the problem is that the cardinality calculation takes into account the number of rows - and because the number of rows in innodb table types is only an estimation and is very variable this is relflected in the cardinality calculation.