CODEWITHSUNDEEP
mysqldatabase-partitioning
shilk
shilk

Reputation: 589

MySQL Partition by Month not working

I create a table by

CREATE TABLE `part_tab` (
  `id` int NOT NULL,
  `use_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY HASH(MONTH(use_time)) PARTITIONS 12;

then I use EXPLAIN PARTIONS syntax, but it seems that MySQL didn't use that partition, it still scans the whole table:

mysql> explain partitions select * from part_tab where use_time < '2013-02-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using where
1 row in set (0.00 sec)

if I change the where condition to equal，the partition is used:

 mysql> explain partitions select * from part_tab where use_time = '2013-02-01' \G
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

Upvotes: 2

Views: 2334

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Your query is:

select *
from part_tab
where use_time = '2013-02-01';

Your partition statement is:

PARTITION BY HASH(MONTH(use_time)) PARTITIONS 12;

What you are trying to do is called "partition pruning". According to the documentation,

Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.7, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function.

I don't believe there is any way to do partition pruning directly on MONTH() instead of YEAR(). You can rewrite your query as:

select *
from part_tab
where month(use_time) = 1;

However, I suspect that range partitioning on the date is a better solution.

Upvotes: 1

Related Questions