Re: partition table performance

[fz wsc]

New test result on Sql Server 2005:

For `select * from table1 where V1 like 'abcd%' order by V1,I2 limit 1,2000;`,
Sql Server 2005 takes no more than 2 seconds to finish. It seems that sql server 2005 save the clustered index in each partition. Anyway, mysql is not as fast as sql server 2005 partition table. I hope mysql can beat sql server 2005 as soon as possible.

Summary:

0) This case is a little complicate: partitioned by key(I1), but in most cases,
query on V1. (As tested, this is the fastest method)

1) In Sql Server 2005(Partitioned by I1):

CREATE CLUSTERED INDEX [V12] ON [table1]([V1],[I2])
-- to speed up the query, use clustered index

In MySql(also partitioned by I1):

create index V12 on table1 (V1,I2);

2) For batch `delete` and `insert` on partition table, mysql and sql server 2005 is almost as fast as each other.

2) For `select * from table1 where V1 like 'abcd%' order by V1,I2 limit 1,2000;`
Sql Server is much more faster than MySql.

Why? Because Sql Server 2005 uses clustered index? Or because Sql Server 2005 partition the index(V1, I2) at the same time as partitioning the data by I1?

Please, mysql, please give the answer or some suggestion to improve



Edited 3 time(s). Last edit at 07/19/2006 07:31PM by fz wsc.