Re: [rt-users] sphinx weirdness [explanation and possible solution]
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
Re: [rt-users] sphinx weirdness [explanation and possible solution]
On Wednesday 25 of February 2015, Arkadiusz Miśkiewicz 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. 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: SELECT DISTINCT main.id FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN ((SELECT ai.id FROM AttachmentsIndex ai WHERE ai.query = 'inconventus') UNION (SELECT ti.id FROM Tickets ti WHERE ti.Subject LIKE '%inconventus%')) u ON u.id=Attachments_2.id WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ); Devs, could you please change querying code, so that sphinx will always get its own subquery? That will always work because mysql query optimizer will not be able to mess with sphinx query. Optimizer will be able only to optimize combining sphinx results with the rest of query though and that's ok and desired. Sphinx subquery will always be simple, like SELECT ai.id FROM AttachmentsIndex ai WHERE ai.query = 'inconventus' Thanks, -- Arkadiusz Miśkiewicz, arekm / ( maven.pl | pld-linux.org )
