Search Functionality

[CSL]

Hi all,

I'd like to ask if anyone is using any modifications / scripts / 3rd-party tools to improve OTRS's search functionality?

We have found the fulltext search in particular to be very limited and basic, and are wondering if others have found the same thing (we use MySQL with Ticket::SearchIndexModule set to StaticDB). Can anyone recommend anything?

Thanks

[crythias]

Considering you can search on any specific field, what are the limitations you are encountering, and what are you expecting?

Basically, I've changed the default search for my implementation to ignore ticket number and look for company (CustomerID) and a keyword.

[CSL]

Thanks for replying crythias,

The issue we had is that OTRS's fulltext search uses the SQL LIKE statement. Below is an extract of the command it runs when searching for 'any':

Code: Select all

AND (((art.a_cc LIKE '%any%' ) ) OR ((art.a_subject LIKE '%any%' ) ) OR ((art.a_to LIKE '%any%' ) ) OR ((art.a_body LIKE '%any%' ) ) OR ((art.a_from LIKE '%any%' ) ))

This is very basic and limiting. It does not allow for logical expressions or operators, and because it does not use an index it is also comparatively slow. MySQL has a native fulltext search index that is far more powerful and far faster than this. It can be applied to any text column in a MYISAM database, so OTRS's article table is the perfect place for it to be added in.

I had a ticket open with OTRS support about this, but they informed me that they couldn't help beyond adding an enhancement request. They said the fulltext search was kept simple so that it worked the same across all of the different databases it supports (Oracle, MSSQL etc).

So I decided that I'd add it myself: I began analysing the perl modules used to construct the SQL, and finally managed to add this to our OTRS install (which is still on 3.0.11). We now have native MySQL fulltext search running with boolean mode expressions enabled, allowing for operators such as + and -. It is faster and returns results that are more relevant.

If anyone is interested in how to do this, I can post a tutorial.

[CSL]

Just to add that I've now posted this as a tutorial in the HOWTO section here:

viewtopic.php?f=60&t=16213