Help with Query Optimisation

kiran.kc · #1 (**permalink**) 12-04-08, 03:24

# 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..

r937 · #2 (**permalink**) 12-04-08, 03:40

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech