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

[Nicholas Clark]

On Mon, Jul 23, 2007 at 04:59:27PM +0100, Nicholas Clark wrote:

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

Actually, turns out that you don't really need the index on MemberId, but
it saves about a second here:

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      | Groups_1             | ref    | PRIMARY,Groups1,Groups2    | Groups2    |      65 | const                             | 58630 | Using where | 
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Tickets4,Tickets5  | PRIMARY    |       4 | rt3.Groups_1.Instance             |     1 | Using where | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | PRIMARY,DisGrouMem,GrouMem | DisGrouMem |       5 | rt3.Groups_1.id                   |     2 | Using where | 
|  1 | SIMPLE      | Users_3              | eq_ref | PRIMARY,Users3             | PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |     1 | Using where | 
+----+-------------+----------------------+--------+----------------------------+------------+---------+-----------------------------------+-------+-------------+
4 rows in set (1.44 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 (13.74 sec)


Nicholas Clark