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

[Ruslan Zakirov]

1) CROSS JOIN is equal to ","
2) STRAIGHT JOIN is workaround for mysql bugs/problems.
3) Start by complaining into mysql's bug tracker
4) There is only one thing I can do, but that will need more testing
from users and only on 3.8.


On Sat, Jan 24, 2009 at 12:37 PM, Ham MI-ID, Torsten Brumm
<torsten.brumm at kuehne-nagel.com> wrote:
> 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
>
> _______________________________________________
> 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
>



-- 
Best regards, Ruslan.