Manual Search on words under the minimum word length

[DarkReaper]

The way vB currently works, it complies a list of all the words used that are longer than the minimum word length, and links them to all the posts they're used in. This is great for speed, but what about if you want to allow searching of words with 3, 2, or even 1(who does this? ;)) letter? It'd be nice if it could "manually" go through all the posts and search for the word, letting the user know it would take more time because of the low word length...

[bira]

my post table has over 630,000 posts and in total is some hundreds of mb big.

A few users running such a search as you suggest will at best exceed the max_execution time for PHP, and at worse crash my MySQL :/

[eva2000]

current i have my searchindex set at

min: 3
max: 17

lowered it from the defaults of 4 and 20 i believe

post table size = 513,049 = 189.1 MB
search index size = 9,071,044 = 212.6 MB
word table size = 473,851 = 36.6 MB

total database size is 652MB right now

[bira]

I'm around about the same ballpark as you, Eva2000. And I have my search word lengh set at 3 - 15

[JamesUS]

It's a good idea in principle - however it's not just speed we're worried about. Searches put greater load on MySQL and the server, and a manual search like what you are suggesting might well put a huge load upon the server (depending on the server).

[DarkReaper]

Is there any other way to do it then?

[bira]

I vaguely remember Ed Sullivan mentioning that MySQL is supposed to add .... something .... to their next version which will allow easy search. Gosh, I don't remember the details. I'll do a search - maybe I'll find what he said.

[bira]

A-ha, found it :D


I have a version of search.php that uses MySQL's built in full text search. It's amazing... sometimes.

The test board was Me2Be's board, which is a Dual PIII 900 or so with 1GB of RAM. Full text index on (title,pagetext) on the post table containing 400,000 posts. Query to add full text index took 30 minutes to run.

Search returning 138 posts takes .02 seconds to run.
Search returning 100,000 posts takes 18.59 seconds to run.

"What kind of search would return 100,000 posts?"
A lot more than you think because full text search doesn't support boolean operators.
A search for "vbulletin forum" will *always* return *any* post containing vbulletin or forum.

Until MySQL 4.0, which supports boolean operators, I highly doubt you'll see a good version of the full text search integrated into vB.


So there's your answer, I guess :)

http://vbulletin.com/forum/showthread.php?s=&postid=73131&highlight=full+text+search+MySQL#post73131

[DarkReaper]

That was in march...any idea when MySQL 4.0 is coming out, or if any progress has been made on this?

[bira]

MySQL 4 is currently in alpha stage, according to their website. So I suspect we're looking at a year long before it becomes not only stable, but also production-wide for many vB owners. After all, you don't only need to wait until it's a stable release (and that will take ages, cos there's loads of bugs atm) - but you also need to wait for it to become the norm among hosts before you release a new search engine that is based on it.

So in short, I wouldn't count on it for the foreseeable future :/

[JamesUS]

Fulltext search is currently in MySQL 3 in a limited form - so in theory we could use some of it now.

However we will probably end up changing our own search system around because the fulltext search does have some limitations and problems.

[DarkReaper]

Any idea how the new system will work?