MySQL Forums
Forum List  »  Partitioning

Exceptions with Partition Pruning
Posted by: ratheesh balakrishnan
Date: June 10, 2010 04:36PM


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


Options: ReplyQuote

Written By
Exceptions with Partition Pruning
June 10, 2010 04:36PM

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.