Enabling Full Text Search in Gemini

When Gemini has been used extensively for a number of years, sometimes people see a slow down in the search. This is the Text Based search which is a free field. As a rule, we’d always recommend using the filters and not the free text field, because you should know in which field you are searching. I personally only ever use that on smaller projects and where the text I am searching for could be in a number of fields, and of course to find a particular ticket ID. If you think about it, there is a lot of data Gemini captures, and expecting lightning fast results while it searches across 10,000 tickets in Description, Comments, Titles, Custom Fields etc is going to take its toll.

Enabling Full Text Search

SQL server has the option to enable a Full Text Search and this will help improve performance significantly.

Firstly, you must enable Full Text Search in the SQL server. There are plenty of resources online to help do this. Here is one https://www.techrepublic.com/blog/data-center/adding-sql-full-text-search-to-an-existing-sql-server/ I found with a quick online search.

Secondly, you must create a catalog. This will tell SQL which fields should be used in any search.

You will need to run this SQL statement to configure the catalog (for English).

CREATE FULLTEXT CATALOG Gemini
WITH ACCENT_SENSITIVITY = ON
GO

CREATE FULLTEXT INDEX ON [dbo].[gemini_customfielddata] (
fielddata Language 1033
)
KEY INDEX gemini_customfielddata_customfielddataid_pk
ON Gemini
WITH CHANGE_TRACKING AUTO
GO

CREATE FULLTEXT INDEX ON [dbo].[gemini_issuecomments] (
comment Language 1033
)
KEY INDEX gemini_issuecomments_commentid_pk
ON Gemini
WITH CHANGE_TRACKING AUTO
GO

CREATE FULLTEXT INDEX ON [dbo].[gemini_issues] (
summary Language 1033
,longdesc Language 1033
)
KEY INDEX gemini_issues_issueid_pk
ON Gemini
WITH CHANGE_TRACKING AUTO
GO

The last step is to tell Gemini that it can use this as the filtering. In the web.config you need to add an app setting with the key gemini.fulltextsearch and value of true

<add key="gemini.fulltextsearch" value="true"/>

Let us know in the comments how it helps.