Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2011

    Unanswered: Multi Join Optimisation/Explain help.

    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)

    explain extended
    SELECT mp.author_id
    , mp.msg_date
    , mt.subject
    , mp.msg_post
    , 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 = 
    JOIN SA_angels_to_message_topics amt ON amt.topic_id = AND amt.angel_id = 2 
    ORDER BY mt.last_post_date DESC 
    LIMIT 10
    Attached Thumbnails Attached Thumbnails grab.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts