| Partitioning Design [message #3430] |
Thu, 14 August 2008 11:27  |
 mutpup
Messages: 1
Registered: August 2008 |
Junior Member |
|
|
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
|
|
|
|
Members
Search
Help
Register
Login
Home
