MySQL Partition by Date Not Pruning the Query

MySQLpartitioning

I have this table:

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `lat` DOUBLE NOT NULL,
   #...
  `date` datetime NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  KEY `pk` (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (MONTH(date))
(PARTITION part0 VALUES LESS THAN (2),
 PARTITION part1 VALUES LESS THAN (3),
 PARTITION part2 VALUES LESS THAN (4),
 PARTITION part3 VALUES LESS THAN (5),
 PARTITION part4 VALUES LESS THAN (6),
 PARTITION part5 VALUES LESS THAN (7),
 PARTITION part6 VALUES LESS THAN (8),
 PARTITION part7 VALUES LESS THAN (9),
 PARTITION part8 VALUES LESS THAN (10),
 PARTITION part9 VALUES LESS THAN (11),
 PARTITION part10 VALUES LESS THAN (12),
 PARTITION part11 VALUES LESS THAN MAXVALUE);

I use partition by Month function to speed up the queries because I have a huge amount of data in this table.

But when I tried a query like this:

Explain SELECT * FROM test.new_table where date between '2017-02-02' and '2017-04-25';

I got this result:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE  | new_table | part0,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part11  | ALL      |           8   | 12.50 |   Using where

I see that I can't benefit from Pruning feature of MySQL.

What I can do else to enhance incoming queries ?

Edit

The most used case to get data is Between 2 dates.

Examples of queries:

SELECT 
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.`date` BETWEEN `u`.`date_start` AND `u`.`date_end`
 OR (ISNULL(`u`.`date_end`) AND (`l`.`date` >= `u`.`date_start`))

2

SELECT 
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.id in (...)

Best Answer

BY RANGE(MONTH(date)) does not prune.

Even if id did prune, it would probably not provide any performance benefit over having a non-PARTITIONed table with a suitable index. May we see your queries?

BY RANGE(TO_DAYS(date)) does prune. But you need to add a new partition periodically, and (optionally) DROP an old partition.

Blog on partitioning.

Related Question