partition table performance

[fz wsc]

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!