59996 - certain queries take too long

Status: VERIFIED DUPLICATE of bug 57350

(Bugzilla :: Bugzilla-General, defect, P3)

Description

[Dan Mosedale (:dmosedale, :dmose)]

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 1

[Jesse Ruderman]

dup of bug 57350?

Comment 2

[Stephan Niemz]

Yes, certainly a dup.

*** This bug has been marked as a duplicate of 57350 ***

Comment 3

[Matthew Tuck [:CodeMachine]]

V.

Comment 4

[Dave Miller [:justdave]]

moving to Bugzilla product
reassign to default owner/qa for INVALID/WONTFIX/WORKSFORME/DUPLICATE