Using lots of custom fields in Redmine? On a MySQL database? Tired waiting for your searches to complete? Here's a quick fix.

act_as_searchable.md

Here is my modified act_as_searchable.rb (we are still using Redmine 1.4.x but the fix can easily be applied to Redmine 2.x).

Our MySQL version is 5.5.

Using LOWER in LIKE queries is just a waste of time, unless you changed the coalescence of the searched columns, the comparison will be case insensitive. So these should be removed. (Lines 88 and 97).

The main culprit though is the IN subquery for the custom_values table. The WHERE clause also match the customized_id with the parent query id. This is not necessary when using IN. So we removed it from the WHERE clause. However MySQL makes it a DEPENDENT SUBQUERY and will not cache the results. This means that the query will be repeated for every issue. A quick hack is to wrap the subquery in another subquery to make it part of a FROM clause. This, MySQL will create a temporary table for the subquery and reuse it for every issues. (line 97)

Our database contains almost 3000 issues and more than 50000 rows in the custom_values tables. These fixes made a search from Redmine pass from 14 seconds to 3-4 seconds.... From more than 680000 examined row to about 125000 examined rows.

Much better.

acts_as_searchable.rb

# Redmine - project management software
# Copyright (C) 2006-2012  Jean-Philippe Lang
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

module Redmine
  module Acts
    module Searchable
      def self.included(base)
        base.extend ClassMethods
      end

      module ClassMethods
        # Options:
        # * :columns - a column or an array of columns to search
        # * :project_key - project foreign key (default to project_id)
        # * :date_column - name of the datetime column (default to created_on)
        # * :sort_order - name of the column used to sort results (default to :date_column or created_on)
        # * :permission - permission required to search the model (default to :view_"objects")
        def acts_as_searchable(options = {})
          return if self.included_modules.include?(Redmine::Acts::Searchable::InstanceMethods)

          cattr_accessor :searchable_options
          self.searchable_options = options

          if searchable_options[:columns].nil?
            raise 'No searchable column defined.'
          elsif !searchable_options[:columns].is_a?(Array)
            searchable_options[:columns] = [] << searchable_options[:columns]
          end

          searchable_options[:project_key] ||= "#{table_name}.project_id"
          searchable_options[:date_column] ||= "#{table_name}.created_on"
          searchable_options[:order_column] ||= searchable_options[:date_column]

          # Should we search custom fields on this model ?
          searchable_options[:search_custom_fields] = !reflect_on_association(:custom_values).nil?

          send :include, Redmine::Acts::Searchable::InstanceMethods
        end
      end

      module InstanceMethods
        def self.included(base)
          base.extend ClassMethods
        end

        module ClassMethods
          # Searches the model for the given tokens
          # projects argument can be either nil (will search all projects), a project or an array of projects
          # Returns the results and the results count
          def search(tokens, projects=nil, options={})
            if projects.is_a?(Array) && projects.empty?
              # no results
              return [[], 0]
            end

            # TODO: make user an argument
            user = User.current
            tokens = [] << tokens unless tokens.is_a?(Array)
            projects = [] << projects unless projects.nil? || projects.is_a?(Array)

            find_options = {:include => searchable_options[:include]}
            find_options[:order] = "#{searchable_options[:order_column]} " + (options[:before] ? 'DESC' : 'ASC')

            limit_options = {}
            limit_options[:limit] = options[:limit] if options[:limit]
            if options[:offset]
              limit_options[:conditions] = "(#{searchable_options[:date_column]} " + (options[:before] ? '<' : '>') + "'#{connection.quoted_date(options[:offset])}')"
            end

            columns = searchable_options[:columns]
            columns = columns[0..0] if options[:titles_only]

            # REMOVED: LOWER(...) 
            token_clauses = columns.collect {|column| "(#{column} LIKE ?)"}

            if !options[:titles_only] && searchable_options[:search_custom_fields]
              searchable_custom_field_ids = CustomField.find(:all,
                                                             :select => 'id',
                                                             :conditions => { :type => "#{self.name}CustomField",
                                                                              :searchable => true }).collect(&:id)
              if searchable_custom_field_ids.any?
                # REMOVED: LOWER(...) and wrapped subquery in a from clause to enable the use of a temporary table.
                custom_field_sql = "#{table_name}.id IN  (select sq.customized_id from (SELECT customized_id FROM #{CustomValue.table_name}" +
                  " WHERE customized_type='#{self.name}' AND value LIKE ?" +
                  " AND #{CustomValue.table_name}.custom_field_id IN (#{searchable_custom_field_ids.join(',')})) sq)"
                token_clauses << custom_field_sql
              end
            end

            sql = (['(' + token_clauses.join(' OR ') + ')'] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')

            find_options[:conditions] = [sql, * (tokens.collect {|w| "%#{w.downcase}%"} * token_clauses.size).sort]

            scope = self
            project_conditions = []
            if searchable_options.has_key?(:permission)
              project_conditions << Project.allowed_to_condition(user, searchable_options[:permission] || :view_project)
            elsif respond_to?(:visible)
              scope = scope.visible(user)
            else
              ActiveSupport::Deprecation.warn "acts_as_searchable with implicit :permission option is deprecated. Add a visible scope to the #{self.name} model or use explicit :permission option."
              project_conditions << Project.allowed_to_condition(user, "view_#{self.name.underscore.pluralize}".to_sym)
            end
            # TODO: use visible scope options instead
            project_conditions << "#{searchable_options[:project_key]} IN (#{projects.collect(&:id).join(',')})" unless projects.nil?
            project_conditions = project_conditions.empty? ? nil : project_conditions.join(' AND ')

            results = []
            results_count = 0

            scope = scope.scoped({:conditions => project_conditions}).scoped(find_options)
            results_count = scope.count(:all)
            results = scope.find(:all, limit_options)

            [results, results_count]
          end
        end
      end
    end
  end
end