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?