MySQL Forums
Forum List  »  Partitioning

Re: What Method is Best
Posted by: Mattias Jonsson
Date: September 14, 2012 02:36AM

I agree that sub-partitioning will not help you, since that can only be HASH/KEY based and not range based.

What you could try out is to use RANGE COLUMNS partitioning like
PARTITION BY RANGE COLUMNS (startTime, endTime)

Do you have a PRIMARY KEY? (you normally should!)
If you can accept that pruning will only happen on one of the start/end time columns, then you can make use of the index prefix for the second column, like this:
(Notice the order in the primary key!)
CREATE TABLE t1 (startTime TIMESTAMP, endTime TIMESTAMP, filler varchar(255), PRIMARY KEY (startTime, endTime))
PARTITION BY RANGE (UNIX_TIMESTAMP(endTime))
(PARTITION pre2000 VALUES LESS THAN (UNIX_TIMESTAMP('2000-01-01')),
PARTITION p2000 VALUES LESS THAN (UNIX_TIMESTAMP('2001-01-01')),
PARTITION p2001 VALUES LESS THAN (UNIX_TIMESTAMP('2002-01-01')),
PARTITION pMax VALUES LESS THAN MAXVALUE
);
This will only prune by endTime, but in the resulting set of partitions, it will use the primary key for index scan according to startTime:
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE startTime BETWEEN '2000-01-02' AND '2001-01-02'
AND endTime BETWEEN '2000-02-04 12:00:00' and '2001-02-04 12:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p2000,p2001 range PRIMARY PRIMARY 8 NULL 2 Using where

Apart from this, can you add more information about the table and usage? (perhaps there are better ways to increase performance)

Options: ReplyQuote


Subject
Views
Written By
Posted
2362
September 12, 2012 03:12PM
Re: What Method is Best
1503
September 14, 2012 02:36AM
1413
September 14, 2012 05:30AM
1299
September 14, 2012 11:11PM


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.