Bug #36794 Partition by function of time, optimise queries to use only correct tables.
Submitted: 19 May 2008 7:53 Modified: 24 Aug 2009 8:09
Reporter: Ben Clewett Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1.24 OS:Any
Assigned to: CPU Architecture:Any
Tags: paritioning, time

[19 May 2008 7:53] Ben Clewett
Description:
When a table is partitioned by a function of time, eg, YEAR(t), queries only select the correct partition when a direct competition of t is used.  Eg:

CREATE TABLE p (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  t TIMESTAMP NULL NOT NULL,
  PRIMARY KEY (id, t),
  KEY t (t)
)
PARTITION BY RANGE ( YEAR(t) )
(
  PARTITION p0 VALUES LESS THAN (2006),
  PARTITION p1 VALUES LESS THAN (2007),
  PARTITION p2 VALUES LESS THAN (2008),
  (etc...)
);

Where t is TIMESTAMP, DATETIME or DATE.

These queries will hit the correct table:

SELECT t = 
SELECT t IN

These will hit every table:

SELECT t BETEEEN
SELECT t >
SELECT YEAR(t) = 
SELECT GROUP BY YEAR(t)

This introduced a big performance hit.  This is probably the most common form of partitioning.  Therefore on a stressed system, this will render partitioning unusable.

How to repeat:
Execute any of the examples shown.

Suggested fix:
Optimisation of the partition selection so that a comparison of the partition function YEAR(t) can be compared to the t in the query and therefore hit only the relevant table.
[16 Oct 2008 9:30] Mikael Ronström
This would require the same type of support as indexes on functions.
There is a proposed patch on this from the community so we might handle
this sometime in the future but not for the moment.

https://code.launchpad.net/~andrey-zhakov/mysql-server/mysql-6.0-wl1075-wl411

is the launchpad tree for this patch.
[24 Aug 2009 8:09] Mattias Jonsson
Closing as a duplicate of bug#4990. Since it would need functional indexes to work.