824361 - Add an index on (bug_id, work_time) in the longdescs table to improve performance

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Frédéric Buclin]

Running a query against GCC Bugzilla (~50,000 bugs) with "comment contains the string 'testing'" + "% complete is less than 47" + some others unrelated criteria and displaying all time related fields in the buglist, the current time spent to execute the query on my machine is 3.20 seconds. Adding a index on (bug_id, work_time) in the longdescs table highly improves the time execution: 0.39 second!

EXPLAIN SELECT ... shows that this new index is now used to collect the time spent in the bug (the 'Extra' column now displays 'Using index', which it didn't before). I tried to create an index using 'work_time' alone, but this brings no performance at all. EXPLAIN ... shows that it ignores this index entirely. It only uses it if the index combines both bug_id and work_time so I replaced the existing index with this new one.

I see that bug 526144 asks to include bug_when in this index, but I cannot reproduce the slowness reported by mockodin. Maybe that bug is now irrelevant with the new code we have for comments?

Comment 1

[Frédéric Buclin]

Attachment: patch, v1

Comment 2

[David Lawrence [:dkl]]

Comment on attachment 695320 [details] [diff] [review]
patch, v1

Review of attachment 695320 [details] [diff] [review]:
-----------------------------------------------------------------

Looks good and works as expected. r=dkl

Comment 3

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Schema.pm
modified Bugzilla/Install/DB.pm
Committed revision 8550.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.4/
modified Bugzilla/DB/Schema.pm
modified Bugzilla/Install/DB.pm
Committed revision 8499.