MySQL Forums
Forum List  »  Merge Storage Engine

Cardinality is NULL on Merge Table's indexes
Posted by: Matt Leech
Date: January 26, 2011 04:45AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cardinality is NULL on Merge Table's indexes
7497
January 26, 2011 04:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.