MySQL Forums
Forum List  »  Partitioning

Re: partition table performance
Posted by: fz wsc
Date: July 18, 2006 10:57PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
6661
July 14, 2006 12:06AM
Re: partition table performance
3061
July 18, 2006 10:57PM
3168
July 19, 2006 03:00AM
4749
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.