List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:May 16 2008 8:14pm
Subject:Re: Replication advise
View as plain text  
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)
Thread
Replication adviseBen Clewett16 May
  • Re: Replication adviseRob Wultsch16 May