On Fri, May 16, 2008 at 6:44 AM, Ben Clewett <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
