Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL:
CREATE TABLE `emp` ( `id` int(11) DEFAULT NULL, `date1` bigint(20) DEFAULT NULL, `gen_year` int(11) GENERATED ALWAYS AS (year(from_unixtime(`date1`))) VIRTUAL, `gen_month` int(11) GENERATED ALWAYS AS (month(from_unixtime(`date1`))) VIRTUAL, UNIQUE KEY `id` (`id`,`gen_year`,`gen_month`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY HASH (12*gen_year+gen_month) PARTITIONS 77 ;
As you can see I want to partition by unique month (taking the year into account). I set 77 partitions because the test data I have put in there uniformly cover 77 months.
When I run the 1st query below, I get pruning just fine, only one partition gets queried.
When I run the 2nd query, I'd expect just a few partitions to be queried, but instead ALL are.
select count(*) from emp where gen_year=1984 and month=8;
select count(*) from emp where gen_year=1984 and month>8;
select count(*) from emp where gen_year=1984 and month BETWEEN 8 AND 12;
EXPLAIN PARTITIONS gives one partition for the first query, but all 77 partitions for the others.
Please advise on what I'm missing.