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 <[email protected]> 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: [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 > -- Best regards, Ruslan. _______________________________________________ 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
