The resulting EXPLAIN has "Using index; Using temporary; Using filesort" on table c (site) - which is killing performance. If I remove the ORDER BY ts the temporary/filesort disappear.

I've tried a bunch of indexes which I thought would work, and even altered the feed_datastore table so that it is ordered by ts DESC but nothing works - anyone have a clue how I can make this run smoother?

Comment

I went a key,value approach - so feed_datastore's key is 'uuid', and the feed_index_site relates/connects `feed_datastore` and `site`

The reason I cannot add the country_ID value to `feed_datastore` is because the country value could change in the site table, requiring the system to then go back and update that value in `feed_datastore` anytime country_ID was changed in `site`

Comment

I had both indexes already created. Unfortunately when I try to run your query, I get an ambiguous error on both straight_joins (something I've never used before). I read up on it and modified your query to this: