Howto : Enable FullText Searching in vBulletin 3.0.2/3

[Zachery]

Originally Posted by mickmel

I've re-uploaded all of the files in the "admincp" directory, but I still don't have the fulltext option in there. Where is a setting like that stored?

When you upgraded you made sure all the files were 3.0.3 and ran the upgrade scripts? If you dont see that option its telling me that the new vboptions.xml didnt get uploaded before you finished the upgrade.

[mickmel]

Originally Posted by Zachery

When you upgraded you made sure all the files were 3.0.3 and ran the upgrade scripts? If you dont see that option its telling me that the new vboptions.xml didnt get uploaded before you finished the upgrade.

I'm not sure what happened. The last few upgrades went successfully. The problem was a missing entry in the options table (I think that's the name). Anyhow, it's working now.

Next problem...

I'm having problems getting one of the ALTER commands to work. After failing with the "post" one a few times, I tried the "thread" one. It completed successfully in just over 4 seconds. I'm running these queries via SSH.

I've run the "post" ALTER command a few times and it's never able to finish. I've repaired the table (no errors), optimized the table and flushed the index. It still won't finish. I might just be impatient, but I let it go for an hour and it hadn't finished. No error message, just no result. The thread table has about 78,000 records in it, the post table has about 1.3 million records in it.

The other strange thing is that my server load while it's working is almost nothing. It's at most 0.05. I would expect that if it was working that hard on the post index it would be putting more strain on the server.

So, the questions:

- Is a one hour wait for this a problem or am I just impatient? Given that the thread table takes 4 seconds and the post table has about 15 times more records, it should only take about 60 seconds, right?
- Is there a way to simplify the query into a few separate ones so that I can run them one at a time?
- Anything else I can do to optimize the table so this might run faster or finish?

Thanks,
Mickey

[Zachery]

ALTER commands usualy take a while, and I am not running this on my own board anymore so i cannot comment too much on it, I hope someone who has abit more knowleged about this can respond.

[mickmel]

In the hopes of speeding it up, I've cut the post ALTER command down a bit. Instead of:

ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)

I'm just doing

ALTER TABLE post ADD FULLTEXT INDEX (title)

Assuming it works, I'll do the pagetext as a separate query as well. Right now we're at 40 minutes and no sign of finishing.

The only reason I'm concerned about it taking so long is that the thread table completed so quickly.

I'll let it go a while longer and see what happens...

Mickey

[Zachery]

The post talbe is quite abit larger... have you configured mysql's setting since installing mysql? an unoptmized my.cnf/ini can be horrid for running larger querys like the alter querys.

[mickmel]

Originally Posted by Zachery

The post talbe is quite abit larger... have you configured mysql's setting since installing mysql? an unoptmized my.cnf/ini can be horrid for running larger querys like the alter querys.

Yep. It's optimized and it's a fast server (dual Xeon, 2 gigs RAM, SCSI). We're at 2 hours now and it'll still working on it. This can't be right...

[mickmel]

Ok, I did a bit more digging and I might have found part of the problem. I was looking at the table in PHPMyAdmin, and found that it has a ton of indexes on it. Here is what it has...

Keyname  	Type  		Cardinality  	Action  	Field
PRIMARY 	PRIMARY 	1526312  	Drop 	Edit 	postid
dateline 	INDEX 		1526312  	Drop 	Edit 	dateline
iconid 		INDEX 		15  		Drop 	Edit 	iconid
userid 		INDEX 		11924  		Drop 	Edit 	userid
threadid 	INDEX 		508770  	Drop 	Edit 	threadid
								userid
title 		FULLTEXT 	508770  	Drop 	Edit 	title
								pagetext
title_2 	FULLTEXT 	1526312  	Drop 	Edit 	title
								pagetext
title_3 	FULLTEXT 	508770  	Drop 	Edit 	title
								pagetext
title_4 	FULLTEXT 	1526312  	Drop 	Edit 	title
								pagetext
title_5 	FULLTEXT 	508770  	Drop 	Edit 	title
								pagetext
title_6 	FULLTEXT 	1526312  	Drop 	Edit 	title
								pagetext
title_7 	FULLTEXT 	508770  	Drop 	Edit 	title

There are 1,526,312 rows in that table, so the ones that show that appear to be done. Should I drop all of the title indexes except for one that has all of the records (like title_2)? Or should I drop all of them and try to reindex?

[Killsparer]

Originally Posted by buro9

Is it me or does the "Find In Thread" functionality fail with this turned on? Is there a quick fix to this?

Same problem. Any way to fix this?

[buro9]

Originally Posted by Killsparer

Same problem. Any way to fix this?

This is now all working in vb 3.0.5

Congrats to all for their hard work, it is VERY appreciated.

[buro9]

Does anyone know... are Similar Threads driven by the fulltext search too?

i.e. If I purge my word index tables, will I break anything now that most of this appears to be working?