MySQL Forums
Forum List  »  Partitioning

Re: partition table performance
Posted by: fz wsc
Date: July 19, 2006 03:00AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
6665
July 14, 2006 12:06AM
3066
July 18, 2006 10:57PM
Re: partition table performance
3170
July 19, 2006 03:00AM
4752
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.