[rt-users] sphinx weirdness [explanation and possible solution]

[Alex Vandiver]

On Wed, 25 Feb 2015 13:54:45 +0100 Arkadiusz Miśkiewicz
<arekm at maven.pl> wrote:
> > Ok, mysql is too smart! For sphinx to work mysql needs to first
> > query AttachmentsIndex_3 and then make joins to it. Otherwise
> > sphinx won't work. That's due to sphinx architecture.
> > 
> > Here mysql is too smart and differently optimizes query thus
> > breaking sphinx support in rt.
> > 
> > Now I've tried FORCE INDEX and such but wasn't able to force mysql
> > to first query AttachmentsIndex_3.
> > 
> > There is STRAIGHT_JOIN that forces joins orders, so maybe that is
> > some solution.

This limitation is unfortunately documented, and not easily fixable:
https://bestpractical.com/docs/rt/latest/full_text_indexing.html#Caveats1

> STRAIGHT_JOIN also won't work since mysql still is able to make
> changes and optimizations to the query.
> 
> Fortunately code below seems to be working - using UNION and separate,
> simple sphinx subquery:
> [snip]

That query is incorrect; it unions Attachment ids (from the Sphinx
results table) with Ticket ids (from the "ti" subquery on Tickets).

> Devs, could you please change querying code, so that sphinx will
> always get its own subquery?

Please try the straight MySQL FTS, included in 4.2.10, instead.  It is
much faster, and not nearly as fiddly.
 - Alex