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.zaki...@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.br...@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-boun...@lists.bestpractical.com [mailto: > rt-users-boun...@lists.bestpractical.com] Im Auftrag von Emmanuel Lacour > > Gesendet: Freitag, 23. Januar 2009 15:25 > > An: rt-users@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: > sa...@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: sa...@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: sa...@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
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com