Bug #5304 Cluster: index cardinality isn't the same as with MyISAM/InnoDB

• Submitted: 30 Aug 2004 22:53
• Modified: 1 Oct 2004 13:30
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.4-gamma (ndb)
• OS: Linux (SuSE 9.1)
• Assigned to: Martin Skold
• CPU Architecture: Any

[30 Aug 2004 22:53] Trudy Pelzer

Description:
The cardinality of a primary key index for two tables with the same data
and the same table definition, except that one is a MyISAM table and the
other is a Cluster table, should be the same. But, assuming table s (MyISAM)
and table s_n (Cluster) each have 5 distinct rows, the cardinality for table s
index is 5, while table s_n index is NULL.

mysql> show index from s;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| s     |          0 | PRIMARY  |            1 | sno         | A         
|           5 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> show index from s_n;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| s_n   |          0 | PRIMARY  |            1 | sno         | NULL      
|        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

How to repeat:
create table tmyisam (col1 int not null primary key);
insert into tmyisam values (10);
insert into tmyisam values (20);
create table tndb (col1 int not null primary key) engine=ndbcluster;
insert into tndb values (10);
insert into tndb values (20);
show index from tmyisam;
show index from tndb;

[31 Aug 2004 5:26] MySQL Verification Team

Thank you for the bug report:

mysql> show index from tmyisam\G
*************************** 1. row ***************************
       Table: tmyisam
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: col1
   Collation: A
 Cardinality: 2
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)

mysql> show index from tndb\G
*************************** 1. row ***************************
       Table: tndb
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: col1
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)

[1 Oct 2004 13:29] Martin Skold

Index cardinality for primary key is fixed to be the number of rows.
Cardinality for other indexes is an estimation and different storage
engines estimate differently, with the aim of getting good query plans. 
This will have to be investigated further for ndbcluster.