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

[Arkadiusz Miśkiewicz]

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 )