I have one merge table comprised of two child tables. The index cardinality on the child tables is correct but the cardinality for most of the indexes on the merge table report NULL.
I have run analyze table on the child tables (can't run analyze on the merge table, "The storage engine for the table doesn't support analyze") and dropped/recreated all the indexes on the merge and child tables, but the cardinality on the merge table stubbornly remains NULL.
MySQL version 5.1.48.
These are my tables:
(I can't find a quick and easy way to format the data below so that it's easy to read, so here it is in an HTML table)
http://mattleech.com/bkwk.html
Merge table
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Merge_Table 0 idx_Delivery_Item_Plnt 1 Delivery A NULL BTREE
Merge_Table 0 idx_Delivery_Item_Plnt 2 Item A NULL BTREE
Merge_Table 0 idx_Delivery_Item_Plnt 3 Plnt A NULL BTREE
Merge_Table 1 idx_SalesDoc 1 SalesDoc A 21367368 BTREE
Merge_Table 1 idx_Createdon 1 Createdon A 5486 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 1 Route A 947 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 2 Createdon A 161874 BTREE
Merge_Table 1 idx_Route_Createdon_DlvTy 3 DlvTy A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A NULL BTREE
Merge_Table 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A NULL BTREE
Merge_Table 1 idx_Material 1 Material A NULL BTREE
Child table 1
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Child_Table_1 0 idx_Delivery_Item_Plnt 1 Delivery A 9432348 BTREE
Child_Table_1 0 idx_Delivery_Item_Plnt 2 Item A 9432348 BTREE
Child_Table_1 0 idx_Delivery_Item_Plnt 3 Plnt A 9432348 BTREE
Child_Table_1 1 idx_SalesDoc 1 SalesDoc A 4716174 BTREE
Child_Table_1 1 idx_Createdon 1 Createdon A 53899 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 1 Route A 53899 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 2 Createdon A 65960 BTREE
Child_Table_1 1 idx_Route_Createdon_DlvTy 3 DlvTy A 66896 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A 6642 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A 314411 BTREE
Child_Table_1 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A 377293 BTREE
Child_Table_1 1 idx_Material 1 Material A 68350 BTREE
Child table 2
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type
Child_Table_2 0 idx_Delivery_Item_Plnt 1 Delivery A NULL BTREE
Child_Table_2 0 idx_Delivery_Item_Plnt 2 Item A NULL BTREE
Child_Table_2 0 idx_Delivery_Item_Plnt 3 Plnt A NULL BTREE
Child_Table_2 1 idx_SalesDoc 1 SalesDoc A 16651194 BTREE
Child_Table_2 1 idx_Createdon 1 Createdon A 2161 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 1 Route A 370 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 2 Createdon A 86275 BTREE
Child_Table_2 1 idx_Route_Createdon_DlvTy 3 DlvTy A 92250 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 1 Soldtopt A 15980 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 2 Createdon A 1585828 BTREE
Child_Table_2 1 idx_Soldtopt_Createdon_ShipToPt 3 ShipToPt A 1850132 BTREE
Child_Table_2 1 idx_Material 1 Material A 42101 BTREE
Edited 2 time(s). Last edit at 01/26/2011 05:05AM by Matt Leech.