57350 - "added comments" search is slow or doesn't work at all

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Cesar Eduardo Barros]

When I try to lookup all bugs I've ever touched (go to the query page, select
everything in the first field (the one that starts with three lines already
selected), go to the second email entry field, enter '[e-mail]', and
mark every checkbox, select 'Importance' ordering, and submit), it hangs for too
long (and Mozilla timeouts). This is an old bug (since about two milestones ago
or more); sorry for waiting instead of reporting...

I'm using 2000101908Mtrunk.

Comment 1

[Cesar Eduardo Barros]

Ah, forgot one step: set 'matching as' on the second email field to 'exact'.

Comment 2

[Cesar Eduardo Barros]

(looks again at the bug report)

I should have added: the hang is in the "please wait..." NS2.x-looking gray page.

Sorry for the spam.

Comment 3

[Cesar Eduardo Barros]

Looks like it's the "Added Comment" checkbox which causes the breakage. Tried
without it and it worked.

Comment 4

[Stephan Niemz]

*** Bug 59996 has been marked as a duplicate of this bug. ***

Comment 5

[Stephan Niemz]

I have seen this quite often, too. :-(
Searching for an email also works for me only if the "added comment" option is
unchecked. If it is checked, I'm seeing the "Please stand by ..." page forever
or until a timeout.

Adding dmose's comment from bug 59996:
--------------------------------------------------------------------------------
The following query:

deselect status, uncheck assigned to, check cc and added comment
email 'redhat.com'

Takes a very long time.

The following SQL is used to do the search:

SELECT bugs.bug_id, bugs.groupset, date_format(bugs.creation_ts,'%Y-%m-%d'),
date_format(bugs.delta_ts,'%Y-%m-%d'), substring(bugs.bug_severity, 1, 3),
substring(bugs.priority, 1, 3), substring(bugs.rep_platform, 1, 3),
map_assigned_to.login_name, substring(bugs.bug_status,1,4),
substring(bugs.resolution,1,4), bugs.keywords, substring(bugs.short_desc, 1, 60)
FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT JOIN profiles
map_qa_contact ON bugs.qa_contact = map_qa_contact.userid, longdescs longdescs_
LEFT JOIN profiles longdescnames_ ON longdescs_.who = longdescnames_.userid LEFT
JOIN cc cc_ ON bugs.bug_id = cc_.bug_id LEFT JOIN profiles map_cc_ ON cc_.who =
map_cc_.userid WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.reporter
= map_reporter.userid AND bugs.groupset & 9223372036854775807 = bugs.groupset
AND longdescs_.bug_id = bugs.bug_id AND (INSTR(LOWER(map_cc_.login_name),
'redhat.com') OR INSTR(LOWER(longdescnames_.login_name), 'redhat.com')) GROUP BY
bugs.bug_id ORDER BY map_assigned_to.login_name, bugs.priority, bugs.bug_s

Part of (maybe the entire) problem is due to the fact that the tables aren't
normalized and lots of the fields are substring compares (which therefore can't
use indexes).  Perhaps full-string compares might actually be faster because
indexes could be used.  In any case, need to look at this more closely with
EXPLAIN and figure out what else we can do to speed this up.

Comment 6

[Cesar Eduardo Barros]

Looks like now we also have "CC search is too slow" -- I have to unmark CC: too
or it won't give me any answer besides "please wait". <sigh> Is it just me?

Comment 7

[Stephan Niemz]

The Cc search still works for me.

Comment 8

[Dave Miller [:justdave]]

Hmmmm....

mysql> show columns from longdescs;
+----------+--------------+------+-----+---------------------+-------+
| Field    | Type         | Null | Key | Default             | Extra |
+----------+--------------+------+-----+---------------------+-------+
| bug_id   | mediumint(9) |      | MUL | 0                   |       |
| who      | mediumint(9) |      |     | 0                   |       |
| bug_when | datetime     |      | MUL | 0000-00-00 00:00:00 |       |
| thetext  | mediumtext   | YES  |     | NULL                |       |
+----------+--------------+------+-----+---------------------+-------+
4 rows in set (0.63 sec)

mysql> show index from longdescs;
+-----------+------------+----------+--------------+-------------+-----------+~
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
+-----------+------------+----------+--------------+-------------+-----------+~
| longdescs |          1 | bug_id   |            1 | bug_id      | A         |        
| longdescs |          1 | bug_when |            1 | bug_when    | A         |        
+-----------+------------+----------+--------------+-------------+-----------+~
2 rows in set (0.43 sec)

I don't see an index for the "who" column here...  wouldn't that speed things up 
pretty drastically if there was one?

Comment 9

[Grey Hodge (jX)]

Attachment: Explanation page to show user upon search timeout

Comment 10

[Grey Hodge (jX)]

After a brief discussion in #mozwebtools, it came up that a good idea might be
to tell the user when their search has timed out, rather than letting them hang
for eternity. It was mentioned that the CGI times out after 5 minutes of
inactivity on the connection, and closes it.

A solution for this would be to add
<META HTTP-EQUIV="Refresh" Content="600;searchtooktoolong.html">
to the "Please Wait" page, where the HTML file (attached here) tells the user
what happened.

Comment 11

[Dave Miller [:justdave]]

Attachment: Patch to checksetup.pl to add index(who)

Comment 12

[Jacob Steenhagen]

># This caused queries by who posted attachments to take a LONG time.

Shouldn't that be "who commented on a bug"?  Other than that, the checksetup.pl
patch looks good (and like a good idea).  I don't the kind of data in my
bugzilla that would be required to to test how much real performance this gives,
but it seems like it would help w/the speed.

The HTML file attached to this bug has the Mozilla header and Bugzilla version
hard-coded into it.

Comment 13

[Dave Miller [:justdave]]

Since this is currently a performance impact at b.m.o I'm going to rush this one 
a little, even though it's not exactly a security thing.

Comment 14

[Dave Miller [:justdave]]

Attachment: Patch v2

Comment 15

[Dave Miller [:justdave]]

new patch fixes the comment Jake pointed out.

Comment 16

[Tara Hernandez]

Reviewed and tested dave's patch 2 (I always like performance improvements), but
landfill doesn't have the kind of db size that makes for a good test.

r=tara, but cc-ing myk and dawn so they can also review

Comment 17

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

So far I don't see any performance improvement running the patch on my local
workstation with 78,000 bugs from b.m.o.  I ran a query to find all the bugs I
commented on and listened to my hard drive churn until Bugzilla timed out.  Then
I applied the patch, re-ran checksetup.pl, verified that the index was created
in mysql, and ran the same query.  I got the same results.  I am going to
dissect the query and see if I can figure out why.

Comment 18

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

Attachment: dave's patch plus SQL query optimization

Comment 19

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

I optimized the query by converting an unnecessary LEFT [OUTER] JOIN into an
INNER JOIN (which is the semantic equivalent of separating the tables with a
comma in the FROM clause and moving the join condition into the WHERE clause). 
The "longdescs" and "profiles" tables don't need a LEFT OUTER JOIN because all
longdescs records should contain a valid reference to a profile (and if they
didn't then you certainly couldn't search them by commenter name).

With the INNER JOIN mysql avoids doing an expensive full table scan on the bugs
table, reducing the query time from 10-30 minutes down to about 30 seconds when
I search my test installation for bugs with comments I added.  Adding an index
on the "longdescs.who" column further reduces the query time to under one second.

Comment 20

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

Attachment: query optimizer explanations of the queries

Comment 21

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

It is still not entirely clear to me why the new version of the query works so
much better than the old version.  The query explanations are not definitive. 
However, they do show that the indexed INNER JOIN query is much less expensive
than the others, and that was also the experimental result.

More information about how to find out how mysql optimizes queries:

http://www.mysql.com/doc/E/X/EXPLAIN.html

Comment 22

[Jacob Steenhagen]

Patch looks good to me (both parts).  mozilla.org is probably the only database
with enough data to test the performance part and Myk said it was *much* better.

r=jake - checked in

Now as soon as b.m.o updates to the tip again, this query will be faster :)
 [or just plain work, even]

Comment 23

[Dave Miller [:justdave]]

Moving to Bugzilla product

Comment 24

[Cesar Eduardo Barros]

When will b.m.o update? I still can't do the query I want. I put it in the URL
field with all the useless bits removed.

If it already updated, then it doesn't seem to be working...

Removing the emaillongdesc2=1 from the URL works fine, so it's still the same bug.

Comment 25

[Dave Miller [:justdave]]

Yes, b.m.o did update (several times since then).

Verified this is still present.  I can't find the bug number off the top of my
head, but I know the SQL in question got changed again because the minimum
version of MySQL we support doesn't support INNER JOIN. (but does suppose the
comma separated table names with a WHEN condition that makes them match, which
is what it got changed to).

Reopening.

Comment 26

[Dave Miller [:justdave]]

making the summary match the long description (first comment) on the bug, since
the problem as stated in the original description is the problem that still
exists.  Reassigning to the proper component.

Comment 27

[Matthew Tuck [:CodeMachine]]

Dave don't morph this bug ... as is it was fixed ... the problem is bug #96101.

Comment 28

[Dave Miller [:justdave]]

I'm not morphing it, I'm correcting it.  Jesse Ruderman (not the reporter)
altered the summary, and the problem Jesse changed the summary to reflect is
what got fixed, not the original bug that was reported.  The problem which was
originally reported still exists.  However, since we did check stuff in here,
and the bug you mention does cover exactly the same thing as the originally
stated problem here, I guess I'll re-resolve this one, since we do still have an
open bug on it.

Changing the summary back to what we actually fixed for the records.

Comment 29

[Matthew Tuck [:CodeMachine]]

Oh the wonders of Bugzilla ...

Comment 30

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

Reopening since this bug isn't fixed.  The URL above continues to take too long
to return results (244 seconds and counting as I write this).  I wonder if being
logged in affects the query speed.

Comment 31

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

*** Bug 96101 has been marked as a duplicate of this bug. ***

Comment 32

[Boris 'pi' Piwinger]

The summary is completely missleading. For me the following is very fast:
http://bugzilla.mozilla.org/query.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=RESOLVED&bug_status=VERIFIED&bug_status=CLOSED&email1=bugzilla%40piology.org&emailtype1=exact&emaillongdesc1=1&email2=&emailtype2=substring&bugidtype=include&bug_id=&changedin=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&short_desc=&short_desc_type=allwordssubstr&long_desc=&long_desc_type=allwordssubstr&bug_file_loc=&bug_file_loc_type=allwordssubstr&status_whiteboard=&status_whiteboard_type=allwordssubstr&keywords=&keywords_type=anywords&field0-0-0=noop&type0-0-0=noop&value0-0-0=&namedcmd=CC&newqueryname=added&tofooter=1&order=Reuse+same+sort+as+last+time
BTW: It is not the bug I saved, instead "Assigned To" and "Reporter" (in seconde
filed) have been added. This could also slow down the query.

On the other hand, the following query (which should give all the bugs I
touched) does not work at all:
http://bugzilla.mozilla.org/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=RESOLVED&bug_status=VERIFIED&bug_status=CLOSED&email1=bugzilla%40piology.org&emailtype1=exact&emailreporter1=1&emailcc1=1&emaillongdesc1=1&email2=&emailtype2=substring&bugidtype=include&bug_id=&changedin=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&short_desc=&short_desc_type=allwordssubstr&long_desc=&long_desc_type=allwordssubstr&bug_file_loc=&bug_file_loc_type=allwordssubstr&status_whiteboard=&status_whiteboard_type=allwordssubstr&keywords=&keywords_type=anywords&field0-0-0=noop&type0-0-0=noop&value0-0-0=&cmdtype=doit&namedcmd=added&newqueryname=&order=Reuse+same+sort+as+last+time
The latter is pretty much what the reporter complained about. The former is what
the summary suggests.

pi

Comment 33

[Bradley Baetz (:bbaetz)]

-> 2.18 - 2.14 is long gone. This is probably a mysql bug, though - see comments
in bug 96101.

Comment 34

[Skewer]

The new version of Bugzilla has a warning message on these types of searches:

   You are searching on a combination of email fields that will not return a result
   due to a bug in Bugzilla's database software. When searching on the CC or Added
   comment fields, do not search on other email fields at the same time.

Of course, it's still wrong for it to do this (it should run the search and
work), but there is a message now.

Comment 35

[Boris 'pi' Piwinger]

This is BAD. The following search did work perfectly a few days ago (I used it
many times):
http://bugzilla.mozilla.org/buglist.cgi?bug_status=UNCONFIRMED&email1=bugzilla%40piology.org&emailtype1=exact&emailcc1=1&emaillongdesc1=1&email2=&emailtype2=substring&emailreporter2=1&bugidtype=include&bug_id=&changedin=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&short_desc=&short_desc_type=allwordssubstr&long_desc=&long_desc_type=allwordssubstr&bug_file_loc=&bug_file_loc_type=allwordssubstr&status_whiteboard=&status_whiteboard_type=allwordssubstr&keywords=&keywords_type=anywords&field0-0-0=noop&type0-0-0=noop&value0-0-0=&cmdtype=doit&namedcmd=added&newqueryname=&order=Reuse+same+sort+as+last+time

pi

Comment 36

[Bradley Baetz (:bbaetz)]

This is why bmo was so slow recently. See bug 150969.

Comment 37

[Boris 'pi' Piwinger]

There you say, exact matches would not be the problem. In my example I use exact
match.

pi

Comment 38

[Felix Miata]

Am I the only one who can't get Bugzilla to produce results since yesterday?
Canned searches like bugs already reported today
<http://bugzilla.mozilla.org/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=RESOLVED&email1=&emailtype1=substring&emailassigned_to1=1&email2=&emailtype2=substring&emailreporter2=1&bugidtype=include&bug_id=&changedin=1&votes=&chfield=%5BBug+creation%5D&chfieldfrom=&chfieldto=Now&chfieldvalue=&product=Browser&product=MailNews&short_desc=&short_desc_type=substring&long_desc=&long_desc_type=substring&bug_file_loc=&bug_file_loc_type=substring&status_whiteboard=&status_whiteboard_type=substring&keywords=&keywords_type=anywords&field0-0-0=noop&type0-0-0=noop&value0-0-0=&cmdtype=doit&newqueryname=&order=Reuse+same+sort+as+last+time>
work OK, but searches on reporter and many others are dead-ending in error
messages after interminably long waits. There's no expedient way to find dupes
with it like this.

Comment 39

[Sander]

I'm in the process of updating the named queries I use to quickly navigate the
bugs I know for triaging - and an important part of these bugs are those I've
commented in. 
Creating a new query for "commenter is [e-mail]" for just the
browser product, I noticed how excruciatingly slow this query was, compared to
how fast my old query used to be. This old query I made using advanced boolean
queries, using "comment changed by". The former query took over two and a half
minutes to complete, while the latter takes mere seconds.

Ah, that new query now even gave an error:
Software error:

SELECT bugs.bug_id, bugs.delta_ts, bugs.bug_severity,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.op_sys,
bugs.votes, bugs.target_milestone, bugs.short_desc FROM bugs, profiles AS
map_assigned_to LEFT JOIN longdescs longdescs_email_1 ON bugs.bug_id =
longdescs_email_1.bug_id AND longdescs_email_1.who IN(46544) LEFT JOIN
bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id
NOT IN (9,10)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 46544  WHERE
bugs.assigned_to = map_assigned_to.userid AND longdescs_email_1.who IS NOT NULL
AND (bugs.product_id IN (1)) AND ((bug_group_map.group_id IS NULL)    OR
(bugs.reporter_accessible = 1 AND bugs.reporter = 46544)     OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to =
46544) OR (bugs.qa_contact = 46544) ) GROUP BY bugs.bug_id ORDER BY
bugs.delta_ts desc,bugs.bug_id : Server shutdown in progress at globals.pl line 283.

For help, please send mail to the webmaster ([e-mail]), giving this
error message and the time and date of the error. 


I don't know the bugzilla database structure and haven't really looked at that
query, but it seems to me there's far too much joining and OR-ing going on,
seeing as how I _only_ search for "commenter" "is".
Specifically, I think this part:
OR (bugs.assigned_to = 46544) OR (bugs.qa_contact = 46544)
really has no place in there, and is probably a major reason for why the query
is so slow.

Anyhoo, for ego-searches I suggest people use "comment" "changed by"

Comment 40

[Dave Miller [:justdave]]

*** Bug 210678 has been marked as a duplicate of this bug. ***

Comment 41

[Jouni Heikniemi]

Comment on attachment 39016 [details] [diff] [review]
dave's patch plus SQL query optimization

Bitrotten.

Comment 42

[Joel Peshkin]

These bugs appear to be abandoned.  Retargeting to 2.20

Comment 43

[Shane H. W. Travis]

(In reply to comment #0)
> When I try to lookup all bugs I've ever touched (go to the query page, select
> everything in the first field (the one that starts with three lines already
> selected), go to the second email entry field, enter '[e-mail]',
> and mark every checkbox, select 'Importance' ordering, and submit), it hangs
> for too long (and Mozilla timeouts).

I ran the OP's query at two different times, each time using both IE 6.0 and 
Netscape 7.02. Each time the query took approximately 2m 45sec (+/- 6 seconds), 
and none of the times did the browser time out. (484 bugs returned.) That's 
well under the CGI timeout limit of 5 minutes, and not bad at all (IMHO) for a 
full-spectrum search on a database of ~275k bugs.

Query, for anyone wanting to dissect it, was: 
https://bugzilla.mozilla.org/buglist.cgi?
query_format=advanced&short_desc_type=allwordssubstr&short_desc=&product=&long_d
esc_type=substring&long_desc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&sta
tus_whiteboard_type=allwordssubstr&status_whiteboard=&keywords_type=allwords&key
words=&resolution=&emailassigned_to1=1&emailtype1=exact&email1=&emailassigned_to
2=1&emailreporter2=1&emailqa_contact2=1&emailcc2=1&emaillongdesc2=1&emailtype2=e
xact&email2=[e-mail]&bugidtype=include&bug_id=&votes=&chfieldfrom=&chf
ieldto=Now&chfieldvalue=&cmdtype=doit&order=Importance&field0-0-0=noop&type0-0-
0=noop&value0-0-0=


I submit that hardware speed increases and general schema improvements in the 
last four years have rendered this bug moot. Can it be closed now, Dave?

Comment 44

[Dave Miller [:justdave]]

(In reply to comment #43)
> I submit that hardware speed increases and general schema improvements in the 
> last four years have rendered this bug moot. Can it be closed now, Dave?

Yeah, incredibly likely.  The last reported confirmation of this problem on here
is indeed before the big server move in September 2003, and there were several
query optimizations in Search.pm in between the last two Bugzilla upgrades since
then.

Comment 45

[Sander]

I don't think this should be marked fixed just yet. A search for "commenter is"
takes roughly a minute. A boolean search for "comment changed by" only 5
seconds. That suggests to me that something can be seriously improved either in
the query or the indexing.
QAContact takes 25 seconds versus 15. (might not be statistically relevant)
Reporter takes 10 seconds versus 2 for ReportedBy. (idem)

Comment 46

[Dave Miller [:justdave]]

OK, I'll give you that (for now).

Running both queries, and eliminating the common SQL that both queries share,
the meat of the SQL that's left follows:

commentor is [e-mail] =

SELECT (columnlist)
FROM bugs,
     profiles AS map_assigned_to,
     components AS map_components
     LEFT JOIN longdescs longdescs_LD0 
        ON (longdescs_LD0.bug_id = bugs.bug_id AND longdescs_LD0.who IN (10297))
WHERE bugs.assigned_to = map_assigned_to.userid
  AND bugs.component_id = map_components.id
  AND ((longdescs_LD0.who IS NOT NULL))

comment changed by [e-mail] =

SELECT (columnlist)
FROM bugs,
     profiles AS map_assigned_to,
     components AS map_components,
     longdescs longdescs_0
WHERE bugs.assigned_to = map_assigned_to.userid
  AND bugs.component_id = map_components.id
  AND longdescs_0.bug_id = bugs.bug_id
  AND ((longdescs_0.who = 10297))

As you can see, "commentor is" uses a LEFT JOIN and "comment changed by" uses an
implicit inner join (comma).  Obviously the LEFT JOIN is going to slow it
down...  the big question is if it's necessary to have the LEFT JOIN, and is the
second query potentially missing results because of the lack of it?

The second query will not return a bug if there aren't any comments at all by me
in it.  The first will still return such bugs if other conditions in that "email
 and numbering" block are met.

This is the difference, the first query is doing an OR on each of the items in
that box (if you have multiple boxes checkmarked).  The only way to really fix
this is to special-case it if that's the ONLY box checked to skip the left join.
 If there's more than one box checked, the left join is mandatory though, or
you'll get inaccurate results.

This might be worth trying to implement this special case.  It's common enough
that it trips people up. :)  I'm sure the same situation would apply with the CC
list member checkbox since that's also a joined table.

Comment 47

[Joel Peshkin]

Mysql4 is supposed to recognize the cases where the logic in comment 46 applies
and automatically opotmize it to an INNER JOIN if it is actually equivalent to
do so.  We certainly don't want to change the logic to always use an INNER JOIN
because that will break all sorts of complex queries.  Do we still see the speed
distinction in Mysql4?

My suggestion is that we WONTFIX this or WORKSFORME this.

Comment 48

[Max Kanat-Alexander]

I agree with Joel. If speed is an issue, use MySQL 4 (or maybe PostgreSQL, for
2.20... we'll have to see how that goes).

Comment 49

[Dave Miller [:justdave]]

I did my tests on bmo, which is using MySQL 4, when I threw together that
analysis.  The difference in speed between the two queries in noticible, even on
MySQL 4.

Comment 50

[Joel Peshkin]

Exactly which version of mysql4 does BMO use?
[If less than 4.0.14, that would explain it]

Comment 51

[Dave Miller [:justdave]]

(In reply to comment #50)
> Exactly which version of mysql4 does BMO use?
> [If less than 4.0.14, that would explain it]

We're using 4.0.14 currently. (exactly that version).

Comment 52

[Dave Miller [:justdave]]

*** Bug 291532 has been marked as a duplicate of this bug. ***

Comment 53

[Max Kanat-Alexander]

Okay, it looks like MySQL always just optimizes this query poorly.

We should move the condition into the WHERE clause instead of being part of the JOIN.

Comment 54

[Max Kanat-Alexander]

Attachment: Patch: v3

Okay, this seems to work correctly on landfill, and it should solve the problem. bkor, does this fix it for you on b.g.o?

Comment 55

[Olav Vitters]

Comment on attachment 233418 [details] [diff] [review]
Patch: v3

Ported patch to 2.20 (surrounding code changed, otherwise it is the same). Original query ran in 4 minutes 30 secs. Patch makes it run in about 1 second.

Very big r+ from me.

Comment 56

[Olav Vitters]

PS: b.g.o runs 4.1.20 so that was not the fix

Comment 57

[Max Kanat-Alexander]

I think this is a significant enough perf problem that it would be worth backporting, also.

Comment 58

[Joel Peshkin]

Wait... this will probably break negation.

Comment 59

[Joel Peshkin]

Comment on attachment 233418 [details] [diff] [review]
Patch: v3

This patch will break negation.  If you search for a bug on which justdave   never commented, this will change the query to search for a bug on which someone other than justdave commented.

Comment 60

[Joel Peshkin]

bkor: could you try the original queries with an extra index available for longdescs on (bug_id, commenter) ??

Comment 61

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

(In reply to comment #59)
> (From update of attachment 233418 [details] [diff] [review] [edit])
> This patch will break negation.  If you search for a bug on which justdave  
> never commented, this will change the query to search for a bug on which
> someone other than justdave commented.

Right.  So you could leave the query as it is for negative searches and only change it for positive ones.  Or you could fix the performance problem in the current query.

Comment 62

[Olav Vitters]

Attachment: EXPLAIN SELECT for 'bug_id_foo' longdescs index on (bug_id, who)

After adding an index called 'bug_id_foo' to longdescs on (bug_id, who) the query now took 5 secs. This was 4 min 30 seconds before.

Comment 63

[Olav Vitters]

Almost forgot: mkanats patch was 1 second. Adding the index took 12 minutes.

Comment 64

[Max Kanat-Alexander]

Okay. I think we should make the index on (who, bug_id), since "who" always needs "bug_id" (because of the join), but we often do a search on bug_id without "who". (The index will be smaller and thus faster with only one column, for bug_id.)

Comment 65

[Max Kanat-Alexander]

Attachment: v4

Okay, here's what should be the final patch. bkor, remove the index you added manually, and then let me know if this patch helps.

Comment 66

[Kevin Benton]

Comment on attachment 37470 [details]
Explanation page to show user upon search timeout

I believe this is a non-solution.  The user waited a long time already for the query results.  Rather than throwing this page back at them, we ought to encourage the use of slow query logs to troubleshoot this type of problem.

Comment 67

[Kevin Benton]

Comment on attachment 37470 [details]
Explanation page to show user upon search timeout

This code is based on Bugzilla 2.11 and is obsolete.

Comment 68

[Kevin Benton]

This bug emphasizes how important (for reviewers at least) it is to have a Created By field displayed in the Attachment table.  We're looking at active patches at the moment from as early as 2001-06-06 and as late as 2006-08-19.  I'll go ahead and file a template update bug.  Maybe some would rather see it implemented in the Created box after the date on a second line to keep the table's width down.

Comment 69

[Olav Vitters]

Attachment: Query optimizer output for MySQL 4.1.20, 5.0.24

EXPLAIN SELECT output on Bugzilla CVS for attachment 234612 [details] [diff] [review]. Contains the EXPLAIN SELECT + query time before and after applying the patch.

Basically this problem does not exist on 5.0.24 (only on my 4.1.20), but the different index does not regress the performance in any way.

Comment 70

[Olav Vitters]

Comment on attachment 234609 [details] [diff] [review]
EXPLAIN SELECT for 'bug_id_foo' longdescs index on (bug_id, who)

As per details in attachment 236635 [details]; this increases query time for MySQL 4.1.20 from 4 min 23.81 sec to 11.99 sec. MySQL 5.0.24 was always 0.x seconds.

Comment 71

[Olav Vitters]

Comment on attachment 234609 [details] [diff] [review]
EXPLAIN SELECT for 'bug_id_foo' longdescs index on (bug_id, who)

whoops.. wrong one.

Comment 72

[Olav Vitters]

Comment on attachment 234612 [details] [diff] [review]
v4

See comment 70.

Comment 73

[Joel Peshkin]

Comment on attachment 236635 [details]
Query optimizer output for MySQL 4.1.20, 5.0.24

Wow!

Comment 74

[Max Kanat-Alexander]

I realized that I'd forgotten to also update DB::Schema (for new installations) so I fixed that on checkin.

Checking in Bugzilla/DB/Schema.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema.pm,v  <--  Schema.pm
new revision: 1.67; previous revision: 1.66
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.16; previous revision: 1.15
done