MySQL Forums :: Partitioning :: Exceptions with Partition Pruning

Advanced Search

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

Subject Views Written By Posted
Exceptions with Partition Pruning 3386 ratheesh balakrishnan 06/10/2010 04:36PM
Re: Exceptions with Partition Pruning 1538 Rick James 06/12/2010 01:21AM
Re: Exceptions with Partition Pruning 1600 ratheesh balakrishnan 06/14/2010 01:10PM
Re: Exceptions with Partition Pruning 1355 Rick James 06/16/2010 09:31AM
Re: Exceptions with Partition Pruning 1606 ratheesh balakrishnan 06/16/2010 05:28PM

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.