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

[Nicholas Clark]

On Mon, Jul 23, 2007 at 11:02:10AM -0400, Brian Kerr wrote:
> On 7/23/07, Brian Kerr <kerrboy at gmail.com> wrote:
> >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.
> 
> Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom'
> 
> Forming this query in the query builder will create the nasty SQL.  It
> renders the RT instance unusable and all subsequent queries stack up.

This test instance of RT has a couple more indexes, but it doesn't seem to
matter for this.

I admit to not understanding the SQL fully, but is that LEFT JOIN really
necessary, given the 'IS NOT NULL' check? If the LEFT JOIN becomes an
inner JOIN, and one adds an index to MemberID then sanity prevails:



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                      | Users3     |     125 | NULL                           |  2725 | Using index | 
|  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2,Groups3   | Groups3    |     130 | const,const                    | 49336 | Using where | 
|  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 | 
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+-------------+
4 rows in set (0.05 sec)

mysql> alter table CachedGroupMembers add index (MemberId);
Query OK, 451242 rows affected (20.25 sec)
Records: 451242  Duplicates: 0  Warnings: 0

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                        | Users3     |     125 | NULL                           |  2725 | Using index | 
|  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2,Groups3     | Groups3    |     130 | const,const                    | 49336 | Using where | 
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Tickets4,Tickets5   | PRIMARY    |       4 | rt3.Groups_1.Instance          |     1 | Using where | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,GrouMem,MemberId | DisGrouMem |      10 | rt3.Groups_1.id,rt3.Users_3.id |     1 | Using where | 
+----+-------------+----------------------+--------+-----------------------------+------------+---------+--------------------------------+-------+-------------+
4 rows in set (0.04 sec)

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 ) 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  | PRIMARY,Users3                      | Users3   |     125 | NULL                             | 2725 | Using index | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | PRIMARY,DisGrouMem,GrouMem,MemberId | MemberId |       5 | rt3.Users_3.id                   |    2 | Using where | 
|  1 | SIMPLE      | Groups_1             | eq_ref | PRIMARY,Groups1,Groups2,Groups3     | PRIMARY  |       4 | rt3.CachedGroupMembers_2.GroupId |    1 | Using where | 
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Tickets4,Tickets5           | PRIMARY  |       4 | rt3.Groups_1.Instance            |    1 | Using where | 
+----+-------------+----------------------+--------+-------------------------------------+----------+---------+----------------------------------+------+-------------+
4 rows in set (0.03 sec)

mysql> 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 ) 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);
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                    2777 | 
+-------------------------+
1 row in set (12.87 sec)

mysql> 


I'm just not sure

a: If the query is actually giving the same answer without the LEFT
b: If so, how to patch DBIx::SearchBuilder to generate the better query.

Nicholas Clark