[Rt-devel] RT 3.6.0 - "New Search" SLOW

[Tim Cutts]

On 26 Jun 2006, at 9:22 pm, Ben Allen wrote:

> Watching on the database, using 'show full processlist' reveals  
> that the query below is consuming a
> large amount of time.
>
> <pre>
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1,  
> CachedGroupMembers CachedGroupMembers_2, Groups Groups_3, ACL ACL_4
> 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.Name ASC
>
>
> Using MySQL's "explain" tool shows that this query searches every  
> record in the 'Users' table (aliased to main).

No it doesn't - at least not as far as I can tell.  It says it's a  
range.  That isn't a full table scan.  What is killing you is the  
filesort.  Try setting the temporary table size threshold in the  
database larger, so that it doesn't use a filesort for it.  The  
default temp table size threshold for using filesort is 32MB, which  
is pretty tiny on current machines.

> +----+-------------+----------------------+-------- 
> +-------------------------+------------+--------- 
> +-------------------------------------+------- 
> +----------------------------------------------+
> | id | select_type | table                | type   |  
> possible_keys           | key        | key_len |  
> ref                                 | rows  |  
> Extra                                        |
> +----+-------------+----------------------+-------- 
> +-------------------------+------------+--------- 
> +-------------------------------------+------- 
> +----------------------------------------------+
> |  1 | SIMPLE      | main                 | range  |  
> PRIMARY,Users3          | PRIMARY    | 4       |  
> NULL                                | 34228 | Using where; Using  
> temporary; Using filesort |
> |  1 | SIMPLE      | Groups_3             | range  |  
> PRIMARY,Groups1,Groups2 | Groups1    | 67      |  
> NULL                                |   498 | Using where; Using  
> index; Distinct           |
> |  1 | SIMPLE      | Principals_1         | eq_ref |  
> PRIMARY                 | PRIMARY    | 4       |  
> rt3.main.id                         |     1 | Using where;  
> Distinct                        |
> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    |  
> DisGrouMem,GrouMem      | DisGrouMem | 10      |  
> rt3.Groups_3.id,rt3.Principals_1.id |     1 | Using where; Using  
> index; Distinct           |
> |  1 | SIMPLE      | ACL_4                | range  |  
> ACL1                    | ACL1       | 54      |  
> NULL                                |     8 | Using where; Using  
> index; Distinct           |
> +----+-------------+----------------------+-------- 
> +-------------------------+------------+--------- 
> +-------------------------------------+------- 
> +----------------------------------------------+
> 5 rows in set (0.00 sec)