From 91b9514aafbefd5c9c10380878bef1b03245d10c Mon Sep 17 00:00:00 2001 From: Christopher Speller Date: Fri, 27 Oct 2017 06:02:16 -0700 Subject: Adding Posts table indexes for 20M rows. (#7728) --- store/sqlstore/post_store.go | 4 ++++ store/sqlstore/store.go | 1 + store/sqlstore/supplier.go | 21 +++++++++++++++------ 3 files changed, 20 insertions(+), 6 deletions(-) (limited to 'store/sqlstore') diff --git a/store/sqlstore/post_store.go b/store/sqlstore/post_store.go index d8f93d2bc..6faf51c3d 100644 --- a/store/sqlstore/post_store.go +++ b/store/sqlstore/post_store.go @@ -11,6 +11,7 @@ import ( "strings" "bytes" + l4g "github.com/alecthomas/log4go" "github.com/mattermost/mattermost-server/einterfaces" "github.com/mattermost/mattermost-server/model" @@ -73,6 +74,9 @@ func (s SqlPostStore) CreateIndexesIfNotExists() { s.CreateIndexIfNotExists("idx_posts_user_id", "Posts", "UserId") s.CreateIndexIfNotExists("idx_posts_is_pinned", "Posts", "IsPinned") + s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_update_at", "Posts", []string{"ChannelId", "UpdateAt"}) + s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_delete_at_create_at", "Posts", []string{"ChannelId", "DeleteAt", "CreateAt"}) + s.CreateFullTextIndexIfNotExists("idx_posts_message_txt", "Posts", "Message") s.CreateFullTextIndexIfNotExists("idx_posts_hashtags_txt", "Posts", "Hashtags") } diff --git a/store/sqlstore/store.go b/store/sqlstore/store.go index 93d3bf39f..cc43778f5 100644 --- a/store/sqlstore/store.go +++ b/store/sqlstore/store.go @@ -59,6 +59,7 @@ type SqlStore interface { AlterColumnTypeIfExists(tableName string, columnName string, mySqlColType string, postgresColType string) bool CreateUniqueIndexIfNotExists(indexName string, tableName string, columnName string) bool CreateIndexIfNotExists(indexName string, tableName string, columnName string) bool + CreateCompositeIndexIfNotExists(indexName string, tableName string, columnNames []string) bool CreateFullTextIndexIfNotExists(indexName string, tableName string, columnName string) bool RemoveIndexIfExists(indexName string, tableName string) bool GetAllConns() []*gorp.DbMap diff --git a/store/sqlstore/supplier.go b/store/sqlstore/supplier.go index f90639f3e..a90ea6388 100644 --- a/store/sqlstore/supplier.go +++ b/store/sqlstore/supplier.go @@ -547,18 +547,22 @@ func (ss *SqlSupplier) AlterColumnTypeIfExists(tableName string, columnName stri } func (ss *SqlSupplier) CreateUniqueIndexIfNotExists(indexName string, tableName string, columnName string) bool { - return ss.createIndexIfNotExists(indexName, tableName, columnName, INDEX_TYPE_DEFAULT, true) + return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, true) } func (ss *SqlSupplier) CreateIndexIfNotExists(indexName string, tableName string, columnName string) bool { - return ss.createIndexIfNotExists(indexName, tableName, columnName, INDEX_TYPE_DEFAULT, false) + return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_DEFAULT, false) +} + +func (ss *SqlSupplier) CreateCompositeIndexIfNotExists(indexName string, tableName string, columnNames []string) bool { + return ss.createIndexIfNotExists(indexName, tableName, columnNames, INDEX_TYPE_DEFAULT, false) } func (ss *SqlSupplier) CreateFullTextIndexIfNotExists(indexName string, tableName string, columnName string) bool { - return ss.createIndexIfNotExists(indexName, tableName, columnName, INDEX_TYPE_FULL_TEXT, false) + return ss.createIndexIfNotExists(indexName, tableName, []string{columnName}, INDEX_TYPE_FULL_TEXT, false) } -func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string, columnName string, indexType string, unique bool) bool { +func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string, columnNames []string, indexType string, unique bool) bool { uniqueStr := "" if unique { @@ -574,10 +578,15 @@ func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string query := "" if indexType == INDEX_TYPE_FULL_TEXT { + if len(columnNames) != 1 { + l4g.Critical("Unable to create multi column full text index") + os.Exit(EXIT_CREATE_INDEX_POSTGRES) + } + columnName := columnNames[0] postgresColumnNames := convertMySQLFullTextColumnsToPostgres(columnName) query = "CREATE INDEX " + indexName + " ON " + tableName + " USING gin(to_tsvector('english', " + postgresColumnNames + "))" } else { - query = "CREATE " + uniqueStr + "INDEX " + indexName + " ON " + tableName + " (" + columnName + ")" + query = "CREATE " + uniqueStr + "INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")" } _, err := ss.GetMaster().ExecNoTimeout(query) @@ -605,7 +614,7 @@ func (ss *SqlSupplier) createIndexIfNotExists(indexName string, tableName string fullTextIndex = " FULLTEXT " } - _, err = ss.GetMaster().ExecNoTimeout("CREATE " + uniqueStr + fullTextIndex + " INDEX " + indexName + " ON " + tableName + " (" + columnName + ")") + _, err = ss.GetMaster().ExecNoTimeout("CREATE " + uniqueStr + fullTextIndex + " INDEX " + indexName + " ON " + tableName + " (" + strings.Join(columnNames, ", ") + ")") if err != nil { l4g.Critical(utils.T("store.sql.create_index.critical"), err) time.Sleep(time.Second) -- cgit v1.2.3-1-g7c22