[rt-users] searching for users takes forever

[Sheeri Kritzer]

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.

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.
> > > 
> > > 
> > 
> 
> -- 
>