[rt-users] Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

[Ham MI-ID, Torsten Brumm]

Hi Emmanuel,
just got also some information from our DBA's, they had a look (on Friday night! Wow!) to the query:

SELECT 
DISTINCT main.* 
FROM Users main 
CROSS JOIN ACL ACL_4 
JOIN Principals Principals_1 ON (Principals_1.id = main.id) 
JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id) 
JOIN Groups Groups_3 ON (Groups_3.id = CachedGroupMembers_2.GroupId) 
WHERE (Principals_1.Disabled = '0') 
	AND (ACL_4.PrincipalType = Groups_3.Type) 
	AND (Principals_1.id != '1') 
	AND (Principals_1.PrincipalType = 'User') 
	AND (ACL_4.RightName = 'OwnTicket') 
	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;

And they came back with a much more faster query doing the same:

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; 

As you can see, less JOINS/CROSS JOINS. This Query is done in a few seconds compared to the one from SearchBuilder with 200sec and more.

Possibly a starting point for the RTDB Guys to review Searchbuilder...?!? But i'm not a DBA---

Torsten


Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), Alfred Manke, Christian Marnetté (Stellv.),  Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne



-----Urspruengliche Nachricht-----
Von: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 15:25
An: rt-users at lists.bestpractical.com
Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI

On Fri, Jan 23, 2009 at 02:55:21PM +0100, Ham MI-ID, Torsten Brumm wrote:
> Hi Emmanuel,
> just for my understanding: If they have somewhere the right to own 
> ticket and i do a bulk update only in one queue where only 5 people 
> have own ticket rights, all the users will be queried?
> 
> How can i easily check if they (especially the unpriviledged users) have own ticket rights?
> 

you can use bulk update on a search result involving more than one queue. And so in Search/Bulk.html, no queue is passed to Elements/SelectOwner, and so all people that can own ticket are displayed.


maybe we can try to get the list of queues from search result and pass it to SelectOwner to reduce the list... but maybe gt of this list will slow down Bulk.html to much ??? I need to try this.


_______________________________________________
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