[rt-users] query problem after 3.4.5 -> 3.6.4 upgrade

[James Moseley]

Your problem may have something to do with the following statement issued
in the 3.6.4 release notes:

"Use 'Watcher = X' inestead of 'Requestor = X OR Cc = X OR AdminCc = X' in
the SelfService interface. Both queries do quite the same job, but the
former is significantly faster."

I ran into query problems as well when trying to search via Requestor,
AdminCc, Cc, etc, after upgrading to 3.6.4.


James Moseley




                                                                           
             "Brian Kerr"                                                  
             <kerrboy at gmail.co                                             
             m>                                                         To 
             Sent by:                  rt-users at lists.bestpractical.com    
             rt-users-bounces@                                          cc 
             lists.bestpractic                                             
             al.com                                                Subject 
                                       [rt-users] query problem after      
                                       3.4.5 -> 3.6.4 upgrade              
             07/23/2007 09:22                                              
             AM                                                            
                                                                           
                                                                           
                                                                           
                                                                           




Hi,

This ticket system has been upgraded from 2.0.12 -> 3.4.5 -> 3.6.4.
Mysql 4.1.22, Centos 4.4, apache 2.0.52, mod_fastcgi.

When doing a search by requestor/owner email address now, we are
getting a nasty query produced.  Here are the details of the query.
Let me know if you need any more information.

Thanks,
Brian

# Query_time: 621  Lock_time: 0  Rows_sent: 1  Rows_examined: 420186151
SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS JOIN Users
Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role'
) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance =
main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (main.Status !=
'deleted') AND ( ( Users_3.EmailAddress LIKE '%username%' AND
CachedGroupMembers_2.id IS NOT NULL )  OR main.Status = 'new' OR
main.Status = 'open') AND (main.Type = 'ticket') AND (main.EffectiveId
= main.id);

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS
JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain =
'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND (
Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id
) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE
(main.Status != 'deleted') AND ( ( Users_3.EmailAddress LIKE
'%username%' AND CachedGroupMembers_2.id IS NOT NULL )  OR main.Status
= 'new' OR main.Status = 'open') AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id);
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+

| id | select_type | table                | type   | possible_keys
        | key        | key_len | ref                            | rows
 | Extra                    |
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+

|  1 | SIMPLE      | Users_3              | index  | NULL
        | Users4     |     121 | NULL                           |
1609 | Using index              |
|  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2
        | Groups2    |      65 | const                          |
10626 | Using where; Using index |
|  1 | SIMPLE      | main                 | eq_ref |
PRIMARY,Tickets4,Tickets5 | PRIMARY    |       4 |
rt3.Groups_1.Instance          |     1 | Using where              |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    |
DisGrouMem,GrouMem        | DisGrouMem |      10 |
rt3.Groups_1.id,rt3.Users_3.id |     1 | Using where; Using index |
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+

4 rows in set (0.00 sec)
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales at bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com