join cluster mess

[certehfiable]

I'm trying to make heads or tails out of this slow query. There has to be a better way.

 

This query scans through the forums and topics and pulls the latest post replies. Why do I fear this is "join" frenzy b0rked?

 

# Time: 091001 20:05:12
# User@Host: ****[****] @ localhost []
# Query_time: 11  Lock_time: 0  Rows_sent: 5  Rows_examined: 533145
SELECT t.tid, t.title, t.posts, t.views, t.forum_id, p.topic_id, p.author_id, p.pid, p.author_id, p.post_date, p.topic_id, m.member_group_id, members_seo_name, m.members_display_name FROM IVBtopics t LEFT JOIN IVBposts p ON ( t.tid=p.topic_id ) inner JOIN (select topic_id, max(post_date) as datapost from IVBposts group by topic_id) as ultimopost on ultimopost.datapost = p.post_date LEFT JOIN IVBmembers m ON ( m.member_id=p.author_id ) WHERE p.queued = 0 and t.approved = 1 and t.posts > 0 and t.forum_id in(75,251,121,81,206,76,103,82,93,77,83,84,113,79,143,78,80,216,120,112,5,3,110,26,4,9,54,65,104,59,15,21,18,45,157,158,66,64,74,99,28,8,36,165,247,89,6,61,41,49,47,98,60,52,62,70,71,94,40,95,96,111,168,169,170,171,172,173,246,106,46,101,105,155,160,161,115,162,114,119,117,118,202,238,127,137,220,203,214,128,129,130,131,156,138,139,140,141,144,145,146,201,148,152,154,248,159,163,185,186,187,174,175,176,188,189,190,191,192,193,194,195,200,196,197,198,199,208,204,209,210,211,207,205,212,213,217,218,219,225,226,227,228,229,233,230,231,232,221,222,223,224,234,235,236,237,239,245,240,241,242,243,256,255,250,249,252,253,254) and t.forum_id NOT in(244,166,102,107,108,109,239,245,242,243,122,123,124,125,126,130,131,140,141,223,224,203,164,142,214,215,74,99,168,169,185,186,187,170,174,175,176,171,188,189,190,191,192,172,193,194,195,200,173,196,197,198,199,246,208,205,106,89,94,120,111,112,163,118,79,30,31,147,149,150,151,153,58,159,232,237) ORDER BY p.post_date desc LIMIT 5;

 

[certehfiable]

::visions of tumbleweeds blowing down an old ghost town main street::

 

Thanks everyone.

[fenway]

Holy crap... 5 records from a possible 530K?

 

These kinds of frameworks produce such mysql garbage that I want to hurl.