Re: partition table performance

[fz wsc]

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 RANGE (I1) (PARTITION p0 VALUES LESS THAN ..........); // total 172 partitions used

Create index table1_1 on table1 (V1, I2); // to speed up `select * from table1 where V1 like 'abcd%' order by V1,I2 limit 1,2000;`

Now, it takes about 2 seconds to complete `delete from table1 where I1=12345`;

And it takes about 9 seconds to complete `insert into table1 (select sequenceForID(), 12345, I2, I3, V1, V2, V3, V4, V5, V6, Da1, De1 from table2)`;

For `select * from table1 where V1 like 'abcd%' order by V1,I2 limit 1,2000;`,
it takes about 20 seconds.

The former two SQL statement(delete&insert) is fast enough now. I hope the `select .....` can be finished in 5 seconds because Sql Server 2005 can finish the same SQL statement in about 5 seconds on the same hardware and the same envioronment (with clustered index).

Any suggestion?



Edited 1 time(s). Last edit at 07/19/2006 07:23PM by fz wsc.