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@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@palisadesys.com
Technical Support Engineer
Palisade Systems
515.296.0701