On 7/26/07, Brian Kerr <[EMAIL PROTECTED]> wrote:
On 7/25/07, Ruslan Zakirov <[EMAIL PROTECTED]> wrote:
> Hi, guys.
>
> Thank you for the feedback. Here is patch that should address
> performance problems with queries like:
> "Owner = X OR Requestor = Y"
> "Requestor = Y OR Status = X"
> and other queries where positive search conditions (=, like ...) on
> Requestor, Cc, AdminCc or Watchers are joined with other conditions
> using OR. It doesn't apply to queries with all ANDs, as well it's not
> about searches by links, dates or something else. It's about watchers
> and ORs.
Hi Ruslan,
Thanks for your help. The patch fixed the query in question. There
is another query behind it that is extremely slow under MySQL 4.1.22,
apparently. Under MySQL 4.1.18 it takes 3 seconds - on MySQL 4.1.22
it takes 3minutes. Yikes.
Can you give me EXPLAINs? Wonder if you can give explain of the query
for both versions of mysql?
Keneth, Robert or other RT users, could you please try to reproduce
results Brian sees.
Seems like a pretty huge difference for any possible query optimizer
changes from 4.1.18 -> 4.1.21.
When I'm talking about mysql query optimizer, I'm talking not about
optimizations mysql uses during execution of a query, but about the
mysql query planner which defines order of operations, which indexes
to use and so on. When you run EXPLAIN SELECT... you're executing the
planner and get a plan mysql will use to execute the query. Even minor
changes in a plan can kill performance.
Ok, we have another variant of the query. Remember that third query we
tested before with COUNT(...)? Try the following query:
SELECT SQL_NO_CACHE DISTINCT main.*
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
WHERE
main.Status != 'deleted'
AND main.Type = 'ticket'
AND main.EffectiveId = main.id
AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' )
ORDER BY main.id ASC LIMIT 50
May be we should switch to this variant even if counting ticket with
it is slower.
Looks like I might need to downgrade mysql.
SELECT DISTINCT main.* 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
(Users_3.EmailAddress LIKE '%tom%') AND (main.Status != 'deleted') AND
(main.Owner = '58936' OR ( CachedGroupMembers_2.id IS NOT NULL ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY
main.id ASC LIMIT 50
50 rows in set (3 min 2.12 sec)
--
Best regards, Ruslan.
_______________________________________________
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
