Project

General

Profile

Redmine query optimizations

Added by Aleksandar Pavic about 5 years ago

I'm wondering if there is a way to optimize query like this...

I've found it eating-up CPU on one of my servers...

[[https://gist.githubusercontent.com/acosonic/877534f85f7a54e65209c1b0800103cf/raw/9f3b044682dd9949d09780d7b56735c19512f596/gistfile1.txt]]


Replies (2)

RE: Redmine query optimizations - Added by Mayama Takeshi about 5 years ago

I assume there might be some id that could be used to unite all these issues.
Couldn't you just create an extra custom field and use it to filter the issues like:

  SELECT COUNT(*) FROM `custom_values` INNER JOIN issues ON issues.id = custom_values.customized_id INNER JOIN issue_statuses ON issues.status_id=issue_statuses.id WHERE `custom_values`.`custom_field_id` = 16 AND issues.id in (SELECT customized_id FROM custom_values WHERE custom_field_id=NEW_CUSTOM_FIELD_ID AND value=SOME_UNIFYING_VALUE) AND `custom_values`.`value` = 'Maior Domus' AND `issue_statuses`.`is_closed` = 1

If not, since you are using the DB directly I think you could add an extra field into table issues like 'aggregation_id' to filter using it:

  SELECT COUNT(*) FROM `custom_values` INNER JOIN issues ON issues.id = custom_values.customized_id INNER JOIN issue_statuses ON issues.status_id=issue_statuses.id WHERE `custom_values`.`custom_field_id` = 16 AND issues.aggregation_id = 1 AND `custom_values`.`value` = 'Maior Domus' AND `issue_statuses`.`is_closed` = 1
    (1-2/2)