Hi,
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:
http://mikaelronstrom.blogspot.com