MySQL Forums
Forum List  »  Partitioning

Other conditions in subpartitions
Posted by: Evgeniy Bulichev
Date: June 28, 2007 07:00AM

Hi,
There is a statement in MySQL manual about subpartitions:
"In MySQL 5.1, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning."
I am interested to know whether in future releases will be implemented an ability to use other conditions for subpartitioning.
Here is an example.
I want to divide my table on partitions using two fields (year and month). My queries will run either against one field (year) or against both fields (year and month).
We may write something like this:
PARTITION BY LIST (year*100 + month)(
PARTITION p1990_01 VALUES IN (199001),
PARTITION p1990_02 VALUES IN (199002),
....
PARTITION p2006_12 VALUES IN (200612)
)

If we want to run query against field year we need to specify all possible months for this year to make optimal query, otherwise MySQL will search through ALL partitions!

It will be great to write something like this:

PARTITION BY LIST (year)
SUBPARTITION BY LIST(month)(
PARTITION p1990 VALUES IN (1990) (
SUBPARTITION s1990_1 VALUES IN (1),
....
SUBPARTITION s1990_12 VALUES IN (12)
),
....
PARTITION p2006 VALUES IN (2006) (
SUBPARTITION s2006_1 VALUES IN (1),
....
SUBPARTITION s2006_12 VALUES IN (12)
)
)

Then, if we specify only year in our query, MySQL will search through only one partiton. If we specify both year and month, MySQL will search in one subpartition of one partition.
We can't get needed results using HASH or KEY for subpartitions.

Options: ReplyQuote


Subject
Views
Written By
Posted
Other conditions in subpartitions
5983
June 28, 2007 07:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.