So - I have 3 tables: feed_datastore, feed_index_site, and site.
feed_datastore has uuid | ts | data (which is a text column with serialized data)
feed_index_site is site_ID | uuid
site has site_ID | xxxx (bunch of other columns - eg one being country_ID).
feed_datastore has data from each site and feed_index_site connects site_ID to uuid
What I’m trying to do is extract uuid, data from feed_datastore based on the country_ID in site. I get something like this:
SELECT a.ts,a.uuid,a.data FROM
site c WHERE a.uuid=b.uuid AND b.site_ID=c.site_ID AND c.country_ID=4 ORDER BY ts DESC LIMIT 6
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?