0

I am looking to partition a table (nearly 30M rows) by date ranges
(mysql version : 5.1.73)

CREATE TABLE `TABLE_X` (
  `name_x` varchar(50) NOT NULL,
  `type_x` varchar(5) NOT NULL,
  `status_x` tinyint(1) NOT NULL,
  `date_x` date NOT NULL,
  KEY `xdate` (`date_x`)
)ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(`date_x`))
 (
    PARTITION p13q1 VALUES LESS THAN (TO_DAYS('2013-04-01')),
    PARTITION p13q2 VALUES LESS THAN (TO_DAYS('2013-07-01')),
    PARTITION p13q3 VALUES LESS THAN (TO_DAYS('2013-10-01')),
    PARTITION p13q4 VALUES LESS THAN (TO_DAYS('2014-01-01')),
    PARTITION p14q1 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    PARTITION p14q2 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    PARTITION p14q3 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    PARTITION p14q4 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    PARTITION p15q1 VALUES LESS THAN (TO_DAYS('2015-04-01')),
    PARTITION p15q2 VALUES LESS THAN (TO_DAYS('2015-07-01')),
    PARTITION p15q3 VALUES LESS THAN (TO_DAYS('2015-10-01')),
    PARTITION p15q4 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p16q1 VALUES LESS THAN (TO_DAYS('2016-04-01'))
);

when i do a 'select' query to retrieve data between two dates from the same month, the result is correct

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-10-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 984405
        Extra: Using where; Using index

but when i try to retrieve rows between different months, i get this result

Eg 1:

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-11-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 1562732
        Extra: Using where; Using index

this query should use only p13q4 partition

Eg 2:

    explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2014-01-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4,p14q1
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 2085003
        Extra: Using where; Using index

this query should use p13q4 & p14q1 partitions.

I don't understand why it's using the p13q1 partition,

What am I missing?

0

This seems to be known problem with to_days and between - if there were some invalid dates in the table, to_days return NULL on them and stores them in the first partition. And when evaluating BETWEEN, mysql can sometimes decide that some dates in that range are invalid and so it thinks it has to check first partition to find them. Such dates giving NULL may be for instance '2013-11-00' which might theoreticaly fall to that between range so optimizer better checks for them - and there is probably no consensus if it is right or wrong..

Possible workaround would be to define special partition for null values. That would be empty most of the time, so performace should be better in some cases.

0

I cannot fully answer this question but I can give you some tips on what to look around for.

First thing I would do is check the dates inside each partition as follows

SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p16q1) GROUP BY date_x;

If dates are partitioned correctly, then you have a bug-infested mysql in relation to the query optimizer. If dates appear in a partition that do not belong, I would reload the table from scratch as follows:

CREATE TABLE TABLE_Y LIKE TABLE_X;
INSERT INTO TABLE_Y SELECT * FROM TABLE_X;
ALTER TABLE TABLE_X RENAME TABLE_XXXX;
ALTER TABLE TABLE_Y RENAME TABLE_X;

Then, retry your queries.

I noticed you are using MySQL 5.1. There are many patches to the partition code (such as ALTER TABLE ... REPAIR PARTITION). I would suggest installing MySQL 5.6 on a test server, copying the data into it and try out your queries on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.