Slow queries in this command, how to fix it?

[Mattwhf]

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?

[noypiscripter]

Is that for vB5? That seems to be for older version. vB5 has no thread table. Either way, you have to ask the plugin author.

What is the query supposed to do, btw?

[Mattwhf]

Originally Posted by noypiscripter

Is that for vB5? That seems to be for older version. vB5 has no thread table. Either way, you have to ask the plugin author.

What is the query supposed to do, btw?

You are right, I wanted to post to this forum https://vborg.vbsupport.ru/forumdisplay.php?f=252

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.

[Paul M]

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.

[Dave]

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.

[cellarius]

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.