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