MySQL Bugs: #39586: Partition not used when it should be

Bug #39586	Partition not used when it should be
Submitted:	22 Sep 2008 12:39	Modified:	23 Sep 2008 18:42
Reporter:	Pawel Parys	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	5.1.28-rc-community-log	OS:	Windows (2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	optimization, partitioning, SELECT

Description:
Hi,

  I have table partitioned using YEAR(date1). When I make query using range:

SELECT * from table where date1 between '2008-08-01' and '2008-08-31';

  it's works fine using only year2008 partition. But if I rewrite that query like that:

SELECT * from table where YEAR(date1) = 2008 and MONTH(date1) = 8;

  whole table is searched. I expected that using this query will also use only partition as it's made using YEAR function.

How to repeat:
Create table with partitions based on YEAR(any_date), insert some records, execute EXPLAIN PARTITIONS for SELECTs above and observe what partitions are used on each query.

Suggested fix:
Optimize query execution by using only needed partitions instead of all.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php

Please see the manual on Partition pruning,
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
and Bug#36758, which deals with the same issue.