# Partitioning Design

**URL:** <https://forums.percona.com/t/partitioning-design/887>\
**Category:** Other MySQL® Questions\
**Created:** [August 14, 2008, 9:57am UTC](https://forums.percona.com/t/partitioning-design/887 "2008-08-14T09:57:01Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cody\_Lerum](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cody_lerum/32/3278_2.png) [@Cody\_Lerum](https://forums.percona.com/u/Cody_Lerum)\
**Post date:** [August 14, 2008, 9:57am UTC](https://forums.percona.com/t/partitioning-design/887/1 "2008-08-14T09:57:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [September 19, 2008, 11:00pm UTC](https://forums.percona.com/t/partitioning-design/887/2 "2008-09-19T23:00:48Z")

</div>

I’d read up on scalability on [http://highscalability.com](http://highscalability.com).

You may wish to use an archive type table for old data and partition that way.