[rt-devel] Query issue...

[James L. Harrison]

Here is the query that is causing all the grief... I think this only occurs
after you do a search with an email address contains restriction... click on
a ticket, and then click back, mysql utilization goes to 99%.

This is the initial search query:
SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals
Principals_2, CachedGroupMembers CachedGroupMembers_3, Principals
Principals_4, Users Users_5, Groups Groups_6, Principals Principals_7,
CachedGroupMembers CachedGroupMembers_8, Principals Principals_9, Users
Users_10    WHERE ((main.EffectiveId = main.id)) AND ((main.Type =
'ticket')) AND ( ( (main.Subject LIKE '%screen%')AND(main.Subject LIKE
'%screen%') ) AND (  ( (Users_5.EmailAddress LIKE
'%harrison%')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type =
'Requestor')AND(Principals_2.PrincipalType = 'Group') ) OR (
(Users_10.EmailAddress LIKE '%harrison%')AND(Groups_6.Domain =
'RT::Ticket-Role')AND(Groups_6.Type =
'Requestor')AND(Principals_7.PrincipalType = 'Group') )  ) )  AND
Groups_1.Instance = main.id  AND Groups_1.id = Principals_2.ObjectId  AND
Principals_2.id = CachedGroupMembers_3.GroupId  AND
CachedGroupMembers_3.MemberId = Principals_4.id  AND Principals_4.ObjectId =
Users_5.id  AND Groups_6.Instance = main.id  AND Groups_6.id =
Principals_7.ObjectId  AND Principals_7.id = CachedGroupMembers_8.GroupId
AND CachedGroupMembers_8.MemberId = Principals_9.id  AND
Principals_9.ObjectId = Users_10.id  ORDER BY main.id ASC LIMIT 50

This query runs with no problem.

Now I click on a ticket, it displays, and I hit back.  Mysql goes to 99%
usage on this query:

SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals
Principals_2, CachedGroupMembers CachedGroupMembers_3, Principals
Principals_4, Users Users_5, Groups Groups_6, Principals Principals_7,
CachedGroupMembers CachedGroupMembers_8, Principals Principals_9, Users
Users_10    WHERE ((main.EffectiveId = main.id)) AND ((main.Type =
'ticket')) AND ( (  ( (Users_5.EmailAddress LIKE
'%harrison%')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type =
'Requestor')AND(Principals_2.PrincipalType = 'Group') ) OR (
(Users_10.EmailAddress LIKE '%harrison%')AND(Groups_6.Domain =
'RT::Ticket-Role')AND(Groups_6.Type =
'Requestor')AND(Principals_7.PrincipalType = 'Group') )  ) AND ( (main.Queue
= '8') ) )  AND Groups_1.Instance = main.id  AND Groups_1.id =
Principals_2.ObjectId  AND Principals_2.id = CachedGroupMembers_3.GroupId
AND CachedGroupMembers_3.MemberId = Principals_4.id  AND
Principals_4.ObjectId = Users_5.id  AND Groups_6.Instance = main.id  AND
Groups_6.id = Principals_7.ObjectId  AND Principals_7.id =
CachedGroupMembers_8.GroupId  AND CachedGroupMembers_8.MemberId =
Principals_9.id  AND Principals_9.ObjectId = Users_10.id  ORDER BY main.id
ASC LIMIT 50;
# User at Host: rt_user[rt_user] @ localhost []
# Query_time: 562  Lock_time: 0  Rows_sent: 0  Rows_examined: 56783101

they are different... The second one is not even looking for a subject of
"screen" like the first one.

The url that triggers this query is:
Listing.html?Bookmark=FrT%253B%25402%257C%25250%257C%25241%257C5&CompileRest
riction=1&OwnerOp=%3D&ValueOfOwner=&RequestorOp=LIKE&ValueOfRequestor=harris
on&SubjectOp=LIKE&ValueOfSubject=screen&QueueOp=%3D&ValueOfQueue=&PriorityOp
=%3C&ValueOfPriority=&DateType=Created&DateOp=%3C&ValueOfDate=&AttachmentFie
ld=Content&AttachmentFieldOp=LIKE&ValueOfAttachmentField=&StatusOp=%3D&Value
OfStatus=&RowsPerPage=50&TicketsSortBy=id&TicketsSortOrder=ASC&RefreshSearch
Interval=-1&Action=Search

Any thoughts?

-Jamie

---
James L. Harrison
harrison at palisadesys.com
Technical Support Engineer
Palisade Systems
515.296.0701