Mysql 5.6 supports PARTITION clause in the query itself.
Mysql 5.5 relies on something called as partition pruning.
>show create table mytable
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
...
PARTITION BY LIST (DAYOFMONTH(created_at))
(PARTITION day1 VALUES IN (1) ENGINE = InnoDB,
PARTITION day2 VALUES IN (2) ENGINE = InnoDB,
...
PARTITION day31 VALUES IN (31) ENGINE = InnoDB)
explain partitions select count(*) from mytable where created_at = '2014-06-19'
explain partitions select count(*) from mytable where creation_date between '2014-06-19' and '2014-06-19'
Uses partition day19
Returns only those rows that have created_at exactly equal to 2014-06-19 00:00:00'
explain partitions select count(*) from mytable where date(created_at) = '2014-06-19'
Correctly returns all rows of 19th June
But uses all partitions day1 to day31. Apparently this happens if you use any function on the LHS the where clause.
explain partitions select count(*) from mytable where timestamp >= '2014-05-13' and time
stamp <= '2014-05-14';
Uses all partitions. Fails to prune partitions. All rows of 13th May.
Obviously I am screwed. Relying on indices for any performance improvement.
Apparently designing partitions on date has this drawback where your actual cell value is more granular than your partition definition. Here my actual value are timestamps accurate to seconds while my partition is defined on day of month(1-31).
This was used to leverage faster purging of data by truncating day-wise partitions. However leveraging any form of performance improvement on select queries seems impossible.
