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 <cl...@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 > --------- RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Boston - October 24-26 * Los Angeles - Q1 2017
