Redmine

In issue page, the changeset display is slow when changeset_issues has very many records.

The following query is executed.

SELECT
  `changesets`.*
FROM
  `changesets`
  INNER JOIN `repositories`
    ON `repositories`.`id` = `changesets`.`repository_id`
  INNER JOIN `projects`
    ON `projects`.`id` = `repositories`.`project_id`
  INNER JOIN `changesets_issues`
    ON `changesets`.`id` = `changesets_issues`.`changeset_id`
WHERE
  `changesets_issues`.`issue_id` = 1
  ...

There is no INDEX in issue_id of changesets_issues table.

• db/schema.rb
  

    create_table "changesets_issues", id: false, force: :cascade do |t|
      t.integer "changeset_id", null: false
      t.integer "issue_id",     null: false
    end
  
    add_index "changesets_issues", ["changeset_id", "issue_id"], name: "changesets_issues_ids", unique: true, using: :btree
  

As a result, this query may be slow if there are a very many records in the changesets_issues table.

When the number of records in the changesets_issues table was about 1 million, it took 2 or 3 seconds to execute this query.
Adding INDEX to issue_id improved the performance of this query to a few milliseconds.