Strange, long running MySQL Query under RT 3.6.5 / MySQL 5.0.67 / Apache 2.2.3 /FastCGI

RT Users
21

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.
Torsten2009/1/26 Ruslan Zakirov ruslan.zakirov@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@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@lists.bestpractical.com [mailto:
rt-users-bounces@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.

The rt-users Archives

Community help: http://wiki.bestpractical.com Commercial support:
sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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