| Innodb Index cardinality keep change [message #1346] |
Fri, 01 June 2007 03:29  |
 jerry
Messages: 2
Registered: December 2006 |
Junior Member |
|
|
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)
|
|
|
| Re: Innodb Index cardinality keep change [message #1347 is a reply to message #1346 ] |
Fri, 01 June 2007 05:16  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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.
Martin Gallagher | Speeple: The latest news
|
|
|
Members
Search
Help
Register
Login
Home
