Searching for all bugs exactly matching an email address can be faster

RESOLVED WORKSFORME

Status

()

Bugzilla
Query/Bug List
RESOLVED WORKSFORME
12 years ago
5 years ago

People

(Reporter: Olav Vitters, Unassigned)

Tracking

({perf})

Details

(URL)

Attachments

(2 attachments)

(Reporter)

Description

12 years ago
Above URL creates the following SQL under 2.20 (CVS HEAD Bugzilla/Search.pm is basically the same):

    SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
           bugs.bug_status, bugs.resolution, map_products.name,
           bugs.bug_severity, bugs.priority, bugs.op_sys, map_products.name,
           bugs.bug_status, bugs.resolution, bugs.short_desc, 
           map_assigned_to.login_name
      FROM bugs
INNER JOIN profiles AS map_assigned_to
        ON (bugs.assigned_to = map_assigned_to.userid)
INNER JOIN products AS map_products
        ON (bugs.product_id = map_products.id)
 LEFT JOIN cc AS cc_CC0
        ON (bugs.bug_id = cc_CC0.bug_id AND cc_CC0.who IN(65403))
 LEFT JOIN bug_status
        ON (bug_status.value = bugs.bug_status)
 LEFT JOIN bug_group_map
        ON bug_group_map.bug_id = bugs.bug_id
     WHERE ((cc_CC0.who IS NOT NULL))
       AND bugs.creation_ts IS NOT NULL
       AND ((bug_group_map.group_id IS NULL))
  GROUP BY bugs.bug_id
  ORDER BY bug_status.sortkey,bug_status.value,bugs.priority,
           map_assigned_to.login_name,bugs.bug_id;

Above query takes about 5-6 seconds on bugzilla.gnome.org (even when repeated). The following query however takes slightly above 0 seconds:

    SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
           bugs.bug_status, bugs.resolution, map_products.name,
           bugs.bug_severity, bugs.priority, bugs.op_sys, map_products.name,
           bugs.bug_status, bugs.resolution, bugs.short_desc, 
           map_assigned_to.login_name
      FROM bugs
INNER JOIN profiles AS map_assigned_to
        ON (bugs.assigned_to = map_assigned_to.userid)
INNER JOIN products AS map_products
        ON (bugs.product_id = map_products.id)
 LEFT JOIN cc AS cc_CC0
        ON (bugs.bug_id = cc_CC0.bug_id)
 LEFT JOIN bug_status
        ON (bug_status.value = bugs.bug_status)
 LEFT JOIN bug_group_map
        ON bug_group_map.bug_id = bugs.bug_id
     WHERE ((cc_CC0.who IN(65403)))
       AND bugs.creation_ts IS NOT NULL
       AND ((bug_group_map.group_id IS NULL))
  GROUP BY bugs.bug_id
  ORDER BY bug_status.sortkey,bug_status.value,bugs.priority,
           map_assigned_to.login_name,bugs.bug_id;

Basically the same change as was made in bug 57350. This on MySQL 4.1.20.

I will attach the EXPLAIN SELECT output plus I'll write a patch to implement the change.
(Reporter)

Comment 1

12 years ago
Created attachment 233443 [details]
EXPLAIN SELECT output for both queries
(Reporter)

Comment 2

12 years ago
Created attachment 233444 [details] [diff] [review]
Patch v1
Assignee: query-and-buglist → bugzilla-mozilla
Status: NEW → ASSIGNED
Attachment #233444 - Flags: review?(mkanat)

Comment 3

12 years ago
Comment on attachment 233444 [details] [diff] [review]
Patch v1

I'm fairly sure this will break negation, just like the other patch would.

Arrr.
Attachment #233444 - Flags: review?(mkanat) → review-

Updated

9 years ago
Assignee: bugzilla-mozilla → query-and-buglist

Updated

9 years ago
Status: ASSIGNED → NEW

Comment 4

8 years ago
I need to be able to search on all bugs to which I have made a comment (or anyone else has), and the intuitive way to do that would be to search on my (or his) email address, but that doesn't work. Is this the correct bug to which to comment on this?

It is also intuitive for "My requests" or "Reports" links to offer a way to find the bugs to which I have commented, but that doesn't work, either.

Comment 5

8 years ago
I found how to do it using "Advanced Search". My comment was to make that option available using simple search. I can imagine that others might want to get a list of all bugs on which they have made changes as a one-click option.

Comment 6

5 years ago
Olav: is this still an issue with Bugzilla 4.4? The Search.pm code got major rewrites in 4.2 and 4.4.

Comment 7

5 years ago
The generated SQL is completely different now after several consecutive rewrites of Search.pm.
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → WORKSFORME
You need to log in before you can comment on or make changes to this bug.