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:Microsoft Windows (2003 Server)
Assigned to: CPU Architecture:Any
Tags: optimization, partitioning, SELECT

[22 Sep 2008 12:39] Pawel Parys
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.
[23 Sep 2008 18:42] Giuseppe Maxia
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.