I've investigated more on this problem and found out that this problem
of mysql as couldn't optimize well some queries when it's possible, I
know how to hack a workaround and help RDBMs optimize it, but later...
-EHAVENOTIME
On 3/29/06, Laas Toom <[EMAIL PROTECTED]> wrote:
Tuesday 28 March 2006 7:01 pm Ruslan Zakirov wrote:
> Could you show full explain for the query?
Unfortunately (or fortunately ;-) the query now is planned differently than,
when I began testing yesterday so I can not give you the very first explain
output, where the 'main' table was joined in first and which took 8 sec to
complete.
But below are two different EXPLAINs which I can get right now.
(Use fixed-font to view these tabels, but I also included URLs for screenshots
of the same tables)
1) The way this query is run now on our server. This query takes 1.5 sec to
complete and after some runs gets even faster (caching probably).
http://www3.eenet.ee/~laas/explain_select_owners_1.png
SQL query:
EXPLAIN SELECT DISTINCT main.* FROM Users main, Principals Principals_1, ACL
ACL_2, CachedGroupMembers CachedGroupMembers_3 WHERE ((ACL_2.PrincipalId =
CachedGroupMembers_3.GroupId)) AND ((ACL_2.PrincipalType = 'Group')) AND
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_3.MemberId =
Principals_1.id)) AND ((Principals_1.Disabled = '0')) AND
((Principals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND
((main.id = Principals_1.id)) AND ((ACL_2.ObjectType = 'RT::Ticket' AND
ACL_2.ObjectId = 1365) OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId =
8) OR (ACL_2.ObjectType = 'RT::System')) ORDER BY main.Name ASC;
EXPLAIN:
id select_ table type possible_ key key_ ref rows Extra
type keys len
------------------------------------------------------------------------------
1 SIMPLE ACL_2 range ACL1 ACL1 85 NULL 7 Using where;
Using index;
Using
temporary;
Using filesort
------------------------------------------------------------------------------
1 SIMPLE main range PRIMARY, PRIMARY 4 NULL 9259 Using where
Users3
------------------------------------------------------------------------------
1 SIMPLE Princi eq_ref PRIMARY PRIMARY 4 rt3. 1 Using where;
pals_1 main.id Distinct
------------------------------------------------------------------------------
1 SIMPLE Cached ref DisGrouMem GrouMem 10 rt3.ACL_2. 1 Using where;
Group GrouMem PrincipalId, Using index;
Members_3 MemberId_idx rt3.main.id Distinct
2) The same query, but I have put the 'Users main' table to the last position
in the FROM statement. This query is fast (0.0032 sec) from the beginning (as
can be concluded by the small number of rows involved in this).
http://www3.eenet.ee/~laas/explain_select_owners_2.png
SQL query:
EXPLAIN SELECT DISTINCT main.* FROM Principals Principals_1, ACL ACL_2,
CachedGroupMembers CachedGroupMembers_3, Users main WHERE ((ACL_2.PrincipalId
= CachedGroupMembers_3.GroupId)) AND ((ACL_2.PrincipalType = 'Group')) AND
((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_3.MemberId =
Principals_1.id)) AND ((Principals_1.Disabled = '0')) AND
((Principals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND
((main.id = Principals_1.id)) AND ((ACL_2.ObjectType = 'RT::Ticket' AND
ACL_2.ObjectId = 1365) OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId
= 8) OR (ACL_2.ObjectType = 'RT::System')) ORDER BY main.Name ASC;
EXPLAIN:
id select_ table type possible_ key key_ ref rows Extra
type keys len
-----------------------------------------------------------------------------
1 SIMPLE ACL_2 range ACL1 ACL1 85 NULL 7 Using where;
Using
-----------------------------------------------------------------------------
1 SIMPLE Cached ref DisGrouMem, GrouMem 5 rt3.ACL_2. 4 Using where;
Group GrouMem, PrincipalId Using index
Members_3 MemberId_idx
-----------------------------------------------------------------------------
1 SIMPLE main eq_ref PRIMARY, PRIMARY 4 rt3.CachedG 1
Users3
-----------------------------------------------------------------------------
1 SIMPLE Princ eq_ref PRIMARY PRIMARY 4 rt3.main.id 1 Using where;
ipals_1 Distinct
Best regards,
Laas Toom
_______________________________________________
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
We're hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html
--
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
We're hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html
