Partition a table in mySQL

Question

Have a table with 3 million rows and would like to partition it. There are 5 columns, one of them which is a date column. I would like to split up the table by dates. Any easy help would be great. Thanks

Answer 1

If I'm understanding your question correctly, there are a couple of ways to do this:

You could do it using the Between keyword to break down by specific date ranges.

drop table if exists TableA, TableB;

create table TableA
select * from YourTable
where DateColumn between '2011-07-01' and '2012-02-01';

create table TableB
select * from YourTable
where DateColumn between '2011-01-01' and '2011-06-30';

Or if your breakdown is more simple (e.g. you want to break down by year), you can use the date functions.

drop table if exists TableA, TableB;

create table TableA
select * from YourTable
where year(DateColumn) = '2011';

create table TableB
select * from YourTable
where year(DateColumn) = '2012';

There are probably more dynamic ways to do this as well, but I would need to hear some more details of your environment and goals.

Answer 2

You can use Table Partitioning in MySQL. Steps Are:

1. Take a backup of your table.

2. Drop Table from your Database

3. Run the create table query along with partition statement.

   example : http://dev.mysql.com/tech-resources/articles/partitioning.html

   CREATE TABLE part_tab
   (  
       c1 int default NULL,
       c2 varchar(30) default NULL,
       c3 date default NULL
   ) engine=myisam 
   PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
   PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
   PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
   PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
   PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
   PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
   PARTITION p11 VALUES LESS THAN MAXVALUE );
   

4. Now Import data of your table.

Answer 3

What do you mean by split up the table?

You can insert into other tables by performing an insert-select and selecting your existing data doing a self JOIN GROUP BY on the date column.