From e0ee73ef9963ab398bcc6011795ad23e8e003147 Mon Sep 17 00:00:00 2001 From: Chris Date: Wed, 31 Jan 2018 08:26:40 -0600 Subject: ABC-79: Optimize channel autocomplete query (#8163) * optimize channel autocomplete query * move to new autocomplete endpoint --- store/sqlstore/channel_store.go | 152 +++++++++++++++++++++++++++------------- 1 file changed, 105 insertions(+), 47 deletions(-) (limited to 'store/sqlstore') diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go index af78b06e0..c2979526c 100644 --- a/store/sqlstore/channel_store.go +++ b/store/sqlstore/channel_store.go @@ -7,6 +7,7 @@ import ( "database/sql" "fmt" "net/http" + "sort" "strconv" "strings" @@ -81,6 +82,7 @@ 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") @@ -1257,18 +1259,58 @@ func (s SqlChannelStore) GetMembersForUser(teamId string, userId string) store.S }) } +func (s SqlChannelStore) AutocompleteInTeam(teamId string, term string) store.StoreChannel { + return store.Do(func(result *store.StoreResult) { + queryFormat := ` + SELECT + * + FROM + Channels + WHERE + TeamId = :TeamId + AND Type = 'O' + AND DeleteAt = 0 + %v + LIMIT 50` + + var channels model.ChannelList + + if likeClause, likeTerm := s.buildLIKEClause(term); likeClause == "" { + if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId}); err != nil { + result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) + } + } else { + // Using a UNION results in index_merge and fulltext queries and is much faster than the ref + // query you would get using an OR of the LIKE and full-text clauses. + fulltextClause, fulltextTerm := s.buildFulltextClause(term) + likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause) + fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause) + query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery) + + if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"TeamId": teamId, "LikeTerm": likeTerm, "FulltextTerm": fulltextTerm}); err != nil { + result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) + } + } + + sort.Slice(channels, func(a, b int) bool { + return channels[a].DisplayName < channels[b].DisplayName + }) + result.Data = &channels + }) +} + func (s SqlChannelStore) SearchInTeam(teamId string, term string) store.StoreChannel { return store.Do(func(result *store.StoreResult) { searchQuery := ` SELECT - * + * FROM - Channels + Channels WHERE - TeamId = :TeamId + TeamId = :TeamId AND Type = 'O' AND DeleteAt = 0 - SEARCH_CLAUSE + SEARCH_CLAUSE ORDER BY DisplayName LIMIT 100` @@ -1305,13 +1347,8 @@ func (s SqlChannelStore) SearchMore(userId string, teamId string, term string) s }) } -func (s SqlChannelStore) performSearch(searchQuery string, term string, parameters map[string]interface{}) store.StoreResult { - result := store.StoreResult{} - - // Copy the terms as we will need to prepare them differently for each search type. - likeTerm := term - fulltextTerm := term - +func (s SqlChannelStore) buildLIKEClause(term string) (likeClause, likeTerm string) { + likeTerm = term searchColumns := "Name, DisplayName" // These chars must be removed from the like query. @@ -1324,56 +1361,77 @@ func (s SqlChannelStore) performSearch(searchQuery string, term string, paramete likeTerm = strings.Replace(likeTerm, c, "*"+c, -1) } + if likeTerm == "" { + return + } + + // Prepare the LIKE portion of the query. + var searchFields []string + for _, field := range strings.Split(searchColumns, ", ") { + if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { + searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(%s) escape '*'", field, ":LikeTerm")) + } else { + searchFields = append(searchFields, fmt.Sprintf("%s LIKE %s escape '*'", field, ":LikeTerm")) + } + } + + likeClause = fmt.Sprintf("(%s)", strings.Join(searchFields, " OR ")) + likeTerm += "%" + return +} + +func (s SqlChannelStore) buildFulltextClause(term string) (fulltextClause, fulltextTerm string) { + // Copy the terms as we will need to prepare them differently for each search type. + fulltextTerm = term + + searchColumns := "Name, DisplayName" + // These chars must be treated as spaces in the fulltext query. for _, c := range spaceFulltextSearchChar { fulltextTerm = strings.Replace(fulltextTerm, c, " ", -1) } - if likeTerm == "" { - // If the likeTerm is empty after preparing, then don't bother searching. - searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1) - } else { - // Prepare the LIKE portion of the query. - var searchFields []string - for _, field := range strings.Split(searchColumns, ", ") { - if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { - searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(%s) escape '*'", field, ":LikeTerm")) + // Prepare the FULLTEXT portion of the query. + if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { + splitTerm := strings.Fields(fulltextTerm) + for i, t := range strings.Fields(fulltextTerm) { + if i == len(splitTerm)-1 { + splitTerm[i] = t + ":*" } else { - searchFields = append(searchFields, fmt.Sprintf("%s LIKE %s escape '*'", field, ":LikeTerm")) + splitTerm[i] = t + ":* &" } } - likeSearchClause := fmt.Sprintf("(%s)", strings.Join(searchFields, " OR ")) - parameters["LikeTerm"] = fmt.Sprintf("%s%%", likeTerm) - // Prepare the FULLTEXT portion of the query. - if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { - splitTerm := strings.Fields(fulltextTerm) - for i, t := range strings.Fields(fulltextTerm) { - if i == len(splitTerm)-1 { - splitTerm[i] = t + ":*" - } else { - splitTerm[i] = t + ":* &" - } - } - - fulltextTerm = strings.Join(splitTerm, " ") + fulltextTerm = strings.Join(splitTerm, " ") - fulltextSearchClause := fmt.Sprintf("((%s) @@ to_tsquery(:FulltextTerm))", convertMySQLFullTextColumnsToPostgres(searchColumns)) - searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeSearchClause+" OR "+fulltextSearchClause+")", 1) - } else if s.DriverName() == model.DATABASE_DRIVER_MYSQL { - splitTerm := strings.Fields(fulltextTerm) - for i, t := range strings.Fields(fulltextTerm) { - splitTerm[i] = "+" + t + "*" - } + fulltextClause = fmt.Sprintf("((%s) @@ to_tsquery(:FulltextTerm))", convertMySQLFullTextColumnsToPostgres(searchColumns)) + } else if s.DriverName() == model.DATABASE_DRIVER_MYSQL { + splitTerm := strings.Fields(fulltextTerm) + for i, t := range strings.Fields(fulltextTerm) { + splitTerm[i] = "+" + t + "*" + } - fulltextTerm = strings.Join(splitTerm, " ") + fulltextTerm = strings.Join(splitTerm, " ") - fulltextSearchClause := fmt.Sprintf("MATCH(%s) AGAINST (:FulltextTerm IN BOOLEAN MODE)", searchColumns) - searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", fmt.Sprintf("AND (%s OR %s)", likeSearchClause, fulltextSearchClause), 1) - } + fulltextClause = fmt.Sprintf("MATCH(%s) AGAINST (:FulltextTerm IN BOOLEAN MODE)", searchColumns) } - parameters["FulltextTerm"] = fulltextTerm + return +} + +func (s SqlChannelStore) performSearch(searchQuery string, term string, parameters map[string]interface{}) store.StoreResult { + result := store.StoreResult{} + + likeClause, likeTerm := s.buildLIKEClause(term) + if likeTerm == "" { + // If the likeTerm is empty after preparing, then don't bother searching. + searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1) + } else { + parameters["LikeTerm"] = likeTerm + fulltextClause, fulltextTerm := s.buildFulltextClause(term) + parameters["FulltextTerm"] = fulltextTerm + searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeClause+" OR "+fulltextClause+")", 1) + } var channels model.ChannelList -- cgit v1.2.3-1-g7c22 From c89cf572f7bd22f1e64046b28552dc2e934010ba Mon Sep 17 00:00:00 2001 From: Chris Date: Wed, 31 Jan 2018 13:57:08 -0600 Subject: make channel autocomplete sort case-insensitive (#8176) --- store/sqlstore/channel_store.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'store/sqlstore') diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go index c2979526c..75a615aee 100644 --- a/store/sqlstore/channel_store.go +++ b/store/sqlstore/channel_store.go @@ -1293,7 +1293,7 @@ func (s SqlChannelStore) AutocompleteInTeam(teamId string, term string) store.St } sort.Slice(channels, func(a, b int) bool { - return channels[a].DisplayName < channels[b].DisplayName + return strings.ToLower(channels[a].DisplayName) < strings.ToLower(channels[b].DisplayName) }) result.Data = &channels }) -- cgit v1.2.3-1-g7c22 From 323d717a402c4407ca14c8b94b5eebf861ccf44a Mon Sep 17 00:00:00 2001 From: Joram Wilander Date: Mon, 5 Feb 2018 18:11:31 -0500 Subject: Uncomment upgrade code for 4.7 (#8201) --- store/sqlstore/upgrade.go | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'store/sqlstore') diff --git a/store/sqlstore/upgrade.go b/store/sqlstore/upgrade.go index 7f38e3a73..0de91f28b 100644 --- a/store/sqlstore/upgrade.go +++ b/store/sqlstore/upgrade.go @@ -341,8 +341,8 @@ func UpgradeDatabaseToVersion46(sqlStore SqlStore) { } func UpgradeDatabaseToVersion47(sqlStore SqlStore) { - // if shouldPerformUpgrade(sqlStore, VERSION_4_6_0, VERSION_4_7_0) { - sqlStore.AlterColumnTypeIfExists("Users", "Position", "varchar(128)", "varchar(128)") - // saveSchemaVersion(sqlStore, VERSION_4_7_0) - // } + if shouldPerformUpgrade(sqlStore, VERSION_4_6_0, VERSION_4_7_0) { + sqlStore.AlterColumnTypeIfExists("Users", "Position", "varchar(128)", "varchar(128)") + saveSchemaVersion(sqlStore, VERSION_4_7_0) + } } -- cgit v1.2.3-1-g7c22