Brian Kerr wrote:
On 7/23/07, Ruslan Zakirov <[EMAIL PROTECTED]> 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.
The above queries were done with status delimiters.
The query breaks whether or not Status is involved. Here is one of
the broken queries without Status. The database query below is
directly from "Requestor.EmailAddress LIKE 'tom' OR Owner = 'tom'"
formed in query builder.
I get the same query out of Oracle when doing this with rt-3.6.4. This
is what I copy from Edit Query->Advanced:
Requestor.EmailAddress LIKE 'Joop%' AND Owner = 'Joop'
Which results in this:
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 ( ( LOWER (users_3.emailaddress) LIKE '%joop%%'
AND cachedgroupmembers_2.ID IS NOT NULL
)
AND main.owner = '62'
)
AND (main.TYPE = 'ticket')
AND (main.effectiveid = main.ID)
And it is 'expensive' in that it does a full table scan of Tickets
because of the %Joop% which will kill any usage of an index on emailaddress.
Plan
SELECT STATEMENT ALL_ROWSCost: 143
11 SORT GROUP BY Bytes: 110 Cardinality: 1 10 NESTED LOOPS
Cost: 143 Bytes: 110 Cardinality: 1 7 NESTED LOOPS Cost:
142 Bytes: 80 Cardinality: 1
4 NESTED LOOPS Cost: 140 Bytes: 65 Cardinality: 1
1 TABLE ACCESS FULL TABLE RT_USER.TICKETS Cost: 138 Bytes:
31 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.GROUPS Cost: 2
Bytes: 34 Cardinality: 1
2 INDEX RANGE SCAN INDEX RT_USER.GROUPS3 Cost: 1
Cardinality: 4
6 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.CACHEDGROUPMEMBERS
Cost: 2 Bytes: 30 Cardinality: 2
5 INDEX RANGE SCAN INDEX RT_USER.GROUPID_IDX Cost: 1
Cardinality: 2
9 TABLE ACCESS BY INDEX ROWID TABLE RT_USER.USERS Cost: 1
Bytes: 30 Cardinality: 1
8 INDEX UNIQUE SCAN INDEX (UNIQUE) RT_USER.USERS_KEY Cost: 0
Cardinality: 1
Changing %Joop% to Joop% gives a index scan instead of full table scan.
This is one of the things changed in our production RT. People need to
add explicitly wildcards and they know that they are in for a wait if
they ask for %text% !
I don't know your ticket/user count but this query performs quite good,
round 200-300msec for 75 rows retrieved.
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 (main.Owner = '58936' OR (
Users_3.EmailAddress LIKE '%tom%' AND CachedGroupMembers_2.id IS NOT
NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)
Joop
_______________________________________________
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
