Automatic stop words prevent users from being able to search for specific terms - Support and Troubleshooting

Automatic stop words prevent users from being able to search for specific terms Description

Description When you setup text indexing on a particular table you will see stop words get created in the ts_index_stop table automatically. This is a normal and desirable process as it helps maintain efficient text index searching by reducing the size of the text index tables that contain the indexed words.

Procedure Automatic stop words get created when a search term generates more search results than the threshold, the system automatically creates a stop word for the search term. The threshold is determined by the "Auto threshold" value for the table that is text indexed. Please note that text indexes are built against the highest level parent table. This is why you will see "task" instead of "incident" or "change_request" when reviewing the text indexes. You can view the tables that have been text indexed and the associated auto threshold as follows:

1) Navigate to System Definition > Text Indexes. 2) Open the text index entry for the table. 3) Locate the "Auto threshold" field as shown below:

The value in the auto threshold defines the # of matches returned that will trigger the creation of the stop word. When there are that many records in that table (or any of the associated child tables) that have the term we will create the stop word. So in the case above it means that when the search results for a specific term would return over 50,000 records we would create the term as a stop word in the ts_index_stop table.

*** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT ***

Do not just increase the value of the auto threshold field to a larger number as this will have a significant effect on the size of the text index tables since every term will be indexed up to that amount and thus potentially increase the total size of the related text index tables for that table and thus significantly degrade text index performance over time.

*** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT ***

So when you have a term that is now a stop word so that searches for that term are no longer bringing back the desired records there are a couple of things to consider.

To start with you should confirm that the word is found in the ts_index_stop word table. Remember that it also may be one of the stemmed words from the search term that is a stop word (for example if "swimming" was the search term remember that "swam" or "swim" might be the stop word.

Once you have located the appropriate record in the ts_index_stop table you need to see what the "Stop mode" drop down list field is set to. It will be set to one of the following two values:

1) Index but do Not Query

OR

2) Neither Index nor Query

To begin with lets understand what those two entries mean. When a stop word entry first gets created in the ts_index_stop table it will start with the "Index but do Not Query" stop mode value. This means that if the term is found in a record we will keep inserting it into the text index tables for that record. So even though the word will not return records for text searches against the necessary indexing is taking place for the term to be found. This allows you to change that particular word to the stop mode value of "Not a Stop Word" which will immediately allow searches to return records for that search term. Now, if the record is set to "Neither Index nor Query" it means that we have not been inserting new entries into the text index tables for that particular term at the point in time which the stop word switched to this stop mode. What this means is that even if you set the stop mode to "Not a Stop Word" you will not see all the appropriate entries returned since newer records that were created/updated with that term after the word was marked as "Neither Index nor Query" would not be in the text index tables. To allow all appropriate records to be returned for a term whose stop mode was changed from "Neither Index nor Query" to "Not a Stop Word" you would need to rebuild the indexes for that table.

*** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT ***

If you do decide to rebuild the indexes for a table to allow a term that was changed from "Neither Index nor Query" to "Not a Stop Word" please be sure to plan that index rebuilding carefully. When you do rebuild the indexes for that table it will wipe out existing indexes and depending on system load and the number of records in the target table it may take a considerable amount of time. It would be best to plan this to run over a weekend or other period of low usage/activity.

*** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT *** IMPORTANT *

Once the indexes have been rebuilt for the table the search term in question should return all expected records. There is no way to know exactly how long an index rebuild will take but you can test it on a non-production instance first to get a general idea of the timing though it will not be exactly the same.

Applicable Versions ALL

Additional Information For more information regarding this please see the following documentation:

Zing removes stop words from queries

Enable automatic stop words for a table