Bug #40983 | null cardinality | ||
---|---|---|---|
Submitted: | 24 Nov 2008 15:55 | Modified: | 16 Dec 2008 18:59 |
Reporter: | Ken Hall | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: General | Severity: | S4 (Feature request) |
Version: | 5.0.54a-enterprise-gpl-nt | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | null cardinality |
[24 Nov 2008 15:55]
Ken Hall
[24 Nov 2008 19:08]
Ken Hall
mysql> show index from vav2; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | vav2 | 0 | vectorAssetValueIDX | 1 | vector_id | A | NULL | NULL | NULL | | BTREE | | | vav2 | 0 | vectorAssetValueIDX | 2 | asset_id | A | NULL | NULL | NULL | | BTREE | | | vav2 | 0 | vectorAssetValueIDX | 3 | value_id | A | 160827179 | NULL | NULL | | BTREE | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.02 sec) mysql> show create table vav1; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------- | vav1 | CREATE TABLE `vav1` ( `vector_id` int(11) NOT NULL default '0', `asset_id` bigint(11) unsigned NOT NULL default '0', `value_id` bigint(11) unsigned NOT NULL default '0', `asset_type` int(11) NOT NULL default '0', UNIQUE KEY `vectorAssetValueIDX` (`vector_id`,`asset_id`,`value_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 COMMENT='maps per vector_id asset_id and value_id' | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------- How is it that two out of three columns within a single composite index can have null while the third has a value? Following an ANALYZE on the table, all three have values, and the one that had a value before did not change (such that maybe it was correct and did not need to change).
[16 Dec 2008 18:59]
Sveta Smirnova
Thank you for the report. Yes, MyISAM does not collect cardinality data without running ANALYZE. So strictly say this is not a bug. But there is feature request already to add this possibility: bug #34467, so I mark this one as duplicate and add a link there.