Re: Improving Slow Query
Some partial answers...
Since you are using InnoDB, and the PRIMARY KEY has those 3 fields, each of the secondary keys has effectively all 3 fields, but in different orders. Your slow SELECT needs pub_date for partition pruning, then may use feature_id and/or pub_date to find things in the index. Or it may give up and use a table scan. A table scan seems likely when you have a huge number of feature_ids. Or it may choose to scan things in pub_date order, thereby taking care of ORDER BY, even before filtering.
Generally, there is no "good" index when you have both an IN and ORDER BY, on different fields.
The rationale for my suggestion of the subquery:
* If you are I/O bound, the main goal is to minimize disk hits. This is more important than minimizing handler stats. At 116GB for the data, I would expect you to be I/O bound.
* The SELECT fetches a lot of fields, and probably carries them around in a tmp table. This 'bulk' has some cost.
* The subquery might involve an "index scan", which is cheaper than a "table scan".
* So, the hope was that the index scan in the subquery would find the 20 keys needed, then only 20 'bulky' rows would be SELECTed.
My analysis was based on a lot of unknown factors, apparently those factors did not mesh together favorably.
I often recommend turning IN into UNION, but that is not practical or cost effective when you have more than a few values in the IN.
I can't predict whether swapping these would help (or possibly hurt other queries):
PRIMARY KEY (feature_id, pub_date, id),
UNIQUE KEY (id,pub_date),
-->
UNIQUE KEY (feature_id, pub_date, id),
PRIMARY KEY (id, pub_date),
The rationale for that involves how InnoDB handles PRIMARY KEYs (embedded in the data's BTree), versus secondary keys (as separate BTree). Switching _may_ help with the subquery I suggested.
I/O bound: Things to consider (though costly) for speeding up:
* RAID
* SSD