MySQL Forums
Forum List  »  Partitioning

partition table performance
Posted by: fz wsc
Date: July 14, 2006 12:06AM

Hi, dear everyone,

In order to test partition table, I make 2 tables, `table1` is huge (over 30000 milion rows). `table2` just has about 60 thousand rows. I want to delete some rows from table1 (`where I1=12345`) and then insert all rows of table2 into table1 (with I1=12345).


CREATE TABLE `table1` (
`ID` int(11) DEFAULT NULL,
`I1` int(11) DEFAULT NULL,
`I2` int(11) DEFAULT NULL,
`I3` int(11) DEFAULT NULL,
`V1` varchar(50) DEFAULT NULL,
`V2` varchar(50) DEFAULT NULL,
`V3` varchar(50) DEFAULT NULL,
`V4` varchar(50) DEFAULT NULL,
`V5` varchar(100) DEFAULT NULL,
`V6` varchar(100) DEFAULT NULL
`Da1` date DEFAULT NULL,
`De1` decimal(18,4) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY LINEAR KEY (I1) PARTITIONS 100 ;

ALTER TABLE table1 ADD INDEX (I1), ADD INDEX (V1);

delete from table1 where I1=12345;
insert into table1 (select sequenceForID(), 12345, I2, I3, V1, V2, V3, V4, V5, V6, Da1, De1 from table2);

I found it took too much time for `delete ......` to complete(from 30 to 50 seconds), while `insert ......` took less time (about 5 seconds)

Is it possible to reduce the time (especially the long `delete` time)? Thank you for any suggestion!

Options: ReplyQuote


Subject
Views
Written By
Posted
partition table performance
7087
July 14, 2006 12:06AM
3226
July 18, 2006 10:57PM
3383
July 19, 2006 03:00AM
4973
July 25, 2006 03:00AM


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.