885409 - MEGA QUERY on bugzilla doesn't finish

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Brandon Johnson [:cyborgshadow]]

Somehow bugzilla is capable of creating this beast of burden below. We should work with the bugzilla team to find out how we can optimize this. :)

Note that this query was running for over 20 minutes without finishing.


SELECT bugs.bug_id AS bug_id, bugs.bug_status AS bug_status, bugs.priority AS priority, CASE WHEN INSTR(CAST(map_assigned_to.login_name AS BINARY), CAST('@' AS BINARY)) != 0 THEN SUBSTR(map_assigned_to.login_name, 1, INSTR(CAST(map_assigned_to.login_name AS BINARY), CAST('@' AS BINARY)) - 1) ELSE map_assigned_to.login_name END AS assigned_to
  FROM bugs
LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id
INNER JOIN profiles AS map_assigned_to ON bugs.assigned_to = map_assigned_to.userid
INNER JOIN bug_status AS map_bug_status ON bugs.bug_status = map_bug_status.value
INNER JOIN priority AS map_priority ON bugs.priority = map_priority.value
INNER JOIN profiles AS name_assigned_to_1 ON bugs.assigned_to = name_assigned_to_1.userid
INNER JOIN profiles AS name_reporter_2 ON bugs.reporter = name_reporter_2.userid
LEFT JOIN (SELECT DISTINCT bug_id FROM cc AS cc_3 INNER JOIN profiles AS name_cc_3 ON cc_3.who = name_cc_3.userid AND  name_cc_3.login_name IN ('[e-mail]') ) AS cc_3_3 ON bugs.bug_id = cc_3_3.bug_id
LEFT JOIN profiles AS name_qa_contact_4 ON bugs.qa_contact = name_qa_contact_4.userid
LEFT JOIN (SELECT DISTINCT bug_id,isprivate FROM longdescs AS commenter_5 INNER JOIN profiles AS name_commenter_5 ON commenter_5.who = name_commenter_5.userid AND  name_commenter_5.login_name IN ('[e-mail]') ) AS commenter_5_5 ON bugs.bug_id = commenter_5_5.bug_id AND commenter_5_5.isprivate = 0
INNER JOIN profiles AS name_assigned_to_6 ON bugs.assigned_to = name_assigned_to_6.userid
INNER JOIN profiles AS name_reporter_7 ON bugs.reporter = name_reporter_7.userid
LEFT JOIN (SELECT DISTINCT bug_id FROM cc AS cc_8 INNER JOIN profiles AS name_cc_8 ON cc_8.who = name_cc_8.userid AND  name_cc_8.login_name IN ('[e-mail]') ) AS cc_8_8 ON bugs.bug_id = cc_8_8.bug_id
LEFT JOIN profiles AS name_qa_contact_9 ON bugs.qa_contact = name_qa_contact_9.userid
LEFT JOIN (SELECT DISTINCT bug_id,isprivate FROM longdescs AS commenter_10 INNER JOIN profiles AS name_commenter_10 ON commenter_10.who = name_commenter_10.userid AND  name_commenter_10.login_name IN ('[e-mail]') ) AS commenter_10_10 ON bugs.bug_id = commenter_10_10.bug_id AND commenter_10_10.isprivate = 0
INNER JOIN profiles AS name_assigned_to_12 ON bugs.assigned_to = name_assigned_to_12.userid
INNER JOIN profiles AS name_reporter_13 ON bugs.reporter = name_reporter_13.userid
LEFT JOIN (SELECT DISTINCT bug_id FROM cc AS cc_14 INNER JOIN profiles AS name_cc_14 ON cc_14.who = name_cc_14.userid AND  name_cc_14.login_name IN ('[e-mail]') ) AS cc_14_14 ON bugs.bug_id = cc_14_14.bug_id
LEFT JOIN profiles AS name_qa_contact_15 ON bugs.qa_contact = name_qa_contact_15.userid
LEFT JOIN (SELECT DISTINCT bug_id,isprivate FROM longdescs AS commenter_16 INNER JOIN profiles AS name_commenter_16 ON commenter_16.who = name_commenter_16.userid AND  name_commenter_16.login_name IN ('[e-mail]') ) AS commenter_16_16 ON bugs.bug_id = commenter_16_16.bug_id AND commenter_16_16.isprivate = 0
INNER JOIN profiles AS name_assigned_to_17 ON bugs.assigned_to = name_assigned_to_17.userid
INNER JOIN profiles AS name_reporter_18 ON bugs.reporter = name_reporter_18.userid
LEFT JOIN (SELECT DISTINCT bug_id FROM cc AS cc_19 INNER JOIN profiles AS name_cc_19 ON cc_19.who = name_cc_19.userid AND  name_cc_19.login_name IN ('[e-mail]') ) AS cc_19_19 ON bugs.bug_id = cc_19_19.bug_id
LEFT JOIN profiles AS name_qa_contact_20 ON bugs.qa_contact = name_qa_contact_20.userid
LEFT JOIN (SELECT DISTINCT bug_id,isprivate FROM longdescs AS commenter_21 INNER JOIN profiles AS name_commenter_21 ON commenter_21.who = name_commenter_21.userid AND  name_commenter_21.login_name IN ('[e-mail]') ) AS commenter_21_21 ON bugs.bug_id = commenter_21_21.bug_id AND commenter_21_21.isprivate = 0
INNER JOIN profiles AS name_reporter_23 ON bugs.reporter = name_reporter_23.userid
 WHERE bugs.creation_ts IS NOT NULL
   AND security_map.group_id IS NULL
   AND  bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')  AND ( (  name_assigned_to_1.login_name IN ('[e-mail]')  OR  name_reporter_2.login_name IN ('[e-mail]')  OR cc_3_3.bug_id IS NOT NULL OR  COALESCE(name_qa_contact_4.login_name, '') IN ('[e-mail]')  OR commenter_5_5.bug_id IS NOT NULL ) AND (  name_assigned_to_6.login_name IN ('[e-mail]')  OR  name_reporter_7.login_name IN ('[e-mail]')  OR cc_8_8.bug_id IS NOT NULL OR  COALESCE(name_qa_contact_9.login_name, '') IN ('[e-mail]')  OR commenter_10_10.bug_id IS NOT NULL ) ) AND ( (  bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED')  AND (  name_assigned_to_12.login_name IN ('[e-mail]')  OR  name_reporter_13.login_name IN ('[e-mail]')  OR cc_14_14.bug_id IS NOT NULL OR  COALESCE(name_qa_contact_15.login_name, '') IN ('[e-mail]')  OR commenter_16_16.bug_id IS NOT NULL ) AND (  name_assigned_to_17.login_name IN ('[e-mail]')  OR  name_reporter_18.login_name IN ('[e-mail]')  OR cc_19_19.bug_id IS NOT NULL OR  COALESCE(name_qa_contact_20.login_name, '') IN ('[e-mail]')  OR commenter_21_21.bug_id IS NOT NULL ) ) AND ( ( bugs.bug_status != 'UNCONFIRMED' OR name_reporter_23.login_name = '[e-mail]' ) ) )
GROUP BY bugs.bug_id
ORDER BY map_bug_status.sortkey, map_bug_status.value, map_priority.sortkey, map_priority.value, assigned_to, bug_id

Comment 1

[Brandon Johnson [:cyborgshadow]]

I'm more concerned about: 
a.) we're not using indices because of the many case statements, functions, etc.  
b.) that's a ton of joins, so we're definitely blowing the join buffer out, forcing this to be a disk table query 
c.) group by x, order by 6x…that's a lot of re-ordering results.

Comment 2

[:glob ✱]

what was the exact date/time this query started?

Comment 3

[Brandon Johnson [:cyborgshadow]]

I can't point to the origin of when this query structure came from. But this particular instance of the query was knowingly causing a higher load on the bugzilla3 database around 9:40 PDT yesterday.

Comment 4

[Brandon Johnson [:cyborgshadow]]

This query seems to have gone away and we haven't seen it in a while. Closing it out.