On Wed, 25 Feb 2015 13:54:45 +0100 Arkadiusz Miśkiewicz <ar...@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