232150 - bug change queries take forever

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Myk Melez [:myk] [@mykmelez]]

Querying for bugs where any field changed between two dates optimizes poorly and
takes forever, stalling replication in the process.

Comment 1

[Myk Melez [:myk] [@mykmelez]]

Attachment: explanation of one such query

Comment 2

[Bradley Baetz (:bbaetz)]

What changed? What was teh query on the old version?

Comment 3

[Myk Melez [:myk] [@mykmelez]]

Looks like it was the checkin for bug 37749 that did it.  Looks like it only
happens when you select [bug creation] along with the other fields.  Selecting
every field but that one is slow, but returns results after a minute or two.

Here's the relevant portion of the two queries with all the "actcheck.fieldid =
##" stuff replaced by ellipses:

((creation_ts >= '2003-03-09 00:00:00') OR (actcheck.bug_id = bugs.bug_id AND
(...) AND actcheck.bug_when >= '2003-03-09 00:00:00'))
                                          ((actcheck.bug_id = bugs.bug_id AND
(...) AND actcheck.bug_when >= '2003-03-09 00:00:00'))

The first portion is from the failing query; the second is from the working one.

Comment 4

[Bradley Baetz (:bbaetz)]

Right. We should undo that change, and then fix the bug to move bug_creation
into the advanced query options for those who really really want that.

Comment 5

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v1: fixes problem

Comment 6

[Myk Melez [:myk] [@mykmelez]]

Applying to b.m.o so we can get replication working again.

Comment 7

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v2: oops, a couple stray ANDs got left in there

Comment 8

[Bradley Baetz (:bbaetz)]

Comment on attachment 139877 [details] [diff] [review]
patch v2: oops, a couple stray ANDs got left in there

We should remove 'bug creation' from that list, and move it to the advanced
query stuff. Ther another bug on moving it.

r=bbaetz anyway

Comment 9

[Myk Melez [:myk] [@mykmelez]]

Checking in Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.50; previous revision: 1.49
done

Comment 10

[Hamid Tehrani]

I had this problem with my Bugzilla 2.17.6. Bug creation search was very slow. 
But the search on other changes (other than bug creation) worked. When I 
applied this patch it fixed the bug creation problem but now it is very slow 
if I choose other changes. It seems that (in my case) either the bug creation 
can work (using this patch) or other changes but not both. I am not able to 
reproduce this using b.m.o.