[rt-users] searching for users takes forever

Jesse Vincent jesse at bestpractical.com
Wed Oct 20 18:16:47 EDT 2004




On Wed, Oct 20, 2004 at 06:11:45PM -0400, Sheeri Kritzer wrote:
> Actually, my problem is that the UI is NOT specifically preventing it.  
> 
> (we're running 3.0.11, by the way)
> 
> The query seems to be this (from the slow queries log):
> 
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1, 
> CachedGroupMembers CachedGroupMembers_2  WHERE ((CachedGroupMembers_2.GroupId = 
> '4')) AND ((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType = 
> 'User')) AND ((Principals_1.id = CachedGroupMembers_2.MemberId)) AND ((main.id = 
> Principals_1.id)or(main.id != '1')or(main.id != '10')) ORDER BY main.Name ASC;
> 
> We're using mysql, and when I run the query from the commandline, there are:
> 5900 rows in set and it takes 2 min 22.01 sec.
> 
> I hope the following is readable.  I think that's all you need (or more than you 
> need). . .we're using MySQL 4.0.14.


Well, we'd  also need "EXPLAIN SELECT ..." if the problem can't easily
be solved by upgrading to a current DBIx::SearchBuilder and/or RT
3.0.12. 

 AND ((main.id =  Principals_1.id)or(main.id != '1')or(main.id != '10')) 

I _know_ that query has been fixed already.  Those ORs should be ANDs




> mysql> show indexes from Users;
> +-------+------------+----------+--------------+--------------+-----------+-----
> --------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | 
> Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +-------+------------+----------+--------------+--------------+-----------+-----
> --------+----------+--------+------+------------+---------+
> | Users |          0 | PRIMARY  |            1 | id           | A         |      
>   5496 |     NULL | NULL   |      | BTREE      |         |
> | Users |          0 | Users1   |            1 | Name         | A         |      
>   5496 |     NULL | NULL   |      | BTREE      |         |
> | Users |          1 | Users2   |            1 | Name         | A         |      
>   5496 |     NULL | NULL   |      | BTREE      |         |
> | Users |          1 | Users3   |            1 | id           | A         |      
>   5496 |     NULL | NULL   |      | BTREE      |         |
> | Users |          1 | Users3   |            2 | EmailAddress | A         |      
>   5496 |     NULL | NULL   | YES  | BTREE      |         |
> | Users |          1 | Users4   |            1 | EmailAddress | A         |      
>   5496 |     NULL | NULL   | YES  | BTREE      |         |
> +-------+------------+----------+--------------+--------------+-----------+-----
> --------+----------+--------+------+------------+---------+
> 6 rows in set (0.01 sec)
> 
> 
> mysql> show indexes from Principals;
> +------------+------------+--------------------+--------------+---------------+-
> ----------+-------------+----------+--------+------+------------+---------+
> | Table      | Non_unique | Key_name           | Seq_in_index | Column_name   | 
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +------------+------------+--------------------+--------------+---------------+-
> ----------+-------------+----------+--------+------+------------+---------+
> | Principals |          0 | PRIMARY            |            1 | id            | 
> A         |      101527 |     NULL | NULL   |      | BTREE      |         |
> | Principals |          1 | Principals2        |            1 | ObjectId      | 
> A         |      101527 |     NULL | NULL   | YES  | BTREE      |         |
> | Principals |          1 | user_PrincipalType |            1 | PrincipalType | 
> A         |           4 |     NULL | NULL   |      | BTREE      |         |
> +------------+------------+--------------------+--------------+---------------+-
> ----------+-------------+----------+--------+------+------------+---------+
> 3 rows in set (0.09 sec)
> 
> mysql> show indexes from CachedGroupMembers;
> +--------------------+------------+------------+--------------+-------------+---
> --------+-------------+----------+--------+------+------------+---------+
> | Table              | Non_unique | Key_name   | Seq_in_index | Column_name | 
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
> Comment |
> +--------------------+------------+------------+--------------+-------------+---
> --------+-------------+----------+--------+------+------------+---------+
> | CachedGroupMembers |          0 | PRIMARY    |            1 | id          | A  
>        |      223695 |     NULL | NULL   |      | BTREE      |         |
> | CachedGroupMembers |          1 | DisGrouMem |            1 | GroupId     | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> | CachedGroupMembers |          1 | DisGrouMem |            2 | MemberId    | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> | CachedGroupMembers |          1 | DisGrouMem |            3 | Disabled    | A  
>        |      223695 |     NULL | NULL   |      | BTREE      |         |
> | CachedGroupMembers |          1 | GrouMem    |            1 | GroupId     | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> | CachedGroupMembers |          1 | GrouMem    |            2 | MemberId    | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> | CachedGroupMembers |          1 | GrouMem1   |            1 | MemberId    | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> | CachedGroupMembers |          1 | group1     |            1 | GroupId     | A  
>        |      223695 |     NULL | NULL   | YES  | BTREE      |         |
> +--------------------+------------+------------+--------------+-------------+---
> --------+-------------+----------+--------+------+------------+---------+
> 8 rows in set (0.28 sec)
> 
> 
> 
> 
> -Sheeri Kritzer
> Systems Administrator
> University Systems Group
> Tufts University
> 617-627-3925
> sheeri.kritzer at tufts.edu
> 
> 
> Quoting Jesse Vincent <jesse at bestpractical.com>:
> 
> > 
> > 
> > 
> > On Wed, Oct 20, 2004 at 04:07:53PM -0400, Todd Chapman wrote:
> > > Did you just say that you can add an unprivileged user to a group?
> > 
> > Yeah. Though the UI may currently prevent it.
> > 
> > 
> > > On Wed, Oct 20, 2004 at 03:55:58PM -0400, Jesse Vincent wrote:
> > > > 
> > > > 
> > > > > I also can't really imagine why anyone would want to add someone into a
> > group, 
> > > > > who doesn't have rights to access rt.  Can someone give me a scenario?
> > > >  
> > > > Managing all your customers by adding them to groups for the
> > > > organizations they work for is a common case. That wy you can grant
> > > > unprivileged users more rights like "SeeQueue" and "CreateTicket" for
> > > > queues that are relevant to them.
> > > > 
> > > > 
> > > 
> > 
> > -- 
> > 
> 

-- 



More information about the rt-users mailing list