[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