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:
None 
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
Description:
For years we have been plagued with queries that suddenly become long running queries and have determined that the indices have lost their cardinality. We are at a loss to determine the cause for this, but the result is catastrophic to our product which relies on MySQL.  Is there EVER a normal situation where a table should have NULL cardinality for an index or is this ALWAYS an indication of an errant condition?

We are about to write code that will detect this condition (any index in a table having null cardinality) and will perform an ANALYZE on that table if the condition is found.

I would welcome your advice on this issue.

Our database is 100% Myisam.

How to repeat:
I do not have a clue how to reproduce this issue.
[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.