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

[Torsten Brumm]

Hi Ruslan,
no problem, if i can check something, let me know, i have already portet our
installation to 3.8.2 in test environment.
Torsten

2009/1/26 Ruslan Zakirov <ruslan.zakirov at gmail.com>

> 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.
> _______________________________________________
> 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
>



-- 
MFG

Torsten Brumm

http://www.torsten-brumm.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090126/d34e4289/attachment.htm>