mirror of
https://codeberg.org/forgejo/forgejo.git
synced 2026-05-12 22:10:25 +00:00
dea9ef6706
Fixes #9963. This realigns all the indexes on the `action` table to best match their intended usages. New: - `IDX_action_created_unix (created_unix)` - Intended for usage in `DeleteOldActions`. - `IDX_action_repo_id_created_unix (repo_id, created_unix)` - Intended for usage when fetching action feeds for a repo and a team, with the same logic as that described below in `IDX_action_user_id_created_unix`. - `IDX_action_repo_id_op_type (repo_id, op_type)` - Intended for `DeleteIssueActions` when it searches for CreateIssue & CreatePullRequest actions for cleanup. Could be optimized further with a denormalization of the issue identifier into a field, but there's no current evidence that this is required. Replaced: - `IDX_action_c_u (created_unix, user_id)` - Replaced with `IDX_action_user_id_created_unix (user_id, created_unix)`. When action feeds are created w/ `ORDER BY created_unix DESC LIMIT 20`, an index beginning with `created_unix` will have to index scan until it can satisfy 20 records; the `user_id` portion of the index is effectively useless until two records appear at the same time. By inverting the order, the database will be able to identify the records created by a user and then pop the most recent 20 from the index order. - At the scale of database I have access to, the performance difference is unmeasurable. This change is supported by theoretical grounds and the findings of #9963, but no experimental evidence. Removed: - `IDX_action_user_id (user_id)` - Redundant with the new `IDX_action_user_id_created_unix`. - `IDX_action_r_u (repo_id, user_id)` - No clear consumer for this index. Retained with no modification: - `IDX_action_comment_id (comment_id)` - Used in `DeleteIssueActions`. - `IDX_action_au_r_c_u (act_user_id, repo_id, created_unix, user_id)` - Heat map generation. ## Checklist The [contributor guide](https://forgejo.org/docs/next/contributor/) contains information that will be helpful to first time contributors. There also are a few [conditions for merging Pull Requests in Forgejo repositories](https://codeberg.org/forgejo/governance/src/branch/main/PullRequestsAgreement.md). You are also welcome to join the [Forgejo development chatroom](https://matrix.to/#/#forgejo-development:matrix.org). ### Tests - I added test coverage for Go changes... - [ ] in their respective `*_test.go` for unit tests. - [ ] in the `tests/integration` directory if it involves interactions with a live Forgejo server. - I added test coverage for JavaScript changes... - [ ] in `web_src/js/*.test.js` if it can be unit tested. - [ ] in `tests/e2e/*.test.e2e.js` if it requires interactions with a live Forgejo server (see also the [developer guide for JavaScript testing](https://codeberg.org/forgejo/forgejo/src/branch/forgejo/tests/e2e/README.md#end-to-end-tests)). ### Documentation - [ ] I created a pull request [to the documentation](https://codeberg.org/forgejo/docs) to explain to Forgejo users how to use this change. - [x] I did not document these changes and I do not expect someone else to do it. ### Release notes - [ ] I do not want this change to show in the release notes. - [x] I want the title to show in the release notes with a link to this pull request. - [ ] I want the content of the `release-notes/<pull request number>.md` to be be used for the release notes instead of the title. Reviewed-on: https://codeberg.org/forgejo/forgejo/pulls/10040 Reviewed-by: Gusted <gusted@noreply.codeberg.org> Co-authored-by: Mathieu Fenniak <mathieu@fenniak.net> Co-committed-by: Mathieu Fenniak <mathieu@fenniak.net>
52 lines
1.7 KiB
Go
52 lines
1.7 KiB
Go
// Copyright 2025 The Forgejo Authors. All rights reserved.
|
|
// SPDX-License-Identifier: GPL-3.0-or-later
|
|
|
|
package forgejo_migrations
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
|
|
"forgejo.org/modules/setting"
|
|
|
|
"xorm.io/xorm"
|
|
)
|
|
|
|
func dropIndexIfExists(x *xorm.Engine, tableName, indexName string) error {
|
|
switch {
|
|
case setting.Database.Type.IsSQLite3(), setting.Database.Type.IsPostgreSQL():
|
|
if _, err := x.Exec(fmt.Sprintf("DROP INDEX IF EXISTS %s", x.Quote(indexName))); err != nil {
|
|
return err
|
|
}
|
|
|
|
case setting.Database.Type.IsMySQL():
|
|
exists, err := indexExists(x, tableName, indexName)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if exists {
|
|
if _, err := x.Exec(fmt.Sprintf("DROP INDEX %s ON %s", x.Quote(indexName), x.Quote(tableName))); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
default:
|
|
return fmt.Errorf("unsupported db dialect type %v", x.Dialect().URI().DBType)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func indexExists(x *xorm.Engine, tableName, indexName string) (bool, error) {
|
|
switch {
|
|
case setting.Database.Type.IsSQLite3():
|
|
return x.SQL("SELECT name FROM sqlite_master WHERE type = 'index' and name = ?", indexName).Exist()
|
|
case setting.Database.Type.IsPostgreSQL():
|
|
return x.SQL("SELECT indexname FROM pg_indexes WHERE schemaname = ? AND tablename = ? AND indexname = ?", setting.Database.Schema, tableName, indexName).Exist()
|
|
case setting.Database.Type.IsMySQL():
|
|
databaseName := strings.SplitN(setting.Database.Name, "?", 2)[0]
|
|
return x.SQL("SELECT `INDEX_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `INDEX_NAME` = ?", databaseName, tableName, indexName).Exist()
|
|
}
|
|
|
|
return false, fmt.Errorf("unsupported db dialect type %v", x.Dialect().URI().DBType)
|
|
}
|