I’m working on setting up a new schema which will store call detail records for a growing phone company. Right now the size is fairly small (10K inserts per day) but this is growing quickly.
Data is really only looked at in monthly reports and so I figure partitioning the best way to handle the growth.
My idea is to partition by Year and then sub-partition by Month. If I understand right I can’t use month(datetime) to partition by and I also cannot have a primary key.
My idea is to use the application to set a year and month. Is that the best way to go or am I missing something fundamental?
Any help would be appreciated.
CREATE TABLE cdr (
id varchar(64) character set utf8 NOT NULL,
GATEWAY varchar(27) character set utf8 NOT NULL,
calling varchar(30) character set utf8 NOT NULL,
called varchar(30) character set utf8 NOT NULL,
ingress_tg varchar(23) character set utf8 NOT NULL,
egress_tg varchar(23) character set utf8 NOT NULL,
duration float NOT NULL,
calling_rc varchar(45) character set utf8 NOT NULL,
calling_region varchar(45) character set utf8 NOT NULL,
calling_lata varchar(5) character set utf8 NOT NULL,
calling_ocn varchar(45) character set utf8 NOT NULL,
called_rc varchar(45) character set utf8 NOT NULL,
called_region varchar(3) character set utf8 NOT NULL,
called_lata varchar(5) character set utf8 NOT NULL,
called_ocn varchar(45) character set utf8 NOT NULL,
btn varchar(10) character set utf8 NOT NULL,
orig_datetime timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
year smallint(5) unsigned NOT NULL,
month tinyint(3) unsigned NOT NULL,
rate float NOT NULL default ‘0’,
type tinyint(3) unsigned default NULL,
cond tinyint(3) unsigned default NULL,
KEY monthyear (year,month),
KEY tg USING BTREE (ingress_tg)
) ENGINE=MyISAM DEFAULT CHARSET=utf8