https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292018-07-02T19:46:51ZRedmineRedmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=859952018-07-02T19:46:51ZPavel Rosický
<ul></ul><p>there're already two indexes</p>
<pre>
custom_values_customized [customized_type, customized_id]
index_custom_values_on_custom_field_id [custom_field_id]
</pre>
<p>it would be better to replace custom_values_customized with [customized_type, customized_id, custom_field_id]. We don't need an extra index and I'll still be effective.</p>
<p>60s to 5s seems to be a lot. My db has 140000 issues and 10000000 custom values (mysql 5.7) and I can confirm grouping queries with custom fields are about 50% faster (without db caching). It's an improvement.</p>
<p>What is your db backend? Could you share query plans (explain) of the problematic query (with and without the index)?<br /><a class="external" href="https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html">https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html</a></p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860102018-07-03T14:16:31ZStephane Evr
<ul></ul><p>Here is my DB Version: <code>mysql Ver 15.1 Distrib 10.0.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2</code></p>
<p>Please find below an example of long SQL Query. The original query was much much bigger, but I have isolated a part which was taking a lot of time. For instance, sort the list of issues by a Custom Field:</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">issues</span><span class="p">.</span><span class="o">*</span> <span class="k">FROM</span> <span class="n">issues</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">custom_values</span> <span class="n">cf_34</span>
<span class="k">ON</span> <span class="n">cf_34</span><span class="p">.</span><span class="n">customized_type</span> <span class="o">=</span> <span class="s1">'Issue'</span>
<span class="k">AND</span> <span class="n">cf_34</span><span class="p">.</span><span class="n">customized_id</span> <span class="o">=</span> <span class="n">issues</span><span class="p">.</span><span class="n">id</span>
<span class="k">AND</span> <span class="n">cf_34</span><span class="p">.</span><span class="n">custom_field_id</span> <span class="o">=</span> <span class="mi">34</span>
<span class="k">AND</span> <span class="n">cf_34</span><span class="p">.</span><span class="n">value</span> <span class="o"><></span> <span class="s1">''</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">Coalesce</span><span class="p">(</span><span class="n">cf_34</span><span class="p">.</span><span class="n">value</span><span class="p">,</span> <span class="s1">''</span><span class="p">)</span> <span class="k">DESC</span>
<span class="k">LIMIT</span> <span class="mi">25</span><span class="p">;</span>
</code></pre>
<p>Here Custom Field id is 34, it is of type list in Redmine and its possible values are <code>['OK', 'KO', '']</code><br />The DB contains:<br />- 147262 Issues<br />- 3318197 Custom Values<br />- 51211 Custom Values associated with Custom Field 34</p>
<p><strong>Running the query without the added index Takes 13 seconds:</strong></p>
<pre>
...
25 rows in set (13.64 sec)
</pre>
<p>And the EXPLAIN:</p>
<pre>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: issues
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 147262
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cf_34
type: ref
possible_keys: custom_values_customized,index_custom_values_on_custom_field_id
key: custom_values_customized
key_len: 96
ref: const,redmine_development.issues.id
rows: 14
Extra: Using where
</pre>
<p><strong>Now with the added index, it takes 2 seconds:</strong></p>
<pre>
...
25 rows in set (1.91 sec)
</pre>
<p>And the EXPLAIN:</p>
<pre>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: issues
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 147262
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: cf_34
type: ref
possible_keys: custom_values_customized,index_custom_values_on_custom_field_id,custom_values_customized_custom_field
key: custom_values_customized_custom_field
key_len: 100
ref: const,redmine_development.issues.id,const
rows: 1
Extra: Using where
</pre> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860112018-07-03T14:24:40ZStephane Evr
<ul></ul><p>Pavel Rosický wrote:</p>
<blockquote>
<p>it would be better to replace custom_values_customized with [customized_type, customized_id, custom_field_id]. We don't need an extra index and I'll still be effective.</p>
</blockquote>
<p>I agree we could just replace the existing index with the new one, though I don't know if this may slow down things somewhere else.</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860142018-07-03T15:17:48ZPavel Rosický
<ul></ul><p>ok, the real problem is elsewhere<br /><pre>
SELECT issues.* FROM issues
LEFT OUTER JOIN custom_values cf_34
ON cf_34.customized_type = 'Issue'
AND cf_34.customized_id = issues.id
AND cf_34.custom_field_id = 34
AND cf_34.value <> ''
ORDER BY Coalesce(cf_34.value, '') DESC
LIMIT 25;
</pre></p>
<p>id: 1<br /> select_type: SIMPLE<br /> table: issues<br /> type: ALL<br />possible_keys: NULL<br /> key: NULL<br /> key_len: NULL<br /> ref: NULL<br /> <strong>rows: 147262</strong><br /> <strong>Extra: Using temporary; Using filesort</strong></p>
<p>it means that your db has to load 147262 issues to execute your query. The query is also ordered<br /><pre>
ORDER BY Coalesce(cf_34.value, '') DESC
</pre><br />because it's ordered by a join statement it has to process<br />14 * 147262 rows<br />if you add an index the locality is better, then we have to process only<br />1 * 147262 rows</p>
<p>Maybe we can use inner join for IS/ALL filters (give it a try), but it would require major changes how redmine stores custom values right now. There always has to be a custom value and it also won't work for NULL values. Let's discuss about it in a new ticket if you're interested.<br /><pre>
SELECT issues.* FROM issues
INNER JOIN custom_values cf_34
ON cf_34.customized_type = 'Issue'
AND cf_34.customized_id = issues.id
AND cf_34.custom_field_id = 34
AND cf_34.value <> ''
ORDER BY Coalesce(cf_34.value, '') DESC
LIMIT 25;
</pre></p>
<p>Stephane Evr wrote:</p>
<blockquote>
<p>Pavel Rosický wrote:</p>
<blockquote>
<p>it would be better to replace custom_values_customized with [customized_type, customized_id, custom_field_id]. We don't need an extra index and I'll still be effective.</p>
</blockquote>
<p>I agree we could just replace the existing index with the new one, though I don't know if this may slow down things somewhere else.</p>
</blockquote>
<p>writes could be slower because [customized_type, customized_id, custom_field_id] is more complicated than [customized_type, customized_id]<br />it won't slowdown existing read queries because the order is the same as the previous index, for instance<br /><pre>
SELECT custom_values WHERE customized_type = 'Issue' AND customized_id = 1 can use [customized_type, customized_id, custom_field_id] index
</pre><br />but<br /><pre>
SELECT custom_values WHERE customized_id = 1 AND custom_field_id = 1 can't (not a real case)
</pre></p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860162018-07-03T16:09:11ZStephane Evr
<ul></ul><p>Pavel Rosický wrote:</p>
<blockquote>
<p>because it's ordered by a join statement it has to process<br />14 * 147262 rows<br />if you add an index the locality is better, then we have to process only<br />1 * 147262 rows</p>
<p>Maybe we can use inner join for IS/ALL filters (give it a try), but it would require major changes how redmine stores custom values right now. There always has to be a custom value and it also won't work for NULL values. Let's discuss about it in a new ticket if you're interested.</p>
</blockquote>
<p>Okay, thanks for your comments! One thing I am missing is why 14 rows? I tried with a completely different custom field and there were 14 rows to process as well.</p>
<p>Anyway I will keep this index as it has really decreased the response time of Issue#index by a lot in complex projects. For sure there is a small overhead but only as new issues are created (existing custom values will not be reindexed when their value changes).</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860172018-07-03T20:19:29ZPavel Rosický
<ul></ul><p>Stephane Evr wrote:</p>
<blockquote>
<p>Okay, thanks for your comments! One thing I am missing is why 14 rows? I tried with a completely different custom field and there were 14 rows to process as well.</p>
</blockquote>
<p>It is showing how many rows it ran through to get result (it's just an estimate, not an exact number). It depends on many factors, but if the number of rows is too high the query is probably too complex or indexes are missing.</p>
<blockquote>
<p>Anyway I will keep this index as it has really decreased the response time of Issue#index by a lot in complex projects. For sure there is a small overhead but only as new issues are created (existing custom values will not be reindexed when their value changes).</p>
</blockquote>
<p>I don't see any downsides about this change, so +1</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860182018-07-03T23:35:55ZGo MAEDA
<ul><li><strong>Category</strong> set to <i>Performance</i></li></ul><p>Stephane Evr and Pavel Rosický, thank you for the detailed investigation.</p>
<p>My understanding is that the conclusion is that Redmine should have an index for <code>[:customized_type, :customized_id, :custom_field_id]</code> instead of <code>[customized_type, customized_id]</code>. Is it correct?</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860302018-07-04T10:37:48ZStephane Evr
<ul></ul><p>Go MAEDA wrote:</p>
<blockquote>
<p>Stephane Evr and Pavel Rosický, thank you for the detailed investigation.</p>
<p>My understanding is that the conclusion is that Redmine should have an index for <code>[:customized_type, :customized_id, :custom_field_id]</code> instead of <code>[customized_type, customized_id]</code>. Is it correct?</p>
</blockquote>
<p>Yes</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=860382018-07-04T12:42:50ZGo MAEDA
<ul><li><strong>Target version</strong> set to <i>Candidate for next major release</i></li></ul> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=1082112022-10-12T14:38:31ZGo MAEDA
<ul><li><strong>File</strong> <a href="/attachments/29777">29171.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/29777/29171.patch">29171.patch</a> added</li><li><strong>Target version</strong> changed from <i>Candidate for next major release</i> to <i>5.1.0</i></li></ul><p>Setting the target version to 5.1.0.</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=1085212022-11-10T23:51:13ZGo MAEDA
<ul><li><strong>Subject</strong> changed from <i>Add missing index to custom_values</i> to <i>Add an index to improve the performance of issue queries involving custom fields</i></li><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Assignee</strong> set to <i>Go MAEDA</i></li></ul><p>Committed the patch. Thank you.</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=1107452023-09-13T13:40:46ZHan Boetes
<ul></ul><p>Dear @Go MAEDA</p>
<p>Can this patch be added to 5.0.6, please? 5.1.0 is a long way off and this fix would be very welcome.</p>
<p>Thanks,<br />Han</p> Redmine - Patch #29171: Add an index to improve the performance of issue queries involving custom fieldshttps://www.redmine.org/issues/29171?journal_id=1107502023-09-14T07:40:25ZGo MAEDA
<ul></ul><p>Han Boetes wrote in <a href="#note-12">#note-12</a>:</p>
<blockquote>
<p>Can this patch be added to 5.0.6, please? 5.1.0 is a long way off and this fix would be very welcome.</p>
</blockquote>
<p>I don't think 5.0.6 should include this change, because minor version upgrades of Redmine customarily do not include database migrations.</p>