Here’s an easy way to speed up Redmine’s search if you’re using PostgreSQL.
How Redmine Search Works
If you know Redmine, you know there’s a slew of different kinds of objects, from projects and issues over commit messages and news to forum messages and wiki pages. Each of these data types is stored in their own table. When you run a search, Redmine builds SQL queries that look like this:
As you can see, besides checking for project ids and status and whether the project actually has the Issue Tracking module enabled, the query has
LIKE statements for the subject and description fields of the
That is just the query for issues, there will be one like this for every entity you are searching through, plus one for custom field values, and if you have ever worked with relational databases you’re starting to see the problem:
Search is done using
LIKE '%query%', and such queries usually cannot make use of any indexes, leading to a full scan of all rows that match the other criteria in the query. Ouch!
How to make it faster?
There are a few possible solutions to the problem:
You could rework the way search is implemented and use a fulltext indexing engine, either built into your database (MySQL and PostgreSQL both can do that), or an external engine like Solr or ElasticSearch.
I took the DB-Engine approach with the Redmine PostgreSQL Full Text Search Plugin before. It works great in some scenarios where it can return more relevant search results in shorter times, but it comes with a few problems as well. First of all, it does not support multi-lingual data sets well because you have to decide on a language definition for tokenization / stemming when configuring it. Whatever language you pick, chances are words from another language will get the wrong treatment, leading to false results.
This approach in general does not play nicely with new data types introduced by other plugins and, because rewriting / monkey patching of the core search is necessaary, is brittle when core Redmine changes anyhting about the search in new versions.
Get rid of the postfix part in the LIKE
A somewhat creative solution to the problem is to have two conventional B-tree indexes per column, i.e. one over
subject, and one over the reverse value of it.
subject LIKE 'foo%' OR reverse(subject) LIKE 'oof%' will then lead to fast index based searches results.
One problem here is that MySQL cannot index expressions but only ‘real’ columns, so you have to actually store the reverse value in it’s own column, which, together with the additional index, will roughly double the size of your database. Regardless of the database being used, Redmine’s search code will have to be changed or monkey patched, leading to the same problems with other plugins and Redmine updates I already mentioned above.
A better approach would be to find a way to speed up that exact query on the database side, without having to change anything about the way Redmine search is implemented. That would solve the performance issue, while keeping full compatibility with other plugins and future Redmine updates.
Guess what, PostgreSQL indeed comes with a solution to this problem: The trigram index, implemented in the pg_trgm module. Trigrams are the result of breaking up text into 3-letter groups like this:
After enabling the
pg_trgm extension for your DB, you can create a trigram index like that:
From now on, whenever there’s an
issues.description LIKE '%query%' kind of query, that index can and will be used by PostgreSQL to speed up the search.
Armed with that knowledge, you’re ready to…
Speed Up Your Redmine Search in Two Minutes
All you have to do is add the
pg_trgm extension to your database and create trigram indexes for all the columns that are queried by Redmine search:
There’s also a rake task which generates and runs the above statements.
Just put the file into
lib/tasks of your Redmine installation, run
bundle exec rake redmine:pg_trgm:setup and you’re done.
Note: While not strictly necessary I added the
concurrently keyword which will run the actual index creation in the background.
Thus, on large datasets, it might take a few minutes or more until the indizes are actually available.
If you are using any plugins that come with their own searchable data stuctures, you can of course add similar indexes for these as well.