I'm waiting for kmike's version - why reinvent the wheel? :)

kmike, did you just use the vB2 code? Coz if you did, I can try to modify it to fit too.

Here is some excellent info that everyone should look at.

http://dev.mysql.com/doc/mysql/en/Fu...ne-tuning.html

My database is fine-tuned already. No matter how much you fine-tune, if your database is big, a LEFT JOIN is a bad query to have.

wasn't meant to be about that. About changing the minimum and maximum word length and other stuff like the stop word list.

Ahhh... thanks. I see now. :) Interesting... do you have any particular settings that work for you?

kmike, do post your code once you have them ready. :)

If you want to remove left joins together with revelance why not to say just
? :)
or in one string

Just wanted to say that we installed this hack on our 950,000 post VB3 board with good results so far. Searches were slow with relevance enabled so I disabled it and searches seem snappy.

Yes why not?! <Slaps JohnWoo> :) Works great, even faster now.

Still want to say few words for left joins :)
Lets say that you have large board with many forums, usergroups and complex access level. After it lets suppose that somebody with common access level (just registered) will search for some common phrase (for example "+javascript +flash").
On one board, where i tested it, such search with no left joins return nothing because fulltext query find first 200 matches in forums invisible to beginner members and stop on it. Showresult page (looking on current member access level) hide all that 200 matches from search result and with great enthusiasm say that nothing found :) On same forum search with left joins return complete set of 200 matches (from other forums visible to beginner members) for same search phrase and may return more if "maxresult" will be set to larger number.
But if your forum access level structure is not too complex, you may remove left join part without visible consequence :)

Awesome, John Woo. Your new query has fixed my slow_query/ mysql locking/ apache crashing problem (as far as I can tell with testing).

I'm using the first code example, just because the original code is like that. :) Thanks!

Even without the LEFT JOIN, certain phrases do make the query go into my slow_query log, but it's only around 40 seconds now. :) Much better.

Another question for those with this hack - when I use the Advanced Search page, and look for all posts by username, it works.

But when I look for all threads started by username (from the dropdown menu under the box where you put the username) only really old threads come up.

Does this happen to you too? Any ideas?

Sorry to ask another question - does Search Thread work for you guys? My members are saying it searches the whole site instead of just the thread.

i can confirm this because i did post about it earlier
but nothing has been done as of yet

Kmike, can you post that templates?

and JohnWoo, maybe make a new release whit Template MOD too, and left join together.

Please this project dont have to die, is really nice...come on :D

This is the best thing that's come out for large vB sites. :)

yeah...i know!
i have a 3 Gigabyte Database...insall this hack and reduce to 800 Mg

But something i have to close search for a few day, becauso all forum goind down. I think that the hack need a little more work, but i dont know where. :P

As some may know, Search Thread does NOT work with this hack installed.

Here is my fix:

Open search.php:

[Removed code - I've made a new piece of code to replace this one that also makes Search Individual Forums work too - refer to post below.]

The post is back! I made it better! ;) I decided to share my optimization!

problem somewhere

Database error in vBulletin 3.0.1:

Invalid SQL: SELECT postid FROM post WHERE MATCH (post.pagetext) AGAINST ('hood) LIMIT 0, 500 mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''hood) LIMIT 0, 500' at line 1

mysql error number: 1064

Are you sure you copied that line as it is? I just cut and pasted that line EXACTLY AS IT IS POSTED THERE just to test it, and it works on my site.

i have +8million lines and 165 megs committed to postindex with +500k posts

i've felt searching drags my server so much, would this be a better alternative?

Erwin: I believe there's an error in your pasted query here:
AGAINST ('$query".$isboolean.")
unmatched single quota. It should be:
AGAINST ('$query' ".$isboolean.")

Erwin,

Yes copied exactly.

I thik there is a single quote missing here

Nope, that doesn't work on my site.

The code that I've posted works as it is - don't ask me why. You might have to modify it for your site.

yup, i added the single quote and it works no prob, and fast

Showing results 1 to 25 of 342
Search took 0.06 seconds.

it's extremely weird that it works on your site. it's invalid SQL.

It just means there's matching single quote added either at the end of $query variable or at the beginning of $isboolean somewhere.

Thanks for update!

[high]* cerebro install :D[/high]

Nice mod...really nice, help a lot. Thanks Erwin for Sharing.

What d u think about stats cache...for optimize forum home.

https://vborg.vbsupport.ru/showthread.php?t=61581

some one install this hack and bollean search?

Okay, I've made the hack work for "Search Forum" now as well as "Search Thread" - before, "Search individual forums" did not work.

I've also cleaned up the code a lot more. Plus optimized the search query by removing the JOIN function when it is not required (unlike before when you had a JOIN query for every search for no real reason). I also removed relevance and other bits that slowed the search down and increased server load. Straight searching should be faster with this.

Remove my previous codes.

Then, open search.php and replace the line beginning with:

$fulltext_sql =

with this:

Some people have had problems with this:

('$query".$isboolean.")

It works for me, but some others have had to modify this bit by removing or adding quotation marks - not sure why.

Hope it works for you! It's working well for me - Search Threads and Search Forums are now working well finally! Plus search is optimized even more!

Note, when I said removed my previous code above that applies only to people who have applied my prevuous Search Thread bug fix.

When I say the query is optimized even more, it's optimized compared to the original fulltext_sql query - it's basically similar to my previous fix, but written slightly different.

Just to clarify. :)

so, erwin... when will we see a final version of your own ?!.... it looks promissing, but we don't have the entire package... ;)

hmm weird i'm sure search relevancy was fixed for 2.2

i can search within forums and threads etc without those modifications

I've only modified the code for my site slightly to remove show results as threads, so all my results are shown as posts, as that's less server intensive than to have to join the threads in.

ya, i don't get it cause it's working fine for me too.

Weird, because it didn't work for me until I made that mod. I am talking about the search form at the top of a forum. The search forum on the Advance Search page does work. It's just the search form at the top of forums that didn't work for me.

in 2.2 they all seem to be working for me now then again i uninstalled the hack

and when i saw the advanced you made i reinstalled the hack but i don't get it everything seems to be running in order now without modifying extra

do you have similar threads on by any chance because i don't :P

No, I never had similar threads on.

Some one have some upgrate or news about new version? jejeje
My board increise a lot...have to change something..CPU is on TOP! :P

The next update of vB3 may have this - not sure.

It is in 3.02 but not supported so the devs won't say how to enable it in 3.0.2. :(