MySQL Forums
Forum List  »  Partitioning

Re: partition table performance
Posted by: Mikael Ronström
Date: July 21, 2006 09:04AM

Hi,
InnoDB has clustered index support as well. It does create a clustered
index on the primary key. However partitioned tables in MySQL 5.1 must
have all fields of partitioning function in primary key.

Thus use a primary key on
V1, I2 and I1 (if this is unique, if not add a autoincrement field at the
end of this list).

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.

Rgrds Mikael




fz wsc Wrote:
-------------------------------------------------------
> 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 ON (,)
> -- 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

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
6665
July 14, 2006 12:06AM
3067
July 18, 2006 10:57PM
3172
July 19, 2006 03:00AM
Re: partition table performance
4036
July 21, 2006 09:04AM
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.