CODEWITHSUNDEEP
phpmysqldatabasedatabase-designdatabase-schema
George Milonas
George Milonas

Reputation: 563

Partition a table in mySQL

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

Upvotes: 0

Views: 1203

Answers (3)

Rahul
Rahul

Reputation: 1

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.

Upvotes: 0

kayge
kayge

Reputation: 719

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.

Upvotes: 1

deed02392
deed02392

Reputation: 5022

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.

Upvotes: 0

Related Questions