Reset Password · YAF.NET Support Forum

Welcome Guest! To enable all features please Login or Register

Posted by: vitop - Wednesday, 20 April 2011 21:46:03
Hello, 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.

Posted by: vitop - Wednesday, 20 April 2011 21:46:03

Hello, 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.

Posted by: bbobb - Wednesday, 20 April 2011 22:04:25
[quote=vitop;49453] 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. [/quote] 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:

Posted by: bbobb - Wednesday, 20 April 2011 22:04:25

[quote=vitop;49453] 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. [/quote] 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:

Posted by: vitop - Wednesday, 20 April 2011 22:21:12
Thanks for the info bbobb. 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." [url=http://msdn.microsoft.com/en-us/library/ms142545(v=SQL.90).aspx]http://msdn.microsoft.com/en-us/library/ms142545(v=SQL.90).aspx[/url] 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)?

Posted by: vitop - Wednesday, 20 April 2011 22:21:12

Thanks for the info bbobb. 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." [url=http://msdn.microsoft.com/en-us/library/ms142545(v=SQL.90).aspx]http://msdn.microsoft.com/en-us/library/ms142545(v=SQL.90).aspx[/url] 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)?

Important Information: