[rt-users] Slow ticket search page becoming a problem
Philip Kime
pkime at Shopzilla.com
Thu Apr 26 20:19:33 EDT 2007
Ok, the issue is that MYSQL 5 won't use the index on main.Name by
default (possible keys list PRIMARY only, which is useless for this
ORDER BY clause), which it really needs to do with an ORDER BY clause
for main.Name (or main.RealName as in my example as I have modified the
display code). It is fixed if you force the index use:
mysql> SELECT DISTINCT main.* FROM Principals Principals_1,
CachedGroupMembers C
achedGroupMembers_2, Groups Groups_3, ACL ACL_4, Users main FORCE
INDEX(Users1) WHERE ((ACL_4.PrincipalType = Groups_3.Type)) AND
((ACL_4.RightName = 'Own
Ticket')) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
((Groups_3
.id = CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = '0'))
AND ((P
rincipals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND
((main.i
d = 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.Name ASC;
Then it's nice and fast again. The explain shows that it's still a
filesort/temp query but it does a indexed table scan instead of an
unindexed range scan.
I assume that this would need a SearchBuilder mod to force the use of
the index related to the ORDER BY clause?
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/06c82d64/attachment.htm>
More information about the rt-users
mailing list