Table partition should not be selected 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?
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.