Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: Help with Query Optimisation

    # Query_time: 163sec Lock_time: 0 Rows_sent: 25 Rows_examined: 2043808
    SELECT p.id AS post_id, p.topic_id, t.forum_id, t.topic_title,
    t.count_replies, p.content, p.enable_bbcode, p.enable_smilies,
    p.enable_html, p.poster_id, m.displayed_name AS last_poster_name,
    p.poster_guest AS last_poster_guest, p.post_time FROM usebb_posts p LEFT
    JOIN usebb_members m ON p.poster_id = m.id, usebb_topics t WHERE
    t.forum_id IN(16, 19, 20, 21, 29, 30, 27, 26, 31) AND t.id = p.topic_id
    ORDER BY p.post_time DESC LIMIT 25;

    am running a forum and sometimes its a bit slow.when contacted the server admin he sent me the above query and asked me to optimize the query.Can any one help me with this.?? am relatively new to this field. can i solve the problem to some extend by deleting unwanted posts from the front end?? waiting for ur reply..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    FROM usebb_posts p LEFT
    JOIN usebb_members m ON p.poster_id = m.id, usebb_topics t WHERE
    t.forum_id IN(16, 19, 20, 21, 29, 30, 27, 26, 31) AND t.id = p.topic_id
    to this --
    Code:
      FROM usebb_topics t
    INNER
      JOIN usebb_posts p 
        ON p.topic_id = t.id 
    LEFT OUTER
      JOIN usebb_members m 
        ON m.id = p.poster_id
     WHERE t.forum_id IN(16, 19, 20, 21, 29, 30, 27, 26, 31)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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