Cardinality and partions
Hi,
We are working on implementing a new application that uses the table described below. When importing data into the table the cardinality seems to be wrong when using partitions, creating the same table omiting partitions gives a more correct number in cardinality.
When using MyISAM it gives NULL in cardinality and using InnoDB cardinality is the number of rows in table for all indexes. I have tried check table and analyze table with no succsess.
We do use partitions in other tables and in thoose the cardinality is correct, so i am a bit confused why it does not work for this table.
This is the table structure:
CREATE TABLE IF NOT EXISTS `iss_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b_num` varchar(6) NOT NULL,
`car_num` varchar(19) NOT NULL,
`xcar_num` blob,
`pro_cod1_2` varchar(2) DEFAULT NULL,
`pro_cod3_4` varchar(2) DEFAULT NULL,
`pro_cod5_6` varchar(2) DEFAULT NULL,
`tr_am` decimal(10,2) DEFAULT NULL,
`dat_tim` varchar(12) DEFAULT NULL,
`dat` int(11) DEFAULT NULL,
`tim` varchar(6) DEFAULT NULL,
`tim_sta` bigint(12) unsigned DEFAULT NULL,
`exp_dat` varchar(4) DEFAULT NULL,
`pos_cod` varchar(12) DEFAULT NULL,
`fun_cod` varchar(3) DEFAULT NULL,
`mc` varchar(4) DEFAULT NULL,
`acq_ins` varchar(11) DEFAULT NULL,
`ret_ref` varchar(12) DEFAULT NULL,
`auk_num` varchar(6) DEFAULT NULL,
`res_cod` varchar(3) DEFAULT NULL,
`ter_id` varchar(16) DEFAULT NULL,
`me_id` varchar(15) DEFAULT NULL,
`me_nam` varchar(22) DEFAULT NULL,
`me_adr` varchar(13) DEFAULT NULL,
`me_cit` varchar(15) DEFAULT NULL,
`me_zip` varchar(10) DEFAULT NULL,
`me_reg` varchar(3) DEFAULT NULL,
`me_cou` varchar(3) DEFAULT NULL,
`cur_cod` varchar(3) DEFAULT NULL,
`cur_rec` varchar(3) DEFAULT NULL,
`cur_aut` varchar(3) DEFAULT NULL,
`p_e_m` varchar(2) DEFAULT NULL,
`pin_cap` varchar(1) DEFAULT NULL,
`set_ind` varchar(1) DEFAULT NULL,
`auk_met` varchar(1) DEFAULT NULL,
`acq_med` varchar(1) DEFAULT NULL,
`me_sou` varchar(1) DEFAULT NULL,
`res_ins` varchar(4) DEFAULT NULL,
`auk_ins` varchar(4) DEFAULT NULL,
`iss_ins` varchar(4) DEFAULT NULL,
`tra_ori_ins` varchar(11) DEFAULT NULL,
`int_net` varchar(1) DEFAULT NULL,
`ser_cod` varchar(3) DEFAULT NULL,
`sys_fla` varchar(2) DEFAULT NULL,
`sta_fla1` varchar(2) DEFAULT NULL,
`sta_fla2` varchar(2) DEFAULT NULL,
`sta_fla3` varchar(2) DEFAULT NULL,
`sta_fla4` varchar(2) DEFAULT NULL,
`loa_dat` int(10) unsigned DEFAULT NULL,
`loa_tim` int(10) unsigned DEFAULT NULL,
`fil_nam` varchar(20) DEFAULT NULL,
index idx (id),
KEY `b_num` (`b_num`),
KEY `car_num` (`car_num`),
KEY `dat` (`dat`),
KEY `mc` (`mc`),
KEY `res_cod` (`res_cod`),
KEY `me_id` (`me_id`),
KEY `me_nam` (`me_nam`),
KEY `me_cou` (`me_cou`),
KEY `cur_cod` (`cur_cod`),
KEY `p_e_m` (`p_e_m`),
KEY `tim_sta` (`tim_sta`),
KEY `int_net` (`int_net`),
KEY `xcar_num` (`xcar_num`(19)),
KEY `fil_nam` (`loa_tim`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE( loa_dat ) (
PARTITION p1 VALUES LESS THAN(20070101),
PARTITION p2 VALUES LESS THAN(20070102),
PARTITION p3 VALUES LESS THAN(20070103),
PARTITION p4 VALUES LESS THAN(20070104),
PARTITION p5 VALUES LESS THAN(20070105),
PARTITION p6 VALUES LESS THAN(20070106),
PARTITION p7 VALUES LESS THAN(20070107),
PARTITION p8 VALUES LESS THAN(20070108),
PARTITION p9 VALUES LESS THAN(20070109),
PARTITION p10 VALUES LESS THAN(20070110));
I have been testing this on a Linux box running Server version: 5.1.22-rc-community-log.
What could cause this behaviuor?
Regards,
Daniel
Edited 1 time(s). Last edit at 12/19/2007 05:26AM by Daniel Andersson.