0037466: Add bug table indexes on (project_id, status) and last_updated

ID	Project	Category	View Status	Date Submitted	Last Update
0037466	mantisbt	sql	public	2026-10-29 08:30	2026-10-30 06:47

Reporter	khushal	Assigned To
Priority	normal	Severity	minor	Reproducibility	have not tried
Status	acknowledged	Resolution	open

Summary	0037466: Add bug table indexes on (project_id, status) and last_updated
Description	mantis_bug_table has separate indexes on project_id and status, but none on both together, and none on last_updated. A filter that hides closed issues runs: `WHERE project_id = X AND status IN (...)` MySQL can use only one of the two indexes, so on large things it reads all issues of the project (or all open issues of all projects) to show one page. Filters on last_updated read the whole project. Proposed: a new upgrade step that adds idx_bug_project_status on (project_id, status) idx_bug_last_updated on (last_updated)
Tags	No tags attached.

khushal 2026-10-29 08:39 reporter ~0071547	PR: https://github.com/mantisbt/mantisbt/pull/2304

dregad 2026-10-30 05:07 developer ~0071549	As mentioned in the comment I just posted on the PR, it would be nice to have details on the improvements brought by the new proposed indexes (explain plans before/after for the offending queries), and the effective performance gains for a given number of rows in the affected table(s).

khushal 2026-10-30 05:57 reporter ~0071550	I posted the explain plans before/after and the table sizes on the PR: https://github.com/mantisbt/mantisbt/pull/2304 In short, on a bug table of 235,584 rows, rows examined dropped from about 25,000 to 625 for the issue list and count, and from about 110,000 to 3,500 for the "Recently Modified" box.

dregad 2026-10-30 06:47 developer ~0071551	Great, thank you