Redmine

I have a complex hierarchy of around 15000 issues in redmine, where an issue of this set could potentially have 3000 subtasks.

When doing CRUD operations on such issue, I notified significant slow downs, caused by the lock_nested_set function.

Here is the profiling for the query actually run in lock_nested_set:

SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (SELECT root_id FROM issues WHERE id IN (70395,70389)))  ORDER BY `issues`.`id` ASC FOR UPDATE;

+-------+
|       |
.........
| 70371 |
| 70373 |
| 70375 |
| 70377 |
| 70379 |
| 70381 |
| 70383 |
| 70385 |
| 70387 |
| 70389 |
| 70391 |
| 70393 |
+-------+
2932 rows in set (2.70 sec)

mysql> show profile for QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000081 |
| checking permissions           | 0.000003 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000038 |
| System lock                    | 0.000017 |
| init                           | 0.000056 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000023 |
| preparing                      | 0.000009 |
| executing                      | 0.000002 |
| Sorting result                 | 0.000005 |
| Sending data                   | 0.000049 |
| optimizing                     | 0.000015 |
| statistics                     | 0.000047 |
| preparing                      | 2.690165 |
| end                            | 0.000009 |
| query end                      | 0.000067 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000038 |
| logging slow query             | 0.000002 |
| logging slow query             | 0.000163 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

It takes around 3 seconds to execute the whole query.

I think the main problem is with the nested SELECT statement. If I execute it separately, then paste its results directly into the main query, the query is much faster:

mysql> SELECT root_id FROM issues WHERE id IN (70395,70389);
+---------+
| root_id |
+---------+
|   45083 |
+---------+
1 row in set (0.00 sec)

SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (45083))  ORDER BY `issues`.`id` ASC FOR UPDATE;

+-------+
|       |
.........
| 70371 |
| 70373 |
| 70375 |
| 70377 |
| 70379 |
| 70381 |
| 70383 |
| 70385 |
| 70387 |
| 70389 |
| 70391 |
| 70393 |
+-------+
2932 rows in set (0.01 sec)

I am not an expert in sql queries, and don't want to break anything... Shouldn't we use a JOIN instead?

Environment:
  Redmine version                3.3.0.stable
  Ruby version                   2.2.2-p95 (2015-04-13) [x86_64-linux]
  Rails version                  4.2.6
  Environment                    development
  Database adapter               Mysql2