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 _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
