Implemeting full-text search in the catalog system

  • Article

The Product catalog system uses the SQL 2000 fulltext search to implement fulltext searching. The fulltext searchable catalog content is stored in SQL fulltext catalogs. A property in the catalog system has an attribute named IsFreeTextSearchable which when set to true causes the contents of that property in the catalogs it is used to be added to the SQL full text catalog so that its contents can be searchable.

Creation of fulltext catalogs                  

By default if Sql fulltext search is installed on the catalog server then the catalog system will add the relevant catalog tables to the Sql fulltext catalog. Since Sql server restricts creation of only 256 fulltext catalogs per server the catalog system allows you to control the number of fulltext catalogs to create per site, by using the “Maximum number of fulltext catalogs to create“ site property associated with the product catalog resource. If the number of full text catalogs in the catalog system is less than this value then the catalog system will create a fulltext catalog for each product catalog otherwise it will add the catalog tables to an existing fulltext catalog. By setting this value to 0 you can prevent the catalog system from creating fulltext catalogs. Similarly when you import an xml file you can specify whether you want to create fulltext catalogs or not to the CatalogManager.ImportXml method. The fulltext catalogs created by the catalog system do not have change tracking on them. You will have to call the ProductCatalog.RegenerateFreeTextSearchIndex method to update the fulltext catalogs. For each product catalog this method will add the categoryname, displayname and all the freetextsearchable properties in the catalog to the fulltext catalog. Alternatively you can Run the Updatefulltextcatalogs script to rebuild your fulltext catalogs especially if you have a number of product catalogs mapped to a single fulltext catalog.

Specifying Wordbreakers

Since the catalog system allows you to store catalog content in different languages it also allows you to specify an appropriate wordbreaker for each language to get the most precise results when a search is performed. By default the catalog system associates the standard wordbreakers with the appropriate languages. Also the catalog system uses the Neutral word breaker to search non multilingual catalog content. You can change the wordbreaker to search non multilingual catalog content using the above script and specifying 'Neutral' as the language name.

Types of Searches

The CatalogSearch class in the Runtime BCLs has a property named FreeTextSearchPhrase which allows you to specify the word or phrase you want to search in one or more catalogs. The catalog system uses

  • FreeTextTable to perform the search if the FreeTextSearchPhrase does not contain the AND operator
  • ContainsTable to perform the search if the FreeTextSearchPhrase contains the AND operator or ends with *. When using ContainsTable the catalog system also specifies the formsof(inflection function to perform inflectional searches.To perform wildcard searches you should end the FreeTextSearchPhrase with *. For eg searching for “Book*“ will return all words beginning with Book. Note that * cannot be specified in the beginning, so searching for “*Book“ will not return all words containing book.

Providing an increased number of search features/conditions is something we are looking forward to, in the upcoming releases. This should allow you to specify more complex search phrases. 

Ranking Search results

When doing a freetext search you can get the Rank associated with search result and also sort the search results based on this rank. In order to do this you should pass FreeTextSearch_Rank as one of the PropertiesToReturn and SortProperty. For each row in the search result the Rank specifies the relative closeness of the FreeTextSearchPhrase in the row.

Ignoring Noise only clauses

Searching for certain phrases ( for eg “book AND a AND CD” ) involving the AND operator can cause the search to fail with the error “A clause of the query contained only ignored words.” In order to avoid these errors you should Run the Updatefulltextcatalogs script. This script should be run everytime you create a new catalog.

Steps to setup full-text search for your catalog system

  1. Set the “Maximum number of fulltext catalogs to create“ site property to an appropriate value.
  2. Ensure that the wordbreakers are appropriately set for each language in your catalog system.
  3. Mark the relevant properties freetextsearchable.
  4. Create your catalogs and optionally Run the Updatefulltextcatalogs script if you want to Ignore Noise only clauses.
  5. Periodically call the ProductCatalog.RegenerateFreeTextSearchIndex method to start an incremental update of your fulltext catalogs.
  6. Note that if you backup and restore your catalog database you will have to Rebuild all your fulltext catalogs using Sql Server Enterprise Manager.

Additional links