[rt-users] Very slow ticket search creation/edit screen 3.8.4

[Justin Hayes]

This is the query that takes all the time:

# Query_time: 37.415600  Lock_time: 0.000329 Rows_sent: 0  Rows_examined: 4474859
use rt_contdel;
SET timestamp=1267024665;
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' OR ACL_4.RightName = 'SuperUser') AND (Groups_3.Domain = 'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;


-------------------------------------------------
Justin Hayes
Orbis Support Manager
justin.hayes at orbisuk.com




On 15 Feb 2010, at 17:42, Justin Hayes wrote:

> Ah ok, so even if all my users have OwnTicket for a queue getting that list is still faster than showing all users on the Search screen, even though the resulting list is the same length?
> 
> Total rows in Users is 543.
> 
> Justin
> 
> -------------------------------------------------
> Justin Hayes
> Orbis Support Manager
> justin.hayes at orbisuk.com
> 
> 
> 
> 
> On 15 Feb 2010, at 17:33, Emmanuel Lacour wrote:
> 
>> On Mon, Feb 15, 2010 at 05:18:53PM +0000, Justin Hayes wrote:
>>> That doesn't seem to be an answer. So it's just slow if you want to have a lot of owners??
>>> 
>>> It doesn't take 40 secs to render the owner dropdown on ticket create/update, so why should the search screen be so bad? They both use the same element.
>>> 
>> 
>> bevause on a ticket, we already ha  ve a queue selected and so we filter
>> potential owners for this queue.
>> 
>> in search screen, we load all people that may own ticket in any queue
>> (all users that have OwnTicket right).
>> 
>> this is a known issue, but that shouldn't be a problem on most systems
>> where people with OwnTicket rights are a few hundreds.
>> 
>> tell us more about how many people you have has potential owner in
>> search screen.
>> 
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>> 
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>> 
>> 2010 RT Training Sessions!
>> San Francisco, CA, USA - Feb 22 & 23
>> Dublin, Ireland - Mar 15 & 16
>> Boston, MA, USA - April 5 & 6
>> Washington DC, USA - Oct 25 & 26
>> 
>> 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
> 
> 2010 RT Training Sessions!
> San Francisco, CA, USA - Feb 22 & 23
> Dublin, Ireland - Mar 15 & 16
> Boston, MA, USA - April 5 & 6
> Washington DC, USA - Oct 25 & 26
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com