Redmine

I've updated the patch slightly, please remove my previous patch. Any comments or suggestions are welcome.

After some refactoring, search requests will be much faster with Redmine 3.0. Here is an example for searching a keyword in the issues of the redmine.org database (14k issues and 45k comments) on my dev machine with PostgreSQL. The search request is:

/search?q=foo&all_words=1&issues=1

With Redmine 2.6:

Completed 200 OK in 2402.4ms (Views: 15.6ms | ActiveRecord: 2371.2ms)

With current trunk (Redmine 3.0):

Completed 200 OK in 905ms (Views: 31.2ms | ActiveRecord: 842.4ms)

The most important thing is that the new queries will now take advantage of indexes if they exist (wasn't the case before 3.0). Here is the indexes for issues and journals tables:

CREATE INDEX issues_subject_description_idx
  ON issues
  USING gin
  (subject COLLATE pg_catalog."default" gin_trgm_ops, description COLLATE pg_catalog."default" gin_trgm_ops);

CREATE INDEX journals_notes_idx
  ON journals
  USING gin
  (notes COLLATE pg_catalog."default" gin_trgm_ops);

After creating these indexes, we get pretty decent response times:

Completed 200 OK in 89ms (Views: 20.0ms | ActiveRecord: 34.0ms)

Thank you for the feedback, that is good news. The change proposed by this patch can still be useful, whenever you search for something and it shows there are 100+ results, it would have been better if it was limited to the last x amount of time as it is sorted by date and you probably won't paginate 10+ pages, if you need older results you just set it to "All time", it makes the "next" button faster. We could also make the default option "All time" and make it configurable. Additionally if the search always searches everything, then it will only get slower as you build a bigger database.

As a side note: would you advise moving from mysql to postgres in preparation for Redmine 3?

Pierre Pretorius wrote:

if you need older results you just set it to "All time", it makes the "next" button faster

I forgot to say that result ids are now cached, making pagination (next/previous links) really, really fast in Redmine in 3.0.

As a side note: would you advise moving from mysql to postgres in preparation for Redmine 3?

The test suite runs with both as you can see here but all the development is done with PostgreSQL. I know PostgreSQL much better and it's my favorite, so I'd say yes. But I didn't try MySQL indexes to improve search request response time.