[rt-users] force use index when constructing the sql from Tickets_Overlay

[Raed El-Hames]

Hi;

rt-3.8.7
mysql 5.1

The following query :

SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN 
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( 
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) LEFT 
JOIN CachedGroupMembers CachedGroupMembers_2  ON ( 
CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2 
.GroupId = Groups_1.id )  WHERE (Users_3.EmailAddress LIKE 
'%blah.gov.uk%') AND (main.Status != 'deleted') AND ( ( main.Subject 
LIKE '%blah%' OR main.Subject LIKE '%blah CC%' OR main .Subject LIKE 
'%blah County%' OR  ( CachedGroupMembers_2.id IS NOT NULL )  )  AND  ( 
main.Created > '2008-12-31 00:00:00' AND main.Created < '2009-03-01 
00:00:00' ) ) AND (main.Type  = 'ticket') AND (main.EffectiveId = 
main.id)  ORDER BY main.id ASC  LIMIT 100;

Takes over 3 minutes to run (from sql client), the web interface takes ~ 
4 to 5 minutes to come back,  an explain show that its using
index Groups2 (Type, Instance) , when joining the Groups table.

However:

SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN 
Groups Groups_1 *use index (Groups1)* ON ( Groups_1.Domain = 
'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( 
Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers 
CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id ) 
AND ( CachedGroupMembers_2 .GroupId = Groups_1.id )  WHERE 
(Users_3.EmailAddress LIKE '%blah.gov.uk%') AND (main.Status != 
'deleted') AND ( ( main.Subject LIKE '%blah%' OR main.Subject LIKE 
'%blah CC%' OR main .Subject LIKE '%blah County%' OR  ( 
CachedGroupMembers_2.id IS NOT NULL )  )  AND  ( main.Created > 
'2008-12-31 00:00:00' AND main.Created < '2009-03-01 00:00:00' ) ) AND 
(main.Type  = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY 
main.id ASC  LIMIT 100;

This returns the results in ~ 20 seconds.

So the first question is; is there any why I can add the use index 
statement when the sql is constructed my guess some where in  
Tickets_Overlay::_WatcherLimit??
Second question if the answer to the first question is no ; then is 
there any other way I can optimise the original query ??

I can send the explain out put if required ;

Regards;

Roy