On Fri, May 16, 2008 at 6:44 AM, Ben Clewett <ben@stripped> wrote:
> Looking at EXPLAIN PARTITIONS I have noticed that these SQL statement
> correctly select the partition:
>
> SELECT WHERE t =
> SELECT WHERE t IN
>
> But these select all partition tables:
>
> SELECT WHERE t >
> SELECT WHERE t BETWEEN
>
> So, my question is, without having to re-write several billion lines of
> code, is there a way I can make all these examples correctly select the
> partition table?
Hmm...
According to http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html :
" The following queries on t2 can make of use partition pruning:
SELECT * FROM t2 WHERE dob = '1982-06-23';
SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
SELECT * FROM t2 WHERE YEAR(dob)
IN (1979, 1980, 1983, 1985, 1986, 1988);
SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21' "
Which look an awful lot like all your queries. For kicks and giggles I
though I would try it out:
mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `t2` (
-> `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
-> `t` TIMESTAMP NOT NULL ,
-> PRIMARY KEY ( `id` , `t` ),
-> KEY (t)
-> )
-> PARTITION BY RANGE ( YEAR(t) )
-> (
-> PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2001),
-> PARTITION p2 VALUES LESS THAN (2002),
-> PARTITION p3 VALUES LESS THAN (2003),
-> PARTITION p4 VALUES LESS THAN (2004),
-> PARTITION p5 VALUES LESS THAN (2005),
-> PARTITION p6 VALUES LESS THAN (2006),
-> PARTITION p7 VALUES LESS THAN (2007),
-> PARTITION p8 VALUES LESS THAN (2008),
-> PARTITION p9 VALUES LESS THAN (2009),
-> PARTITION p10 VALUES LESS THAN (2010)
-> );
Query OK, 0 rows affected (0.33 sec)
mysql> CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP)
-> RETURNS TIMESTAMP NOT DETERMINISTIC
-> RETURN FROM_UNIXTIME(
-> FLOOR(
-> UNIX_TIMESTAMP(start) +
-> RAND() *
-> (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start))
-> )
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE IF NOT EXISTS `integers` (
-> `i` int(11) NOT NULL default '0',
-> PRIMARY KEY (`i`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> INSERT INTO `integers` (`i`) VALUES
-> (0),
-> (1),
-> (2),
-> (3),
-> (4),
-> (5),
-> (6),
-> (7),
-> (8),
-> (9);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 SELECT NULL,random_timestamp('1999-01-01','2009-12-30')
-> FROM integers AS `i1`
-> INNER JOIN integers AS `i2`
-> INNER JOIN integers AS `i3`
-> INNER JOIN integers AS `i4`
-> INNER JOIN integers AS `i5`
-> INNER JOIN integers AS `i6`;
Query OK, 1000000 rows affected (1 min 17.12 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM t2 WHERE t ='2006-11-28'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: p7
type: ref
possible_keys: t
key: t
key_len: 4
ref: const
rows: 1
Extra: Using index
1 row in set (0.00 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM t2 WHERE t BETWEEN
'2006-11-28' AND '2006-12-15'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10
type: range
possible_keys: t
key: t
key_len: 4
ref: NULL
rows: 8674
Extra: Using where; Using index
1 row in set (0.00 sec)
It seems to me either the documentation is wrong, the output of
EXPLAIN PARTITIONS is wrong, or there was a regression at some point.
Or I could have an incorrect understanding of partitions on MySQL
(very possible)...
--
Rob Wultsch
wultsch@stripped
wultsch (aim)
