226284 - slow queries for creation_ts

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Benjamin Coles]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5a) Gecko/20030806 Mozilla Firebird/0.6.1
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5a) Gecko/20030806 Mozilla Firebird/0.6.1

The Search.pm was rewritten in 2.17.6, the query no longer searches for
bugs.creation_ts which causes the server to lag while it checks every table for
creation_ts. Tried to fix code to look like 2.17.4 or add an additional bug. in
front of creation_ts and no avail, had to downgrade Search.pm to 2.17.4 version
while at 2.17.6. Running smoothly again

Reproducible: Always

Steps to Reproduce:
1.Do a search that involves the creation_ts variable
2.
3.

Actual Results:  
took 5 minutes do get a returned query

Expected Results:  
should have been instantanous like previous version

query &debug=1 returned bug.creation_ts in 2.17.4 and just creation_ts in 2.17.6

Comment 1

[Dave Miller [:justdave]]

can you give any more specific steps to reproduce?  (exactly what was chosen on
the query page, etc)  I can't reproduce this on landfill at all.

http://landfill.bugzilla.org/bugzilla-tip/query.cgi

Comment 2

[Benjamin Coles]

As explained on #mozwebtools - it was reproduced in landfill by selecting a 2
week old date and the search took  5 minutes to complete where as on 2.17.4
*wasn't on landfill* would get it done in 2 seconds - The &debug=1 produced the
same results as my bugzilla installtion. It was missing the bug. in front of
creation_ts so it forces mysql to find the table it belongs to.

2.17.6
SEARCHING creation_ts

supposed to be 
2.17.4
SEARCHING bugs.creation_ts

Comment 3

[Benjamin Coles]

Bad Query as requested by bbaetz:

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, bugs_activity actcheck LEFT JOIN
bug_group_map ON bug_group_map.bug_id = bugs.bug_id AND bug_group_map.group_id
NOT IN (19,9,3,16,17,4,12,1,6,8,13,2,5,18,7) LEFT JOIN cc ON cc.bug_id =
bugs.bug_id AND cc.who = 8596 WHERE bugs.assigned_to = map_assigned_to.userid
AND ((creation_ts >= '2003-11-23 01:00:00')) AND (bugs.bug_status IN
('UNCONFIRMED','NEW','ASSIGNED')) AND ((bug_group_map.group_id IS NULL) OR
(bugs.reporter_accessible = 1 AND bugs.reporter = 8596) OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 8596)
) GROUP BY bugs.bug_id ORDER BY bugs.bug_status, bugs.priority,
map_assigned_to.login_name, bugs.bug_id

Comment 4

[Dave Miller [:justdave]]

2.17.4 on landfill gives me this:

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON
bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to =
map_assigned_to.userid AND (bugs.creation_ts > '2003-11-23 01:00:00') AND
((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id

and returns almost instantly with 0 results

2.17.6 on landfill gives me this:

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, bugs_activity actcheck LEFT JOIN
bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to =
map_assigned_to.userid AND ((creation_ts >= '2003-11-23 01:00:00')) AND 1 = 1
AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id

and also returns almost instantly with 0 results.

There's a join on bugs_activity on 2.17.6 that wasn't there on 2.17.4.  I would
be much more suspect of that, than the lack of "bugs."  There's nothing in the
where part or any of the join conditions to tell it how to join on bugs_activity
and it's not selecting anything from it.  I would wonder if the parser is
getting really confused by that... :)

Comment 5

[Bradley Baetz (:bbaetz)]

Attachment: fix

We don't want to push the activities table in unless we're actually going to
use it.

Comment 6

[Bradley Baetz (:bbaetz)]

Mine.

Comment 7

[Bradley Baetz (:bbaetz)]

Fixed