Bug #13132 Index cardinality not updated

• Submitted: 13 Sep 2005 9:31
• Modified: 13 Sep 2005 12:34
• Reporter: Lars Teuber
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20, 4.1.14, 4.1.7
• OS: Windows (Windows/Linux)
• CPU Architecture: Any

[13 Sep 2005 9:31] Lars Teuber

Description:
Index cardinality of a newly created MySql table will not be updated when being filled with data.

How to repeat:
Tested on Windows XP using Mysql 4.1.14 and 4.1.7
Tested on Linux using Mysql 4.0.20

CREATE TABLE `test` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`test` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `test` )
);

INSERT INTO `test` ( `id` , `test` )
VALUES (
'', '1'
);

SHOW INDEX FROM test;

Expected cardinality for index 'test' is 1, but the result is NULL. 
It's only fixed after an analyse/optimize/repair/check.

[13 Sep 2005 11:05] Valeriy Kravchuk

Sorry, but I was not able to repeat the behaviour you described on 4.1.14-nt:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.18 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `test` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    -> `test` INT UNSIGNED NOT NULL ,
    -> PRIMARY KEY ( `id` ) ,
    -> INDEX ( `test` )
    -> );
Query OK, 0 rows affected (0.84 sec)

mysql>
mysql> INSERT INTO `test` ( `id` , `test` )
    -> VALUES (
    -> '', '1'
    -> );
Query OK, 1 row affected, 1 warning (0.17 sec)

mysql>
mysql> SHOW INDEX FROM test;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| test  |          0 | PRIMARY  |            1 | id          | A         |
     1 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | test     |            1 | test        | A         |
     1 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
2 rows in set (0.14 sec)

Cardinality is 1, as expected. What storage engine is used, by the way? In my case it was InnoDB. 

When I tried with similar MyISAM table, the results were slightly different:

mysql> SHOW INDEX FROM test2;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| test2 |          0 | PRIMARY  |            1 | id          | A         |
     1 |     NULL | NULL   |      | BTREE      |         |
| test2 |          1 | test     |            1 | test        | A         |
  NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+

So, only primary key's cardinality is updated. Is this what you had seen in your test?

Please, send the results of "SHOW CREATE TABLE test" to be sure.

[13 Sep 2005 11:47] Lars Teuber

Hello Valeriy,

sorry that I did not mention the storage engine, it is MyISAM. 
Only primary key's cardinality is updated, not the cardinality of the other column.

Result of "SHOW CREATE TABLE test":

CREATE TABLE `test` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `test` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `test` (`test`)
) TYPE=MyISAM

Best regards
Lars.

[13 Sep 2005 12:34] Valeriy Kravchuk

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

For MyISAM tables this is a documented behaviour. See the details on Cardinality column in http://dev.mysql.com/doc/mysql/en/show-index.html