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)?

Regards,



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

Options: ReplyQuote


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