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

Status: RESOLVED WORKSFORME

(Bugzilla :: Query/Bug List, defect)

Description

[Olav Vitters]

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.

Comment 1

[Olav Vitters]

Attachment: EXPLAIN SELECT output for both queries

Comment 2

[Olav Vitters]

Attachment: Patch v1

Comment 3

[Max Kanat-Alexander]

Comment on attachment 233444 [details] [diff] [review]
Patch v1

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

Arrr.

Comment 4

[Jon Roland]

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

[Jon Roland]

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

[Frédéric Buclin]

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

[Frédéric Buclin]

The generated SQL is completely different now after several consecutive rewrites of Search.pm.