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

[Ruslan Zakirov]

On 7/23/07, Forrest Blount <forrest at itasoftware.com> wrote:
> I believe that's what Brian was saying.
I don't, the original query Brian sent is different  and selects only
by Requestor and Status properties. The most important part of that
query is ORs everywhere. If the query is built using the query builder
and that's what Brian wants then it's ok and we go on with performance
problem. But if the query is generated in our code then we must start
looking for a bug as I don't see a reason why we generate it instead
of something like:
"Requestor.EmailAddress = X *AND* ( Status = 'new' OR Status = 'open' )"


>
> (from a follow-up email from kerrboy at gmail.com):
> > 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.
> Ruslan, is it possible this is related to your update to
> Tickets.Overlay.pm on 2/13/07?
>
> I ask because the code in _WatcherMembershipLimit  seems to be
> responsible for the query Brian pasted...
It's related and I'm trying to figure out if we can improve
performance without loosing fixes I've added.

Can people send me explains for the following two queries:
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);

explain SELECT COUNT(DISTINCT main.id)
    FROM Tickets main
    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.GroupId = Groups_1.id
    LEFT JOIN Users Users_3
        ON CachedGroupMembers_2.MemberId = Users_3.id
        AND Users_3.EmailAddress LIKE '%username%'
WHERE main.Status != 'deleted'

AND ( ( Users_3.id IS NOT NULL )
    OR main.Status = 'new' OR main.Status = 'open')
    AND (main.Type = 'ticket')
    AND (main.EffectiveId = main.id);


>
> Ruslan Zakirov wrote:
> > Do I understand right that the query is like "Requestor.EmailAddress =
> > 'xxx' OR Status = 'new'  OR Status = 'open'"?
> > * Note all binary operators (aggregators) are ORs.
> >
> >
> > 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.
> >>
> >> 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
> >>
> >
> >
>
> _______________________________________________
> 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
>


-- 
Best regards, Ruslan.