Feature #24553
closed
missing index on table custom_values
0%
Description
Hi,
we were facing a performance issue while querying redmine database to generate some reports over all tickets. We are using lots of custom values and want to have all in the output. Problem was that the MySQL Query was slow and lasts over 10 minutes.
I checked database structure and found that there is an index missing on column customized_id in table custom_values.
After adding the index the query fast (~ 1 sec) and all is fine.
Maybe you can check whether to add the index in the future releases.
We are running:
Redmine version 3.1.1.stable
Ruby version 2.0.0-p384 (2014-01-12) [x86_64-linux-gnu]
Rails version 4.2.4
Server: Ubuntu 14.04.5 LTS
query:
CREATE INDEX custom_values_index1 ON custom_values (customized_id);
I am not sure but maybe this is related to Defect #18696 since this query is using same column.
Regards,
Lars Vandam
Related issues
Updated by Jean-Philippe Lang over 7 years ago
When accessing the custom values of an object, we have to filter on customized_id and customized_type (eg. = 'Issue'). Would it make sense to add the index on [customized_type, customized_id] instead of customized_id alone?
Updated by Toshi MARUYAMA over 7 years ago
- Related to Defect #18696: MySQL Queries are Slow added
Updated by Jean-Philippe Lang over 7 years ago
- Status changed from New to Closed
- Target version deleted (
3.4.0)
Actually, there's already an index on [customized_type, customized_id]. You should always have a condition on both in your queries. I'm closing it, please reopen if you can give some feedback.
Updated by Lars Vandam about 7 years ago
Hi,
sorry for the late feedback.
Our report queries are not using customized_type (its always 'Issue').
Currently our custom_values table has 220K rows and without the index on customized_id it would be too slow.
Regards,
Lars