SELECT t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky,
t.ID_POLL, t.id_prefix, 0 AS new_from, t.ID_LAST_MSG,
t.recycledTime, ml.posterTime AS lastPosterTime, ml.ID_MSG_MODIFIED,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, att.ID_ATTACH,
att.attachmentType, att.filename, meml.avatar, mf.subject AS firstSubject,
mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName,
mf.posterName) AS firstDisplayName, LEFT(ml.body, 384
) AS lastBody,
LEFT(mf.body, 256) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys ,ba.id_msg as best_answer,
IFNULL(big.ID_ATTACH, 0) AS bigId, big.filename AS bigFilename,
big.width AS bigWidth, big.height AS bigHeight, IFNULL(thn.ID_ATTACH,
0
) AS thnId, thn.filename AS thnFilename, thn.width AS thnWidth,
thn.height AS thnHeight
FROM (smf_topics AS t, smf_messages AS ml, smf_messages AS mf)
LEFT JOIN smf_attachments AS big ON (big.ID_MSG = mf.ID_MSG
AND big.attachmentType = 0
)
LEFT JOIN smf_attachments AS thn ON (thn.ID_ATTACH = big.ID_THUMB)
LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN smf_members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)
LEFT JOIN smf_best_answer AS ba ON t.ID_TOPIC = ba.ID_TOPIC
LEFT JOIN smf_attachments AS att ON (att.ID_MEMBER = meml.ID_MEMBER)
WHERE t.ID_BOARD = 123
AND ml.ID_MSG = t.ID_LAST_MSG
AND ( 1 > 0
OR memf.ID_MEMBER = 0
OR t.isSticky = 1
)
AND mf.ID_MSG = t.ID_FIRST_MSG
GROUP BY t.ID_TOPIC
ORDER BY isSticky DESC, ID_LAST_MSG DESC
LIMIT 0, 10
Don't use LEFT unless you need it.
Let's see CREATE TABLE and EXPLAIN.
OR is hard to optimize.
Don't use "comma-JOIN", use explicit JOIN..ON instead. (In making this change, you may discover a bug.)
After those items, if there is still a performance issue, we can discuss "late row lookup" to avoid gathering all the data before getting to the ORDER BY and LIMIT.