MySQL Forums
Forum List  »  Partitioning

Partition Pruning Problem on Billion Row Table
Posted by: Steve Orr
Date: May 21, 2009 05:12PM

A process has been collecting data for about 6 months and has now accumulated a billion rows. The table has a primary key based on 4 columns where the first PK column, "stat_date," is a date datatype. 400 partitions were created on this stat_date column alone using key partitioning.

The table was originally for archive purposes but the business need has changed to include data querying. And of course we ran into a partition pruning problem when we started querying the data. Doh! Queries like "where stat_date in (...)" work fine, but I've confirmed that queries like "where stat_date between..." are slow because they are unable to perform partition pruning.

The guy who created this table assumed one partition would be created for each date but my "explain partition" query analysis shows otherwise. (The intent was to drop daily partitions older than one year.)

Question 1: How can I determine which key values were placed in which partitions? (I looked in the information_schema and couldn't intuit the answer.)

Question 2: In order to properly create a partition for each day of the year we'll need to use range partitioning but I don't like the burden of having to explicitly name each partition. Has anyone every partitioned by year and subpartitioned by month? We're getting about 6 million rows per day and I'm not sure how this would perform. I'm looking for suggestions on how to minimize the maintenance burden and maximize partition pruning.

Running MySQL 5.1.34


AtDhVaAnNkCsE <-- TIA

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition Pruning Problem on Billion Row Table
5908
May 21, 2009 05:12PM


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.