vb.org Archive - New Posts / Get Daily Optimization

Page 3 of 6

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vBulletin 3.0 Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=33)

- - New Posts / Get Daily Optimization (https://vborg.vbsupport.ru/showthread.php?t=66578)

mtha	06-27-2004 06:15 PM

Quote:

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

06-27-2004 06:26 PM

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

Thanks.

tamarian

06-27-2004 06:37 PM

Quote:

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

06-27-2004 11:52 PM

thanks

thakikka

06-28-2004 04:36 AM

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

06-28-2004 05:55 AM

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

06-28-2004 05:57 AM

Quote:

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

06-28-2004 07:50 AM

It definitely sped things up. Thanks, Freddie!

mtha	06-28-2004 12:18 PM

Quote:

Originally Posted by KirbyDE

Thats cool, thanks

noppid

06-28-2004 05:29 PM

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.

All times are GMT. The time now is 09:53 AM.

Page 3 of 6

Last »

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01321 seconds Memory Usage 1,748KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: