Philip, please try the following query and send us times and EXPLAIN:
SELECT STRAIGHT_JOIN DISTINCT main.* FROM
ACL ACL_4,
Groups Groups_3,
CachedGroupMembers CachedGroupMembers_2,
Principals Principals_1,
Users main
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;
It's the same query but with forced order of joins, I do believe that
this is the ideal plan for joins in this situation for all setups.
On 4/27/07, Philip Kime <[EMAIL PROTECTED]> wrote:
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:
[snip]
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
--
Best regards, Ruslan.
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
