Can one of you MySQL gurus look at the following query and tell me if there is a way to optimize it a little better? It works fine, but I kind of jerry-rigged it along the way to get what I needed from it.
Code:
$topattach = $vbulletin->db->query_first("
SELECT counter AS aviews, a.attachmentid, a.dateline, a.filename, a.contenttypeid,
post.userid as userid, post.username as username, post.postid AS postid,
thread.threadid AS threadid, thread.title AS threadtitle,
user.usergroupid
FROM " . TABLE_PREFIX . "attachment AS a
LEFT JOIN " . TABLE_PREFIX . "post AS post ON (post.postid = a.contentid)
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (thread.threadid = post.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid = post.userid)
WHERE a.contenttypeid = $contenttypeid
" . iif(!empty($exuserids), "AND user.userid NOT IN ($exuserids)") . "
" . iif(!empty($exgroups), "AND user.usergroupid NOT IN ($exgroups)") . "
ORDER BY aviews
DESC LIMIT 1
05-15-2011, 06:32 AM
Optimizing MySQL Query Help
Linear Mode
