[rt-users] Help! I enabled Full Text search and now Simple search won't look at Subjects !!

[Claes Merin]

I beg to differ about the attachments table, in our setup we rely on
customfields and there parameters. We have quite a few tickets that does
not have any attachments.
  If you do not write anything in the message body when you create a
ticket (through web-ui or REST), there will be no attachment connected
to that ticket.

Also i did a comparison between mysql/mariadb FULLTEXT index and sphinx,
and it seems that sphinxsearch is able to extract more info than the
mysql FTS. I guess it's about the data processing that sphinx does with
encoding and other things.

Our goal is to include CF's in the fulltext index as well. As far as I
can see right now, there are two ways to do this.

a) Modify the RT simplesearch use an "TicketsIndex" instead of the
"AttachmentsIndex".

Use sphinx to create the TicketsIndex table and modify the collector
query to include both attachment, ticket.subject and customfields in the
the database. (Simple join statements)

b) Create "null" attachments and attach these to tickets that does not
have any current attachments, just to create and relation between ticket
and attachment. The advantage with this is that we do not have to modify
any of the RT code, but of course it adds some rows to the database.


Claes Merin

On 2016-08-20 06:34, Alex Vandiver wrote:
> On Fri, 19 Aug 2016 16:35:07 +0200
> Claes Merin <claes at gastabud.com> wrote:
>> We are using sphinx (since last night) for FTS searches.
>>
>> Setup is as described in this excellent guide:
>>
>> http://blog.christosoft.de/2016/07/rt4-request-tracker4-fulltext-search-sphinx-debian-jessie/
> 
> Is there a reason you're not using MySQL's built-in search, available
> since 4.2.10?  It's more performant, and has none of the caveats:
> https://docs.bestpractical.com/rt/4.2/full_text_indexing.html#Caveats1
> 
> Especially since it allows for incremental indexing of new attachment
> rows, I know of no reason to prefer Sphinx.
> 
>> We also ran in to the problem described here, and I was thinking if one
>> could resolve it by including the subject in the sphinx database...
> 
> I expect that should work fine.
> 
>> Modifying the query in the guide to simply include the t.Subject like
>> this...
>>
>> ## /etc/sphinxsearch/sphinx.conf
>> sql_query = \
>>   SELECT a.id id, t.Subject, a.content FROM Attachments a \
>>   JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType =
>> 'RT::Ticket' \
>>   JOIN Tickets t ON txn.ObjectId = t.id \
>>   WHERE a.ContentType LIKE '%text%' AND t.Status != 'deleted'
>> ##
>>
>> The problem with this is, if there is a ticket that does not have an
>> attachment, it will not get indexed and is not searchable through FTS...
> 
> The "attachments" table is used to index all content in RT.  This
> includes textual content, such as the bodies of messages.  As such, it
> is impossible for a ticket to not have at least one Attachment record.
> 
>  - Alex
>