GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Innodb Index cardinality keep change

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Innodb Index cardinality keep change

    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)

  • #2
    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.

    Comment

    Working...
    X