Hi,
Ben Vaughn Wrote:
-------------------------------------------------------
> Hello,
>
> I currently have some MyISAM tables which see
> about 200,000 inserts per day. I would like to
> (once 5.1 goes release) split these tables by
> week, and have a event create a new partition
> every sunday night for the following week. The
> one thing I haven't been clear on yet is if my
> table structure will support a scheme like that.
> Could anyone take a look? I would like to
> parition on the "m_date" field.
>
Looks like a straightforward case, I would propose to use
TO_DAYS(m_date) as the partitioning function and use
PARTITION BY RANGE where you perform an ALTER TABLE ADD
PARTITION once a week and after a while I presume you
need to start doing a ALTER TABLE DROP PARTITION also
on a weekly basis.
Rgrds Mikael
> Thanks!
>
> CREATE TABLE `m_msg` (
> `m_md5` varchar(32) NOT NULL default '',
> `m_from` varchar(128) NOT NULL default '',
> `m_to` varchar(128) NOT NULL default '',
> `m_cc` text NOT NULL,
> `m_subject` text NOT NULL,
> `m_attach` tinyint(1) NOT NULL default '0',
> `m_image` tinyint(1) NOT NULL default '0',
> `m_date` date NOT NULL default '0000-00-00',
> `m_time` time NOT NULL default '00:00:00',
> `m_archived` tinyint(1) unsigned NOT NULL
> default '0',
> PRIMARY KEY (`m_md5`),
> KEY `i_date` (`m_date`),
> KEY `i_from` USING BTREE (`m_from`(25)),
> KEY `i_to` USING BTREE (`m_to`(25)),
> KEY `i_attach` USING BTREE (`m_attach`),
> KEY `i_image` USING BTREE (`m_image`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> MAX_ROWS=2000000000 PACK_KEYS=0
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com