MySQL Forums
Forum List  »  Partitioning

Re: Proactive partitioning by Week
Posted by: Mikael Ronström
Date: June 12, 2007 01:57AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4721
June 08, 2007 04:14PM
Re: Proactive partitioning by Week
3393
June 12, 2007 01:57AM
2834
June 13, 2007 11:14AM
3002
July 02, 2007 01:46PM
2907
July 03, 2007 08:43AM


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.