Re: What Method is Best

[Mattias Jonsson]

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)