[rt-users] Searching Ticket Bodies

[Thomas Sibley]

On 01/08/2013 11:08 AM, Tim Gustafson wrote:
> Hrmm, I remember now.  That documentation states:
> 
> "MySQL does not support full-text indexing natively."
> 
> Which is untrue.  MySQL does indeed have full-text indexing, and the
> most recent version has full-text indexing with InnoDB tables.  Is
> there any hope for taking advantage of the FTS built-in to MySQL?  My
> MySQL server does a *lot* more than just RT, and re-compiling the
> whole thing with an add-on that gets used by exactly one database (out
> of, let's say, 400 databases) seems sub-optimal.

As Ken noted in his reply, MySQL 5.6 with InnoDB FTS hasn't reached a
non-developer release yet (what they call general availability, GA).

Additionally, the initial FTS support in 5.6 doesn't look promising for
a quick turnaround to get it supported in RT.  Two technical hurdles:

1) There is no native support for stemming in the DB, which is quite
important for content searches.  This means RT would need to do its own
stemming as a preprocessing step before handing the query to MySQL.  Pg,
Oracle, and Sphinx support this natively.

2) Only TEXT columns may be FTS indexed; on MySQL, the
Attachments.Content column is currently a BLOB since it is also used to
store binary data.  Additionally, partial full text indexes aren't
supported, so binary and textual content will have to be entirely split,
not just encoded as under Pg.  As an incredibly large upgrade step of
some sort or another, RT is probably going to need to make schema
changes to the Attachments table.  This is the largest table in big
installs by orders of magnitude, so executing the schema changes as
correctly and efficiently as possibly is key.  It will take significant
engineering and testing effort.

All this is to say, "Don't hold your breath." :)  Ken lists a number of
alternatives, such as isolating a MySQL instance with Sphinx from your
primary MySQL instance.