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
