Hi Ruslan,<br>no problem, if i can check something, let me know, i have already portet our installation to 3.8.2 in test environment.<br>Torsten<br><br><div class="gmail_quote">2009/1/26 Ruslan Zakirov <span dir="ltr"><<a href="mailto:ruslan.zakirov@gmail.com">ruslan.zakirov@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">1) CROSS JOIN is equal to ","<br>
2) STRAIGHT JOIN is workaround for mysql bugs/problems.<br>
3) Start by complaining into mysql's bug tracker<br>
4) There is only one thing I can do, but that will need more testing<br>
from users and only on 3.8.<br>
<div><div></div><div class="Wj3C7c"><br>
<br>
On Sat, Jan 24, 2009 at 12:37 PM, Ham MI-ID, Torsten Brumm<br>
<<a href="mailto:torsten.brumm@kuehne-nagel.com">torsten.brumm@kuehne-nagel.com</a>> wrote:<br>
> Hi Emmanuel,<br>
> just got also some information from our DBA's, they had a look (on Friday night! Wow!) to the query:<br>
><br>
> SELECT<br>
> DISTINCT main.*<br>
> FROM Users main<br>
> CROSS JOIN ACL ACL_4<br>
> JOIN Principals Principals_1 ON (Principals_1.id = <a href="http://main.id" target="_blank">main.id</a>)<br>
> JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id)<br>
> JOIN Groups Groups_3 ON (Groups_3.id = CachedGroupMembers_2.GroupId)<br>
> WHERE (Principals_1.Disabled = '0')<br>
> AND (ACL_4.PrincipalType = Groups_3.Type)<br>
> AND (Principals_1.id != '1')<br>
> AND (Principals_1.PrincipalType = 'User')<br>
> AND (ACL_4.RightName = 'OwnTicket')<br>
> AND ((ACL_4.ObjectType = 'RT::Queue')<br>
> OR (ACL_4.ObjectType = 'RT::System'))<br>
> AND ((Groups_3.Domain = 'RT::Queue-Role')<br>
> OR (Groups_3.Domain = 'RT::System-Role'))<br>
> ORDER BY main.Name ASC;<br>
><br>
> And they came back with a much more faster query doing the same:<br>
><br>
> SELECT STRAIGHT_JOIN DISTINCT main.* FROM<br>
> ACL ACL_4,<br>
> Groups Groups_3,<br>
> CachedGroupMembers CachedGroupMembers_2,<br>
> Principals Principals_1,<br>
> Users main<br>
> WHERE ((ACL_4.PrincipalType = Groups_3.Type))<br>
> AND ((ACL_4.RightName = 'OwnTicket'))<br>
> AND ((CachedGroupMembers_2.MemberId = Principals_1.id))<br>
> AND ((Groups_3.id = CachedGroupMembers_2.GroupId))<br>
> AND ((Principals_1.Disabled = '0'))<br>
> AND ((Principals_1.PrincipalType = 'User'))<br>
> AND ((Principals_1.id != '1'))<br>
> AND ((<a href="http://main.id" target="_blank">main.id</a> = Principals_1.id))<br>
> AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))<br>
> AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) ORDER BY main.RealName ASC;<br>
><br>
> 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.<br>
><br>
> Possibly a starting point for the RTDB Guys to review Searchbuilder...?!? But i'm not a DBA---<br>
><br>
> Torsten<br>
><br>
><br>
> 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<br>
><br>
><br>
><br>
> -----Urspruengliche Nachricht-----<br>
> Von: <a href="mailto:rt-users-bounces@lists.bestpractical.com">rt-users-bounces@lists.bestpractical.com</a> [mailto:<a href="mailto:rt-users-bounces@lists.bestpractical.com">rt-users-bounces@lists.bestpractical.com</a>] Im Auftrag von Emmanuel Lacour<br>
> Gesendet: Freitag, 23. Januar 2009 15:25<br>
> An: <a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
> Betreff: Re: [rt-users] Strange,long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 /Apache2.2.3/FastCGI<br>
><br>
> On Fri, Jan 23, 2009 at 02:55:21PM +0100, Ham MI-ID, Torsten Brumm wrote:<br>
>> Hi Emmanuel,<br>
>> just for my understanding: If they have somewhere the right to own<br>
>> ticket and i do a bulk update only in one queue where only 5 people<br>
>> have own ticket rights, all the users will be queried?<br>
>><br>
>> How can i easily check if they (especially the unpriviledged users) have own ticket rights?<br>
>><br>
><br>
> 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.<br>
><br>
><br>
> 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.<br>
><br>
><br>
> _______________________________________________<br>
> <a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users" target="_blank">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a><br>
><br>
> Community help: <a href="http://wiki.bestpractical.com" target="_blank">http://wiki.bestpractical.com</a> Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a><br>
><br>
><br>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
> Buy a copy at <a href="http://rtbook.bestpractical.com" target="_blank">http://rtbook.bestpractical.com</a><br>
><br>
> _______________________________________________<br>
> <a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users" target="_blank">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a><br>
><br>
> Community help: <a href="http://wiki.bestpractical.com" target="_blank">http://wiki.bestpractical.com</a><br>
> Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a><br>
><br>
><br>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
> Buy a copy at <a href="http://rtbook.bestpractical.com" target="_blank">http://rtbook.bestpractical.com</a><br>
><br>
<br>
<br>
<br>
</div></div><font color="#888888">--<br>
Best regards, Ruslan.<br>
</font><div><div></div><div class="Wj3C7c">_______________________________________________<br>
<a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users" target="_blank">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a><br>
<br>
Community help: <a href="http://wiki.bestpractical.com" target="_blank">http://wiki.bestpractical.com</a><br>
Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a><br>
<br>
<br>
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
Buy a copy at <a href="http://rtbook.bestpractical.com" target="_blank">http://rtbook.bestpractical.com</a></div></div></blockquote></div><br><br clear="all"><br>-- <br>MFG<br><br>Torsten Brumm<br><br><a href="http://www.torsten-brumm.de">http://www.torsten-brumm.de</a><br>