MySQL when using WHERE BETWEEN, query hits all partitions
You have just found one of many reasons why PARTITIONing is mostly useless.

Even with your first SELECT, there is no performance benefit over what you could have with a non-partitioned table with a suitable index. j In particular:

INDEX(target_id, created_at)  -- in this order


The second SELECT shows how PARTITION BY HASH treats a range -- by looking in all partitions.  It has no choice but to do that.  Yeah, yeah, with a lot more smarts, it could notice that that date range should hit only two months.  Even so, non-partitioned with a suitable index would run faster and be simpler.

Other comments about the indexes:

KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
KEY `index_on_created_at` (`created_at`)


has two issues:


INDEX(a,b), INDEX(a) -- you may as well toss the latter since the former can serve its purposes.
Until MySQL 8.0, DESC is ignored in INDEX definitions.  (Since you don't have an ORDER BY that depends on it, there is no harm done.)


If your goal is to understand what partitioning can/cannot do, I recommend my blog .


  even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes


I dispute that.  Did you run with the Query cache off?  Did you run each test twice -- to avoid buffer_pool_ caching differences?  Actually, the key is probably "with the same indexes".  Almost always, when switching between partitioned and non-partitioned, the indexes need to be revised.


When partitioned, put the partition key later in each index where it is needed.  Or leave it out if the partition pruning suffices.
When not partitioned, non-range columns should be early in indexes.
When not partitioned, range columns (eg date range) should be late in indexes.


Why even a point query does not speed up with partitioning.  Notice how either way seems to be about the same amount of work:


Non partitioned:  BTree index that is used is, say, 3 levels deep.  Hop-skip-jump, there is the row you desire.
Partitioned:  First prune to the desired partition ("hop").  The, say, the BTree is only 2 levels deep ("skip-jump").


Ah.  Maybe you can discover a 5th use case for Partitioning.