New Posts / Get Daily Optimization

[Freddie Bingham]

Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

ORDER BY lastpost DESC

Above this, place:

AND sticky IN(0,1)

For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.

[Boofo]

Will this work on RC3 for now? Once 3.0.2 gets released, I plan on upgrading (that is if 3.0.3 isn't just around the corner). ;)

[Freddie Bingham]

Will this work on RC3 for now? Once 3.0.2 gets released, I plan on upgrading (that is if 3.0.3 isn't just around the corner). ;)
It will work on any version of 3.0 that you have access to.

[SnowBot]

This is nice of you to post :)

I think vBulletin should tell the users now what mods they have made to the files as most people have hacked the boards and dont want to rehack everything once an upgrade comes out. There cant be that many things to do and most people who have hacked there boards can make the file edits with the new 3.0.2 corrections.

food for thought :)

[Freddie Bingham]

This is nice of you to post :)

I think vBulletin should tell the users now what mods they have made to the files as most people have hacked the boards and dont want to rehack everything once an upgrade comes out. There cant be that many things to do and most people who have hacked there boards can make the file edits with the new 3.0.2 corrections.

food for thought :)
Our policies are not for discussion here.

[Boofo]

It will work on any version of 3.0 that you have access to.
Thank you, Freddie. ;)

This is already added to 3.0.2, right?

[P.Jackson]

no doubt :)

[the Sandman]

Thanks Freddie!

[colicab-d]

is 3.0.2 out? Ive not seen it

[the Sandman]

It's not out... yet...

[sabret00the]

nice work, that's a major optimisation right there :)

[nuno]

Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

ORDER BY lastpost DESC

Above this, place:

AND sticky IN(0,1)

For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.

God bless you, dear!

[MindTrix]

Nice little addition thanks

[Vega]

Nice one! :)

Thx.

[Cold Steel]

Installed - thanks!

[Freddie Bingham]

is 3.0.2 out? Ive not seen it
3.0.2 is not out yet but I assumed that this little change would be helpful now.

[SnowBot]

Our policies are not for discussion here.
I didnt say i wanted to :P I just stated my views.

[PET]

uhh...i just upgrade to 3.0.1 and now i hear 3.0.2 :(

[Oblivion Knight]

Thanks Freddie.. :)

[tamarian]

My "check daily" went from about 1 second to about 0.1 seconds!!

Well done, installed. :)

[mtha]

Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

ORDER BY lastpost DESC

Above this, place:

AND sticky IN(0,1)

For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.
sounds good, but ... could you explain for me how this supposed to work? all threads has sticky value of (0,1), isnt it?

I dont see the exclussion in this condition :(

[neocorteqz]

always the small things that make a big impact. :)

Thanks.

[tamarian]

sounds good, but ... could you explain for me how this supposed to work? all threads has sticky value of (0,1), isnt it?

I dont see the exclussion in this condition :(
I'm not sure either, but I suspect that it has to do with some MySQL indexing row-scan quirk, that makes this trick possible.

But I'd love to hear more from Freddie on this :)

[MugenSi00]

thanks

[thakikka]

vb3 rc4:
"Parse error: parse error, unexpected $ in /www/htdocs/********/search.php on line 1658"

this is line 1654 to 1668:

// remove all ids from $orderedids that do not exist in $remaining
$orderedids = array_intersect($orderedids, $remaining);
unset($remaining);

// rebuild the $orderedids array so keys go from 0 to n with no gaps
$orderedids = array_merge($orderedids, array());

// count the number of items
$numitems = sizeof($orderedids);

// do we still have some results?
if ($numitems == 0)
{
eval(print_standard_error('searchnoresults', 1, 0));
}

[Erwin]

Interesting - my method to limit the load was to modify the query so that it is forced to only scan the past 100 posts posted only. According to the mysql logs, that only made the query scan 100 rows. But I will try this out for sure. :)

[Andreas]

I dont see the exclussion in this condition :(
You are right that the condition itself doesn't make sense als all threads are stiicky or not ;)

But: Table thread has an index called forumid which is built from the colums forumid, visible, sticky and lastpost.

Three of them (forumid, visible and lastpost) are already conditions of that particular query, so if a condition for column sticky is added mySQL uses this index which dramatically reduces the time to execute the query.

@thakikka
The code excerpt you posted seems just fine, so the error must be somewhere above.

[Erwin]

It definitely sped things up. Thanks, Freddie!

[mtha]

You are right that the condition itself doesn't make sense als all threads are stiicky or not ;)

But: Table thread has an index called forumid which is built from the colums forumid, visible, sticky and lastpost.

Three of them (forumid, visible and lastpost) are already conditions of that particular query, so if a condition for column sticky is added mySQL uses this index which dramatically reduces the time to execute the query.

@thakikka
The code excerpt you posted seems just fine, so the error must be somewhere above.
Thats cool, thanks

[noppid]

This should be listed as a bug if it's bringing servers to their knees. Nice try trying to hide it and offer it as a hack. It should have been announced at .com.

[tamarian]

This should be listed as a bug if it's bringing servers to their knees.
What is bringing your server to it's knees? I'm using it on 2 million+ forum with no problems.

[Freddie Bingham]

This should be listed as a bug if it's bringing servers to their knees. Nice try trying to hide it and offer it as a hack. It should have been announced at .com.
If you are going to have that attitude then I don't need to bother posting things like this but if it makes you happy I can post it in the bug tracker (http://www.vbulletin.com/forum/bugs.php?do=view&bugid=3054). I'm sorry that you feel I'm trying to "hide" something. Perhaps if that was my goal then I would have just updated the code and not posted here at all? Doesn't that seem to make more sense?

[P.Jackson]

well whatever ;) thanks for releasing it here freddie im sure alot of people appriciate it including me :)

[Xenon]

nice one Freddie :)

that shows the little hints can boost MySQL :)

[the Sandman]

IMO Freddie's first post was accurate and straightforward - it's an optimization for vB3 from the upcoming 3.0.2 release. Since adding it involves editing a file, and it's not a bug, this seems to be the appropriate place to release it. Of course, there are other possible ways to look at it, but the majority here seem to appreciate it as do I. Thanks again Freddie!

Any other tidbits you can throw our way? :D

[WoodiE]

Funny - my speeds increased with this hack.

getnew went from .02 to .03

getdaily went from .05 to .07

and my get unanswered threads went from .30 to .44

-Michael

[Freddie Bingham]

Funny - my speeds increased with this hack.

getnew went from .02 to .03

getdaily went from .05 to .07

and my get unanswered threads went from .30 to .44



-Michael
WoodiE that isn't possible unless you ran an unscientific test or you are missing the index on the thread table.

[Alien]

WoodiE that isn't possible unless you ran an unscientific test or you are missing the index on the thread table.
Kick ass. Thanks a LOT, Freddie! Doing nicely...

[vbmechanic]

Ignore the naysayers... THANK YOU for releasing this early; makes a huge difference on sites with 1 mil+ posts.

[Princeton]

thanks Freddie ...

optimization hacks/tip = :D:):up:

[PranK]

Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

Awesome - thanks!! :D

[Logician]

This hack is VERY VERY effective for a large board. Check the screenshots for the difference it makes. Thx for sharing it!

[Xenon]

actually i think it's just a bug in the mysql query optimizer.

with the added code it is using the index, without it doesn't use it.
but as there are just the 0,1 as values, the optimizer should always use the index in my eyes.

but i'm sure they might have had an reason for this (or not ;))

[Mijae]

Pretty nice :D 0.02 seconds instead of 0.05 :P

[neocorteqz]

This hack is VERY VERY effective for a large board. Check the screenshots for the difference it makes. Thx for sharing it!
Damn thats nice.

[Oblivion Knight]

Will this work on RC3 for now? Once 3.0.2 gets released, I plan on upgrading (that is if 3.0.3 isn't just around the corner). ;)That's just scary. You predicted something before it happened.. :paranoid:

*ahem* Sorry for going off-topic.

[Polo]

Cool, this has been added in version 3.0.3 :)

[YabbaDabba]

Get New went from 0.26 to 0.02

Thanks!

**clicks install**

[ImportPassion]

Cool, this has been added in version 3.0.3 :)
what he said

[ImportPassion]

just answered my own question. and the answer is yes

[theArchitect]

Installed - thanks! Many thanks for your work.

*theArchitect is glad it came as standard in 3.0.3*

[The Coldwood]

It will work on any version of 3.0 that you have access to.
It was automatically fixed in 3.0.6

[nintendo]

It was automatically fixed in 3.0.6

So does 3.0.6 allready have this installed??

[Xenon]

yep!

[Corriewf]

Nice!