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.
Seems like a pretty huge difference for any possible query optimizer
changes from 4.1.18 -> 4.1.21.
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)
_______________________________________________
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
