The use of STRAIGHT_JOIN is not without controversy. I didn't find a real recommendation but several critical posts.

/Micha

From a DB administrator perspective, I have no argument with a quote from one of your linked threads: "Optimizer hints should generally be your last resort.". I completely agree, and you'll see those same comments for just about every hint available for use in SQL. Hints should always be used with caution, but can be very helpful when you can't find a more direct way to cajole SQL into using the most efficient query plan.

To put a finer point on it, some hints aren't much more than a strong recommendation, others tell SQL to perform as instructed, unless it deems a significant performance hit, and yet others tell SQL it must do this regardless. Proceed with caution. And yes, efficiency can change with DB statistics updates, index changes, data changes, field changes, and event DB versions, so SQL life is not static.

Regarding the performance comments I've been posting, and my philosophy in general, hints really shouldn't be used unless there is a dramatic performance improvement, and you've exhausted all other mechanisms. As such, the only change I've noted so far that I'd strongly consider implementing is the mod to latest_postings, where run time on our DB goes from 1.75 sec to 1.5 ms (20220803.1).

My mods to total_postings (and some other poorly performing queries) are still an exercise in progress, and do not yet see a material enough performance gain to warrant implementing. My comments were more an exercise in the approaches I've undertaken to try to get them more performant. As noted, the JOINs on _akismet_rating and _b8_rating, in conjunction with a WHERE clause on the spam fields are what is causing poor performance due to table scans.

Anyway, I'm going to keep at it to see what I can further squeeze out of them.

Joe