This is for a threaded private message system.
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)
Code:
explain extended
SELECT mp.author_id
, mp.msg_date
, mt.subject
, mp.msg_post
, mt.id as topic_id
, CONCAT(a.first_name, " ", a.last_name) as name
, a.profile_image
, amt.has_unread
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
LIMIT 10
dBforums
>
Multi Join Optimisation/Explain help.
05-16-11, 17:12
Linear Mode
