Partition Help

[Michael Gargiullo]

I'm working on a project in which we'd like to convert from Oracle to
MySQL. We need to partition our data for speed concerns.  Currently in
Oracle I create 8, 3 hour partitions for each day (Currently running
450M -750M rec inserts/day). I was looking for matching functionality in
MySQL, but it seams daily partitions are as close as I'm going to come.

 

We're running 5.1.10 and I'm having a bit of trouble creating partitions
in both new tables and altering old tables.  Below is one example of
what I've tried.

 

Can anyone shed some light on this subject?

 

 

-Mike

 

create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3
datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3))

      PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))(

        SUBPARTITION s0a

          DATA DIRECTORY = '/FW_data1'

          INDEX DIRECTORY = '/FW_indx1'

      ),

     PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))(

        SUBPARTITION s1a

          DATA DIRECTORY = '/FW_data2'

          INDEX DIRECTORY = '/FW_indx2'

      )

      PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))(

        SUBPARTITION s2a

          DATA DIRECTORY = '/FW_data3'

          INDEX DIRECTORY = '/FW_indx3'

      )

);