MySQL Forums
Forum List  »  Partitioning

Re: partition table performance (vs sql server 2005)
Posted by: fz wsc
Date: July 21, 2006 08:55PM

Hi Mikael,

Thanks for your reply.

> InnoDB has clustered index support as well.

My test results show that Sql server 2005's clustered index without partition table is TOO MUCH slower than `partition_table+clustered_index` for the former `batch delete+insert+select` SQL statement. So I guess, to beat Sql server 2005(batch delete & batch insert & select), the similar tech should be adopted.

To make `batch delete and batch insert` fast, I have to partitioned the table by I1; But most of the queries are on V1,I2.

I also tried to partitioned the table by V1 or V1+I2 but the speed for `batch delete&insert` are very slow.

If partititon by I1 and use (V1+I2+ID) as primary key(to use clustered index), the batch delete and insert take about 800 seconds to complete while `select...` takes about 4 seconds.

> The query with LIKE 'abcd%' cannot really be improved by partitioning,
> at least not in MySQL 5.1. I'm currently working on a variant of
> partitioning which will be suitable for this type as well.

Maybe my case is not uncommon. How can I help(i.e. test)?


Edited 1 time(s). Last edit at 07/21/2006 11:14PM by fz wsc.

Options: ReplyQuote

Written By
July 14, 2006 12:06AM
July 18, 2006 10:57PM
July 19, 2006 03:00AM
Re: partition table performance (vs sql server 2005)
July 21, 2006 08:55PM
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.