Search Limite

[David Bott]

Hello...

Please help if you can. I run AVS Forum, a very large Vb Site. Currently we have over 1.5 million on-line posts and I am getting cruised by large search results.

What I am looking for is someone who can hack the search.php so it will only return say the 100 most recent threads that has a persons search request.

I ask this because some of these searches are returning results in the 20,000 range. The issue is it then locked the tables when it is making this large return. So my hope is by not returning so much...I can cut down on the lock time and help the site.

Thoughts?

Thank you for your time,

David Bott
AVS Forum Admin
http://www.avsforum.com

[Logician]

edit search.php, find:


$posts=$DB_site->query("SELECT postid,wordid FROM searchindex WHERE $andor $intitle");


Replace it as:


$posts=$DB_site->query("SELECT postid,wordid FROM searchindex WHERE $andor $intitle ORDER BY postid DESC LIMIT 100");


This should work for what you wanted to do.. However 1 reservation:
This will force vb to return only 100 (recent) posts for every search. But please notice that this is 100 posts not 100 threads. So if someone searches for "posts" and he will be displayed 100 results. But if he searches for "threads" (which is vb default as you know) he might be returned less than 100 results depending on how many threads have more than 1 posts with that keyword.

Say if you search "sport" and choose "show as threads" and if you have a recent thread with 450 posts and 300 of them include that keyword, search will return only 1 result which is this thread.

If you apply the hack, you'll see better what I mean..

I didnt check it, but I guess the hack applies only applies to "regular searchs".. Today's new post searches or member searches shouldnt be affected..

[David Bott]

Thank you so much for your time. I will give this a try right away!!!

[David Bott]

ok...Seems to work just like you had said. Seeing it returns the top "posts" I have now made the limit 500 for the system surely should have no problem with that number vs returning 19,000. ;)

We will now see and THANK YOU!

[David Bott]

Hi...Me again...Sorry...

I would like to see this maybe more developed down the road. What I mean is...where the default is say maybe 100 but the user may select to go further back in time thus setting their own return if needed. Even the default will help the server greatly in just saving large returns when not needed.

Just a thought...But I do greatly thank you again for your help.

[Logician]

You can edit your search template and put a HTML code like this:


<select name="returnno">
<option selected>100</option>
<option>200</option>
<option>300</option>
<option>400</option>
<option>unnamed1</option>
</select>


Then find:

// ###################### Start getallforumsql #######################
And before that add:

$search_default_number=(int)$HTTP_POST_VARS['returnno'];
if ($search_default_number<=0 OR !$search_default_number)
{$search_default_number=100;}


Then in the hack above replace your return result number with $search_default_number like:

$posts=$DB_site->query("SELECT postid,wordid FROM searchindex WHERE $andor $intitle ORDER BY postid DESC LIMIT $search_default_number");


Not tested but should work..

Enjoy ;)

[David Bott]

Oh WOW!!!!! THis will be wonderful! Will get it install and see how it goes! THANK YOU!!!

[David Bott]

Ok...It all seems to work. But I have one problem.

What if I want them to be able to look though the entire database? I made one setting called ALL give it a value of 20000 and the server really does not seem to like that. :) I used that vaule for the test I was doing before returned 19,792 posts.

If I do the serach wihtout the hack...it returns a lot quicker with the 19,792 results then with it set at a LIMIT of 20000.

But let me tell you...this is GREATLY helped the server!!!!!

Pondering.

[Logician]

David it's not the "LIMIT" that causes the performance loss, it's the "ORDER BY postid" command. I had to insert it so that the result returned will be sorted from the recent to the old. However searchindex table has an index on wordid not postid column and it's very large so our SQL command puts a burden on the db. The solution is easy: just create a index on postid column too and you'll be fine..

[David Bott]

Hi...We need to talk about the serach thing. It seems to be affecting results somehow. Do you use AIM or ICQ? If so...can you please be so kind to PM me contact info so we may be able to work on this live.

THANKS!