Partitioning performance issues // 5.1

[Ya Wi]

Hi all,
I am testing partioning since few days and I am not able to obtain better performances with partioned table versus classical table :

Version : 5.1.xx
OS : Linux 64 and Windows 32
Engine : InnoDB

I have a standard table whose PK is (user_id, post_id) where
user_id between 1 and 5,000,000
post_id between 1 and 500,000
Other columns are set to null.

Another table has 10 partitions based on user_id (500,000 user_id ranges)

I fill test tables with 20 millions of records (data are evenly spread on user_id dimension)

Test 1 :
I insert 1 million of records on a specific range of user_id , ie I hit only one partition.

Test 2 :
I randomly insert 1 million of records into tables.

In the two cases, process take more time with partioned tables than with a classical innodb table, what I cannot understand.

Is partioning only useful for select queries ? Or are my tables too small ?
Maybe partioning need separate disks ?

Many thanks for our advises,
yaw



Edited 1 time(s). Last edit at 02/17/2010 10:47AM by Ya Wi.