MySQL Forums
Forum List  »  Partitioning

Re: partition table performance (vs sql server 2005)
Posted by: Mikael Ronström
Date: July 24, 2006 01:02AM

See comments below.

fz wsc Wrote:
> 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.

I guess you mean primary key (V1+I2+I1). Hmm, 800 seconds
sounds a very lot. How many records are deleted? Maybe an
index on I1 would be beneficial? The insert can probably be
optimised if you add an order by V1,I2,I1 in the select
statement to ensure that you insert into the clustered index
in order to avoid thrashing when writing the clustered index.

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

I'll think of some way to distribute it, maybe a patch or
something, it's so early in the design cycle that we haven't
started thinking of public clones yet, however I should have
something testable in just a few weeks that should probably
make the select really fast (how many records does it return?).

Rgrds Mikael

> Regards,

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:

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 24, 2006 01:02AM
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.