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: [email protected]
[mailto:[email protected]] Im Auftrag von Emmanuel Lacour
Gesendet: Freitag, 23. Januar 2009 15:25
An: [email protected]
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:
[email protected]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
_______________________________________________
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
