Remove the index on Channels.DisplayName. (#8530)

As outlined in [this
discussion](https://pre-release.mattermost.com/core/pl/uw5bwmkb6irkbkn6pk9rkzpytr),
this index causes issues with MySQL's query planner, leading to full
table scans in a case where it would have made more sense to leverage a
filesort.

2 files changed, 21 insertions, 3 deletions

diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go
index e7a157192..fe4c561ca 100644
--- a/store/sqlstore/channel_store.go
+++ b/store/sqlstore/channel_store.go
@@ -90,7 +90,6 @@ func NewSqlChannelStore(sqlStore SqlStore, metrics einterfaces.MetricsInterface)
 func (s SqlChannelStore) CreateIndexesIfNotExists() {
 	s.CreateIndexIfNotExists("idx_channels_team_id", "Channels", "TeamId")
 	s.CreateIndexIfNotExists("idx_channels_name", "Channels", "Name")
-	s.CreateIndexIfNotExists("idx_channels_displayname", "Channels", "DisplayName")
 	s.CreateIndexIfNotExists("idx_channels_update_at", "Channels", "UpdateAt")
 	s.CreateIndexIfNotExists("idx_channels_create_at", "Channels", "CreateAt")
 	s.CreateIndexIfNotExists("idx_channels_delete_at", "Channels", "DeleteAt")
diff --git a/store/sqlstore/upgrade.go b/store/sqlstore/upgrade.go
index 174b434f8..89c6e3d07 100644
--- a/store/sqlstore/upgrade.go
+++ b/store/sqlstore/upgrade.go
@@ -17,7 +17,9 @@ import (
 
 const (
 	VERSION_4_9_0            = "4.9.0"
+	VERSION_4_8_1            = "4.8.1"
 	VERSION_4_8_0            = "4.8.0"
+	VERSION_4_7_2            = "4.7.2"
 	VERSION_4_7_1            = "4.7.1"
 	VERSION_4_7_0            = "4.7.0"
 	VERSION_4_6_0            = "4.6.0"
@@ -69,7 +71,9 @@ func UpgradeDatabase(sqlStore SqlStore) {
 	UpgradeDatabaseToVersion46(sqlStore)
 	UpgradeDatabaseToVersion47(sqlStore)
 	UpgradeDatabaseToVersion471(sqlStore)
+	UpgradeDatabaseToVersion472(sqlStore)
 	UpgradeDatabaseToVersion48(sqlStore)
+	UpgradeDatabaseToVersion481(sqlStore)
 	UpgradeDatabaseToVersion49(sqlStore)
 
 	// If the SchemaVersion is empty this this is the first time it has ran
@@ -367,19 +371,33 @@ func UpgradeDatabaseToVersion471(sqlStore SqlStore) {
 	}
 }
 
+func UpgradeDatabaseToVersion472(sqlStore SqlStore) {
+	if shouldPerformUpgrade(sqlStore, VERSION_4_7_1, VERSION_4_7_2) {
+		sqlStore.RemoveIndexIfExists("idx_channels_displayname", "Channels")
+		saveSchemaVersion(sqlStore, VERSION_4_7_2)
+	}
+}
+
 func UpgradeDatabaseToVersion48(sqlStore SqlStore) {
-	if shouldPerformUpgrade(sqlStore, VERSION_4_7_1, VERSION_4_8_0) {
+	if shouldPerformUpgrade(sqlStore, VERSION_4_7_2, VERSION_4_8_0) {
 		saveSchemaVersion(sqlStore, VERSION_4_8_0)
 	}
 }
 
+func UpgradeDatabaseToVersion481(sqlStore SqlStore) {
+	if shouldPerformUpgrade(sqlStore, VERSION_4_8_0, VERSION_4_8_1) {
+		sqlStore.RemoveIndexIfExists("idx_channels_displayname", "Channels")
+		saveSchemaVersion(sqlStore, VERSION_4_8_1)
+	}
+}
+
 func UpgradeDatabaseToVersion49(sqlStore SqlStore) {
 	// This version of Mattermost includes an App-Layer migration which migrates from hard-coded roles configured by
 	// a number of parameters in `config.json` to a `Roles` table in the database. The migration code can be seen
 	// in the file `app/app.go` in the function `DoAdvancedPermissionsMigration()`.
 
 	//TODO: Uncomment the following condition when version 4.9.0 is released
-	//if shouldPerformUpgrade(sqlStore, VERSION_4_8_0, VERSION_4_9_0) {
+	//if shouldPerformUpgrade(sqlStore, VERSION_4_8_1, VERSION_4_9_0) {
 	sqlStore.CreateColumnIfNotExists("Teams", "LastTeamIconUpdate", "bigint", "bigint", "0")
 	defaultTimezone := model.DefaultUserTimezone()
 	defaultTimezoneValue, err := json.Marshal(defaultTimezone)
@@ -387,6 +405,7 @@ func UpgradeDatabaseToVersion49(sqlStore SqlStore) {
 		l4g.Critical(err)
 	}
 	sqlStore.CreateColumnIfNotExists("Users", "Timezone", "varchar(256)", "varchar(256)", string(defaultTimezoneValue))
+	sqlStore.RemoveIndexIfExists("idx_channels_displayname", "Channels")
 	//	saveSchemaVersion(sqlStore, VERSION_4_9_0)
 	//}
 }