New Posts / Get Daily Optimization

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:

PHP Code:

ORDER BY lastpost DESC 


Above this, place:

PHP Code:

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.

[mtha]

Originally Posted by 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:

PHP Code:

ORDER BY lastpost DESC 


Above this, place:

PHP Code:

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]

Originally Posted by mtha

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:

PHP Code:

    // 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]

Originally Posted by mtha

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]

Originally Posted by KirbyDE

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.