MySQL Forums
Forum List  »  PHP

Re: Query optimization
Posted by: Rick James
Date: November 26, 2015 03:07PM

    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.

Options: ReplyQuote


Subject
Written By
Posted
November 16, 2015 04:27AM
November 16, 2015 12:05PM
Re: Query optimization
November 26, 2015 03:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.