index cardinality null

[Ken Hall]

When I am creating a table, some of the indices cardinality is set to NULL. If I ANALYZE this table with no entries, there is no change. If I put one entry in the table, the cardinality is set appropriately. I have read that the cardinality for the primary key is set to be the row count however I have seen my primary key ignored when SHOW INDEX FROM tablename shows the cardinality of the primary key to be null.

So :
A. if the primary key is a two field index and one of the two shows NULL cardinality does that impact the use of that index or is the display from the SHOW INDEX misleading?

B. Since the cardinality impacts how the optimizer performs joins, should I perform periodic ANALYZE or should I allow the optimizer to figure out the best join and then set that with a STRAIGHT JOIN ?

C. When I create a table with multiple indicies, what triggers the calculation of the cardinality for non primary key indices?


For Example:
CREATE TABLE `fassmap` (
`FID` int(11) NOT NULL default '0',
`rowID` int(11) NOT NULL default '0',
`assetID` int(11) NOT NULL default '0',
PRIMARY KEY (`FID`,`rowID`,`assetID`),
KEY `fidAssetID` (`FID`,`assetID`),
KEY `assetID` (`assetID`),
KEY `rIDfID` (`rowID`,`FID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

With 0 entries I see:
mysql> show index from fassmap\G
*************************** 1. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: rowID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Column_name: assetID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 2
Column_name: assetID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: fassmap
Non_unique: 1
Key_name: assetID
Seq_in_index: 1
Column_name: assetID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 1
Column_name: rowID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 2
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
8 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------------------------

After 6 entries have been inserted:
mysql> show index from fassmap\G
*************************** 1. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: rowID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Column_name: assetID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 2
Column_name: assetID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: fassmap
Non_unique: 1
Key_name: assetID
Seq_in_index: 1
Column_name: assetID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 1
Column_name: rowID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 2
Column_name: FID
Collation: A
Cardinality: NULL <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
8 rows in set (0.00 sec)

---------------------------------------------------------------------------------------------------------
After performing ANALYZE:
mysql> ANALYZE TABLE fassmap;
+-----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| bridge1.fassmap | analyze | status | OK |
+-----------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from fassmap\G
*************************** 1. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: 2 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: rowID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: fassmap
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Column_name: assetID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 4. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 1
Column_name: FID
Collation: A
Cardinality: 2 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 5. row ***************************
Table: fassmap
Non_unique: 1
Key_name: fidAssetID
Seq_in_index: 2
Column_name: assetID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 6. row ***************************
Table: fassmap
Non_unique: 1
Key_name: assetID
Seq_in_index: 1
Column_name: assetID
Collation: A
Cardinality: 3 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 7. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 1
Column_name: rowID
Collation: A
Cardinality: 3 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 8. row ***************************
Table: fassmap
Non_unique: 1
Key_name: rIDfID
Seq_in_index: 2
Column_name: FID
Collation: A
Cardinality: 6 <<<<<<<<<<<<<<
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
8 rows in set (0.00 sec)

mysql> select version();
+---------------+
| version() |
+---------------+
| 4.1.10-nt-log |
+---------------+