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 -- RT Training - Boston, September 9-10 http://bestpractical.com/training
