I'm trying to get the latest post per topic, latest first for a given user.
The query works as in it returned what I need. I don't fully appreciate what the explain is telling me.
My test data is very small, 200 posts, 5 topics.
If this query is likely to be a problem how can I optimise it.
If it's easier to read outside of the image..
There are indexes on all the date fields (datetime) and the id fields (author_id, topic_id etc)
, mt.id as topic_id
, CONCAT(a.first_name, " ", a.last_name) as name
FROM (SA_message_topics mt)
JOIN SA_message_posts mp ON mt.last_post_date = mp.msg_date
JOIN SA_angels a ON mp.author_id = a.id
JOIN SA_angels_to_message_topics amt ON amt.topic_id = mt.id AND amt.angel_id = 2
ORDER BY mt.last_post_date DESC