[rt-users] Slow ticket search page becoming a problem

[Philip Kime]

The problem seems to be caused by the ORDER BY clause. If I remove this,
it's very fast.
 
PK

________________________________

From: Jesse Vincent [mailto:jesse at bestpractical.com] 
Sent: Thursday, April 26, 2007 6:00 AM
To: Todd Chapman
Cc: Philip Kime; RT Users
Subject: Re: [rt-users] Slow ticket search page becoming a problem


That looks suspiciously like the problem I'd been talking to you about
before, Todd. 

On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:


	I know I asked this before but I've been swamped and lost track
of there the discussion got to.
	
	RT 3.6.3, Mysql 5.0.27. The main ticket search page is really
slow to load, typically 45 seconds, sometimes longer. Problem query and
explain below. It's starting to become a problem for us. Seems that the
first row of the explain output is the guilty one. Rows_examined is
absurdly high.
	
	PK
	
	# Query_time: 45 Lock_time: 0 Rows_sent: 290 Rows_examined:
65256162
	SELECT DISTINCT main.* FROM Users main , Principals
Principals_1, CachedGroupMembers CachedGroupMembers_2, Groups Groups_3,
ACL ACL_4 WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND
((ACL_4.RightName = 'OwnTicket')) AND ((CachedGroupMembers_2.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_2.GroupId)) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType =
'User')) AND ((Principals_1.id != '1')) AND ((main.id =
Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Queue') OR
(ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain =
'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) ORDER BY
main.RealName ASC;
	
	*************************** 1. row ***************************
	id: 1
	select_type: SIMPLE
	table: main
	type: range
	possible_keys: PRIMARY
	key: PRIMARY
	key_len: 4
	ref: NULL
	rows: 673
	Extra: Using where; Using temporary; Using filesort
	*************************** 2. row ***************************
	*************************** 2. row ***************************
	id: 1
	select_type: SIMPLE
	table: Principals_1
	type: eq_ref
	possible_keys: PRIMARY
	key: PRIMARY
	key_len: 4
	ref: rt3.main.id
	rows: 1
	Extra: Using where; Distinct
	*************************** 3. row ***************************
	id: 1
	select_type: SIMPLE
	table: CachedGroupMembers_2
	type: ref
	possible_keys: DisGrouMem,SHRD_CGM1
	key: SHRD_CGM1
	key_len: 5
	ref: rt3.main.id
	rows: 1
	Extra: Using where; Using index; Distinct
	*************************** 4. row ***************************
	id: 1
	select_type: SIMPLE
	table: ACL_4
	type: range
	possible_keys: ACL1
	key: ACL1
	key_len: 54
	ref: NULL
	rows: 77
	Extra: Using where; Using index; Distinct
	*************************** 5. row ***************************
	id: 1
	select_type: SIMPLE
	table: Groups_3
	type: eq_ref
	possible_keys: PRIMARY,Groups1,Groups2
	key: PRIMARY
	key_len: 4
	ref: rt3.CachedGroupMembers_2.GroupId
	rows: 1
	Extra: Using where; Distinct
	
	
	--
	Philip Kime
	NOPS Systems Architect
	310 401 0407
	_______________________________________________
	http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

	Community help: http://wiki.bestpractical.com
	Commercial support: sales at bestpractical.com


	Discover RT's hidden secrets with RT Essentials from O'Reilly
Media. 
	Buy a copy at http://rtbook.bestpractical.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070426/f17bfa81/attachment.htm>