Improve tag search query (#16104)

1 files changed, 6 insertions, 8 deletions

diff --git a/app/models/tag.rb b/app/models/tag.rb
index bb93a52e2..efffc7eee 100644
--- a/app/models/tag.rb
+++ b/app/models/tag.rb
@@ -40,7 +40,8 @@ class Tag < ApplicationRecord
   scope :trendable, -> { Setting.trendable_by_default ? where(trendable: [true, nil]) : where(trendable: true) }
   scope :discoverable, -> { listable.joins(:account_tag_stat).where(AccountTagStat.arel_table[:accounts_count].gt(0)).order(Arel.sql('account_tag_stats.accounts_count desc')) }
   scope :recently_used, ->(account) { joins(:statuses).where(statuses: { id: account.statuses.select(:id).limit(1000) }).group(:id).order(Arel.sql('count(*) desc')) }
-  scope :matches_name, ->(value) { where(arel_table[:name].matches("#{value}%")) }
+  # Search with case-sensitive to use B-tree index.
+  scope :matches_name, ->(term) { where(arel_table[:name].lower.matches(arel_table.lower("#{sanitize_sql_like(Tag.normalize(term))}%"), nil, true)) }
 
   delegate :accounts_count,
            :accounts_count=,
@@ -126,10 +127,9 @@ class Tag < ApplicationRecord
     end
 
     def search_for(term, limit = 5, offset = 0, options = {})
-      normalized_term = normalize(term.strip)
-      pattern         = sanitize_sql_like(normalized_term) + '%'
-      query           = Tag.listable.where(arel_table[:name].lower.matches(pattern))
-      query           = query.where(arel_table[:name].lower.eq(normalized_term).or(arel_table[:reviewed_at].not_eq(nil))) if options[:exclude_unreviewed]
+      striped_term = term.strip
+      query = Tag.listable.matches_name(striped_term)
+      query = query.merge(matching_name(striped_term).or(where.not(reviewed_at: nil))) if options[:exclude_unreviewed]
 
       query.order(Arel.sql('length(name) ASC, name ASC'))
            .limit(limit)
@@ -145,7 +145,7 @@ class Tag < ApplicationRecord
     end
 
     def matching_name(name_or_names)
-      names = Array(name_or_names).map { |name| normalize(name).mb_chars.downcase.to_s }
+      names = Array(name_or_names).map { |name| arel_table.lower(normalize(name)) }
 
       if names.size == 1
         where(arel_table[:name].lower.eq(names.first))
@@ -154,8 +154,6 @@ class Tag < ApplicationRecord
       end
     end
 
-    private
-
     def normalize(str)
       str.gsub(/\A#/, '')
     end