At 02:03 PM 9/26/2006, you wrote:
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
Mike,
How is this table being updated?
a) From one source like a batch job?
b) Or from hundreds of users concurrently?
If a), then why not just create 1 table per day (or 3 tables per day) and
when you want to reference (the entire day or) a week, just create a Merge
Table?
http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
If b), then you need to use InnoDb tables because that has row locks
compared to MyISAM's table locks.
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'
)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
