I successfully installed YAF 1.9.301 a while ago, and it is working great, but now I am in a bit of trouble with the search capability.

The forum is getting quite large, and searches are killing the database, and I am sure that users are not too happy about the performance.

Pruning is not an option.

I was thinking about implementing a full-text search, and I found your fulltext.sql in the installation folder, but I am a newbie when it comes to full-text search, so I have some questions.

Forum DB is running on SQL Server 2005, and my online research shows that SQL Server 2005 does not support full-text searches on text/ntext types.

I am OK with changing the yaf_message.message to nvarchar(max), but is that enough to make the full-text search work?

I could not find anything online that indicates that types could be a problem when implementing a full-text search in YAF.

Am I missing a step?

Also, if anyone has more experience with the full-text searches, what am I looking at in terms of disk space for a full-text index for roughly 500K rows in yaf_message (about 500MB of data)?

Any help is greatly appreciated.

Technically if you have HTML code to search, but you should store it in IMAGE field. But it involes an overhead when you're converting data. But if you have a plain text you can use NTEXT/TEXT. Really MS SQL is a looser when it comes to FTS.:cry:

I am using BBCode editor, so it is not exactly HTML that I am storing in there.

Would that still pose problems (in conjunction with ntext fields)?

Microsoft site says: "You can build full-text indexes on columns that contain char, varchar and nvarchar data."

http://msdn.microsoft.com/en-us/library/ms142545(v=SQL.90).aspx 

Do you know of any sites successfully running FTS on MS SQL 2005 in default YAF configuration (i.e. where yaf_message.message field is of ntext type)?