List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:December 4 2006 9:36am
Subject:Why incomplete Cardinalities with MERGE tables ?
View as plain text  
I have some base tables, called data2004, data2005 etc.
They all have the following structure:
CREATE TABLE IF NOT EXISTS `data200X`
( F1 int unsigned NOT NULL default '0',
  F2 smallint unsigned NOT NULL default '0',
  F3 smallint unsigned NOT NULL default '0',
  F4 tinyint unsigned NOT NULL default '0',
  F5 tinyint unsigned NOT NULL default '0',
...
  PRIMARY KEY (F1,F2,F3,F4,F5),
  KEY F2 (F2), KEY F3 (F3), KEY F4 (F4)
) ENGINE=MyISAM;

For all these MyISAM tables SHOW INDEX gives ALL cardinalities, like:
KeyName  Column  Cardinality
Primary     F1                9837
Primary     F2            220333
Primary     F3          3892565
Primary     F4        11677695
Primary     F5        11677695
F2             F2                  24
F3             F3                241
F4             F4                  31

I defined a MERGE table with 3 base tables, like:
CREATE TABLE IF NOT EXISTS `data0406`
( F1 int unsigned NOT NULL default '0',
  F2 smallint unsigned NOT NULL default '0',
  F3 smallint unsigned NOT NULL default '0',
  F4 tinyint unsigned NOT NULL default '0',
  F5 tinyint unsigned NOT NULL default '0',
...
  KEY Combi (F1,F2,F3,F4,F5),
  KEY F2 (F2), KEY F3 (F3), KEY F4 (F4)
) ENGINE=MERGE UNION=(data2004, data2005, data2006);

For this MERGE table SHOW INDEX gives NOT ALL cardinalities:
KeyName  Column  Cardinality
Primary     F1               30143
Primary     F2             686726
Primary     F3         12589987
Primary     F4                 NULL   <<< absent
Primary     F5                 NULL   <<< absent
F2             F2                   75
F3             F3                 725
F4             F4                   96

When defining a MERGE table with 4 base tables, like:
ENGINE=MERGE UNION=(data2003, data2004, data2005, data2006);
the SHOW INDEX gives even less cardinalities:
KeyName Column Cardinality
Primary     F1               xxxxx
Primary     F2             xxxxxx
Primary     F3                 NULL   <<< absent
Primary     F4                 NULL   <<< absent
Primary     F5                 NULL   <<< absent
F2             F2                   xx
F3             F3                 xxx
F4             F4                   xx

Any ideas ? I am using MySQL 5.0.15 NT

TIA, Cor

Thread
Why incomplete Cardinalities with MERGE tables ?C.R.Vegelin4 Dec