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
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
