Partition Pruning Problem on Billion Row Table

[Steve Orr]

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