What is the difference of having full-text search

Welcome Guest! To enable all features please Login or Register

neo6053
53% (Neutral)
YAF Forumling Topic Starter

14 years ago

I was migrating my db to other server. Somehow, i was unable to migrate the full-text search. Can anyone tell me how to recreate it.

Btw, i've disable the function of full-text search. What is the benefits of having it? I still can search for words inside the thread. What is the difference?

Wanna join the discussion?! Login to your YAF.NET Support Forum forum account or Register a new forum account

Sponsor

centur
58.4% (Neutral)
YAF Camper

14 years ago

if you have source а yaf - there is a file

\yafsrc\YetAnotherForum.NET\install\mssql\fulltext.sql

If you dont - here the entire file content :


-- Enables FULLTEXT support for YAF
-- Must be MANUALLY run against the YAF DB

if (select fulltextserviceproperty('IsFulltextInstalled'))=1 and (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
	exec sp_fulltext_database N'enable' 
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) = 1
BEGIN
	if not exists (select * from dbo.sysfulltextcatalogs where name = N'YafSearch')
	BEGIN
		EXEC sp_fulltext_catalog N'YafSearch', N'create'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'create', N'YafSearch', N'PK_yaf_Message'	
		EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Message]', N'Message', N'add'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'activate' 
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_change_tracking'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_background_updateindex'

		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'create', N'YafSearch', N'PK_yaf_Topic'
		EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Topic]', N'Topic', N'add'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'activate' 
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_change_tracking'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_background_updateindex'
		
		-- enable in yaf_Registry as a default
		IF EXISTS ( SELECT 1 FROM [{databaseOwner}].[{objectQualifier}Registry] where [Name] = N'usefulltextsearch' )
			UPDATE [{databaseOwner}].[{objectQualifier}Registry] SET [Value] = '1' WHERE [Name] = N'usefulltextsearch'
		ELSE
			INSERT INTO [{databaseOwner}].[{objectQualifier}Registry] ([Name],[Value],[BoardID]) VALUES (N'usefulltextsearch','1',NULL);
	END
END
GO

I think its pretty self-explained.

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

Here the short quote from link above:

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.

The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query (for example, '%cencini%') against millions of rows of text data can take minutes to return; whereas a full-text query (for 'cencini') can take only seconds or less against the same data, depending on the number of rows that are returned.

neo6053
53% (Neutral)
YAF Forumling Topic Starter

14 years ago

thank you very much. i have actually run that sql already. but it gives me error

Msg 15009, Level 16, State 1, Procedure sp_fulltext_table, Line 39

The object '[{databaseOwner}].[{objectQualifier}Message]' does not exist in database 'Forum_DB' or is invalid for this operation.

Msg 15009, Level 16, State 1, Procedure sp_fulltext_table, Line 39

The object '[{databaseOwner}].[{objectQualifier}Topic]' does not exist in database 'Forum_DB' or is invalid for this operation.

Thank you for the infor of full-text search too.

centur
58.4% (Neutral)
YAF Camper

14 years ago

aww, I didnt noticed that...

Replace {databaseOwner} token on your Config.DatabaseOwner ("YAF.DatabaseOwner" from config or use "dbo" if not specified) and {objectQualifier} token on Config.DatabaseObjectQualifier ("YAF.DatabaseObjectQualifier" or use "yaf_")

manually. And run script again 🙂

Important Information: