MySQL Forums
Forum List  »  Partitioning

Cardinality and partions
Posted by: Daniel Andersson
Date: December 19, 2007 05:25AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cardinality and partions
4783
December 19, 2007 05:25AM


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.