This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:
`ID` int(11) NOT NULL, `target_id` int(11) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`ID`, `created_at`), KEY `index_created_at_target_id` (`created_at` desc, `target_id`) KEY `index_on_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY HASH(MONTH(`created_at`)) PARTITIONS 12
If I query the data with a single
select * from my_table where `created_at`= '2018-12-00 05:00:00' AND target_id in (6,7,8);
Then the explain knows to only hit 1 partition. This is correct.
However, if I query on a range:
select * from my_table where `created_at` BETWEEN '2018-12-00 05:00:00' AND '2019-01-04 04:59:59' AND target_id in (6,7,8);
The explain now hits all partitions. Is this a known limitation, or am I just doing something wrong?
Side note; judging by other "partitioning" questions, I expect a couple "partitioning doesn't help just index correctly" responses... I'm just trying it for myself and gathering some metrics so we can find the solution that performs best for our needs. (also partitioning the table, even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes; 2.3s vs 0.8s)
I did make sure that both queries do hit the
index_on_created_at index. The only difference is a 'non unique key lookup' versus an 'index range scan' on the index.