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

[Ruslan Zakirov]

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
>


-- 
Best regards, Ruslan.