[rt-users] Mysql slowness with subject searches (possibly others)

[Rich Bishop]

Follow up to my own question - we've still seeing these problems and have
discovered it seems to be due to mysql not using an index on the query. This
query: 

SELECT DISTINCT main.* FROM Tickets main JOIN Groups
Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance =
main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId =
'1060860' ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE ( (  (
main.Queue = '3' OR main.Queue = '4' OR main.Queue = '5' OR main.Queue = '6' OR
main.Queue = '7' OR main.Queue = '8' OR main.Queue = '9' OR main.Queue = '10' OR
main.Queue = '10' OR main.Queue = '11' OR main.Queue = '11' OR main.Queue = '12'
OR main.Queue = '12' OR main.Queue = '13' OR main.Queue = '13' OR main.Queue =
'14' OR main.Queue = '14' OR main.Queue = '15' OR main.Queue = '16' OR
main.Queue = '18' OR main.Queue = '18' OR main.Queue = '19' OR main.Queue = '20'
OR main.Queue = '21' OR main.Queue = '21' OR main.Queue = '22' OR main.Queue =
'23' OR main.Queue = '24' OR main.Queue = '26' OR main.Queue = '29' OR
main.Queue = '30' OR main.Queue = '31' OR main.Queue = '32' OR main.Queue = '36'
OR main.Queue = '38' OR main.Queue = '44' OR main.Queue = '51' OR main.Queue  =
'106' OR main.Queue = '110' OR main.Queue = '115' OR main.Queue = '120' OR
main.Queue = '124' OR main.Queue = '125' OR main.Queue = '128' OR main.Queue =
'129' OR main.Queue = '138' OR main.Queue = '139' OR main.Queue = '141' OR
main.Queue = '142' OR main.Queue = '148' OR main.Queue = '150' OR main.Queue =
'153' OR main.Queue = '154' )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL
AND Groups_1.Name = 'Requestor' AND  ( main.Queue = '17' OR main.Queue = '46' )
)  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'Cc' AND
main.Queue = '17' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status !=
'deleted') AND (main.Type = 'ticket') AND (main.Subject LIKE '%floria%')  ORDER
BY main.id ASC  LIMIT 50;


takes around 22s, add use index (Tickets1) and it runs in 1.5s. I'm not aware
that we can add the index hint into a DBIx::SearchBuilder query and I'm not sure
how to force mysql to use the index without a hint. Does anyone have any ideas?

Thanks,

Rich