The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
Slow queries in this command, how to fix it?
Hello,
I read the log file and it noticed me this Mysql command is making my forum slow, with more than 15 seconds to query it. I found it in an addon that I am using on my forum $rcthrds = $vbulletin->db->query_read(" SELECT thread.threadid, thread.title, thread.lastpost, thread.lastpostid, thread.forumid, thread.lastposter, thread.lastposterid, thread.postusername, thread.dateline, thread.views, thread.replycount, thread.visible, thread.open, thread.prefixid, forum.title AS forumtitle, user.username, user.userid, user.usergroupid, user.displaygroupid, user.avatarid, user2.avatarid AS avatarid2 " . iif($vbulletin->options['threadpreview'], ",post.pagetext AS preview") . " " . iif($vbulletin->options['avatarenabled'], ',avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline') . " " . iif($vbulletin->options['avatarenabled'], ',avatar2.avatarpath AS avatarpath2, NOT ISNULL(customavatar2.userid) AS hascustomavatar2, customavatar2.dateline AS avatardateline2') . " FROM " . TABLE_PREFIX . "thread AS thread LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.username = thread.postusername) LEFT JOIN " . TABLE_PREFIX . "user AS user2 ON (user2.username = thread.lastposter) INNER JOIN " . TABLE_PREFIX . "forum AS forum ON (forum.forumid = thread.forumid) " . iif($vbulletin->options['threadpreview'], "LEFT JOIN " . TABLE_PREFIX . "post AS post ON (post.postid = thread.firstpostid)") . " " . iif($vbulletin->options['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . " " . iif($vbulletin->options['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar2 ON(avatar2.avatarid = user2.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar2 ON(customavatar2.userid = user2.userid)") . " WHERE NOT ISNULL(thread.threadid) AND thread.visible = '1' AND thread.open!='10' $forumfilter ORDER BY rand(), lastpost DESC LIMIT 0, {$vbulletin->options['rcmt_reclimit']} "); I think this command ORDER BY rand(), lastpost DESC is the reason caused slow queries on my forum. How to fix this? |
#3
|
|||
|
|||
Quote:
Don't know why I posted here. Mod please moves it to vB4 Programming Discussions. Thanks In the codes above I doubted this line causes error slow query ORDER BY rand(), lastpost DESC It will choose a random thread in all forums to show on the box. I think it made my apache (httpd) down and Mysql server run slowly. |
#4
|
||||
|
||||
Yep, that will probably be slow as hell on a forum with loads of threads.
It wont directly bring apache down, although if you have loads of visitors and everyone is running that query, and taking that long, its going to have some serious effects, and you will likely run out of resources for httpd to run properly (and mysql if its on the same box). The lastpost in the Order By seems a bit redundant as well. Turn that plugin off. |
#5
|
|||
|
|||
More specifically, the rand() function in the query causes it to be slow.
A "solution" would be to generate the random number in PHP and use that as the threadid in a where statement in the query. The only downside is that the threadid may not exist if it has been deleted. |
#6
|
||||
|
||||
The probability of the id not existing should be comparatively small on an average forum, and you could get around that issue easily. Just run the randomly generated id against the database, and if it does not exist, i.e. the result is empty, start over: generate another one, and so on.
|
Thread Tools | |
Display Modes | |
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|