Exceptions with Partition Pruning

[ratheesh balakrishnan]

Hi,

I've been testing MYSQL 5.1 partitioning functionality from over weeks now, as we were planning to implement it on production database.

I've had many benchmark figures but most of them were -ive then +ive from performance perspective

My testing scenarios were:

# Uploading data in Innodb table similar to a production table with 2Million, 3Million, 6 Million & 128 Million

# Partitioned the table based on primary key using Hash,Liner Hash & range partition

# Pretty much all SQL queries had partitioned key in their WHERE clause with an IN operator

# The IN operator have been provided from single value to 2000 values

# Created few queries without partitioned key column in the WHERE clause

Results from the test:

# All queries with minimum values in the WHERE clause with partitioned key columns were not performing well compared to the regular table irrelevant of the size of table , # partitions & type of the partition method. Hence, partition pruning was not kicking in

# Queries with IN parameter values from 100 to 2000 were performing extremely well than a regular table

# Queries with the WHERE clause not having key partitioned column were not performing well with the partitioned table

As mentioned above, these results were consistent irrelevant of the size of table , # partitions & type of the partition method.


Please share your thoughts and experiences

Thanks
Ratheesh