MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can this be optimized any futher?
Posted by: Rick James
Date: February 20, 2010 11:26PM

SELECT  vht.Video_VideoID AS VideoID
    FROM  Account_has_Tag AS aht
    JOIN  Video_has_Tag AS vht ON vht.Tag_TagID = aht.Tag_TagID
    LEFT JOIN  VideoHistory AS h ON vht.Video_VideoID = h.Video_VideoID
      AND    h.Account_AccountID = 53
    WHERE  aht.Account_AccountID = 53
      AND  ( h.TS < DATE_SUB( NOW( ) , INTERVAL 3 DAY )
         OR  h.TS IS NULL )
    GROUP BY  vht.Video_VideoID
    ORDER BY  SUM( vht.Weight * aht.Weight ) DESC
    LIMIT  50

The OR prevents use of h.TS index. Anyway, you have declared it to be NOT NULL, so simply remove the "OR h.TS IS NULL" clause. Once you do that, add
INDEX(Account_AccountID, TS)

Why is it LEFT JOIN instead of JOIN?

The complex ORDER BY requires that all rows be evaluated; that is, no shortcut can be taken for the "LIMIT 50".

If you are using MyISAM, this is redundant with the PRIMARY KEY:
KEY `Account_AccountID` (`Account_AccountID`)
Ditto of for the other single-field keys.

After adding that index, see if this works faster:
SELECT  vht.Video_VideoID AS VideoID
    FROM  Account_has_Tag AS aht,
          Video_has_Tag AS vht,
          VideoHistory AS h,
    WHERE vht.Tag_TagID = aht.Tag_TagID
      AND h.Video_VideoID = vht.Video_VideoID
      AND aht.Account_AccountID = 53
      AND h.Account_AccountID = 53
      AND h.TS < DATE_SUB( NOW( ) , INTERVAL 3 DAY )
    GROUP BY  vht.Video_VideoID
    ORDER BY  SUM( vht.Weight * aht.Weight ) DESC
    LIMIT  50

Please provide
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

Options: ReplyQuote


Subject
Views
Written By
Posted
2567
February 16, 2010 02:44PM
Re: Can this be optimized any futher?
1429
February 20, 2010 11:26PM


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.