MySQL Bugs: #36794: Partition by function of time, optimise queries to use only correct tables.

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

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.

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.

Closing as a duplicate of bug#4990. Since it would need functional indexes to work.