<p>This is really difficult to provide a proper answer for in its current form, so I'll give some general advice when troubleshooting things like this.</p>

<ol>
<li><p>Try to limit the complexity of the Twig code building the query.  It might help to break it up into multiple, smaller queries instead of executing everything as one monolithic query.</p></li>
<li><p>Don't do this in Twig. Break out into a plugin/PHP using a <a href="http://buildwithcraft.com/classreference/services/TemplatesService#hook-detail" rel="nofollow">template hook</a> and grab only the data you need using <a href="http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder" rel="nofollow">query builder</a>. Again, it might help to break things up into multiple, smaller queries even from PHP.</p></li>
<li><p>Enable <a href="http://buildwithcraft.com/docs/config-settings#devMode" rel="nofollow">devMode</a> and find the resulting SQL that is generated.  Use standard MySQL <a href="https://www.google.com/search?q=debugging%20mysql%20slow%20queries" rel="nofollow">slow query debugging techniques</a> to see where the bottleneck is and optimize from that information.</p></li>
<li><p>Finally, you can try to throw more resources at your server and brute force your way though it.</p></li>
</ol>

CC BY-SA 3.0
2015-08-20T17:48:09.947Z
2015-08-20T17:48:09.947Z
Brad Bell
<p>This complex query makes the server cpu run at 100%.</p>

<pre><code>{% set entries = craft.entries({
      section: sectionParam,
      search: searchParam,
      relatedTo: relatedToParams,
      condition: conditionParam,
      price: [
          'and',
          '&gt;= ' ~ pricesParam|split(';')[0],
          '&lt;= ' ~ pricesParam|split(';')[1]
      ],
      priceOffer: [
          'and',
          '&gt;= ' ~ pricesParam|split(';')[0],
          '&lt;= ' ~ pricesParam|split(';')[1]
      ],
      specsMtplm: weightParam,
      specInternalLengthFeet: lengthParam,
      priceOffer: '',
      specsBerthSleeps: [
          'and',
          '&gt;= ' ~ berthsParam|split(';')[0],
          '&lt;= ' ~ berthsParam|split(';')[1]
      ],
      spectransmission: transParam,
      seatbelts: seatbeltsParam

}).order('dateCreated desc').limit(null) %}
</code></pre>

<p>How can I optimise this twig so it doesn't kill the server each time it runs?</p>

<entries><database><performance>
How do I optimise this colossal query?
# How do I optimise this colossal query?

(Asked by: username_0 on 2015-08-20)

This complex query makes the server cpu run at 100%.
{% set entries = craft.entries({
      section: sectionParam,
      search: searchParam,
      relatedTo: relatedToParams,
      condition: conditionParam,
      price: [
          'and',
          '>= ' ~ pricesParam|split(';')[0],
          '<= ' ~ pricesParam|split(';')[1]
      ],
      priceOffer: [
          'and',
          '>= ' ~ pricesParam|split(';')[0],
          '<= ' ~ pricesParam|split(';')[1]
      ],
      specsMtplm: weightParam,
      specInternalLengthFeet: lengthParam,
      priceOffer: '',
      specsBerthSleeps: [
          'and',
          '>= ' ~ berthsParam|split(';')[0],
          '<= ' ~ berthsParam|split(';')[1]
      ],
      spectransmission: transParam,
      seatbelts: seatbeltsParam

}).order('dateCreated desc').limit(null) %}

How can I optimise this twig so it doesn't kill the server each time it runs?

**Tags:** entries, database, performance

---

## 1 answers

### Answer by username_1 on 2015-08-20. Score: 4

This is really difficult to provide a proper answer for in its current form, so I'll give some general advice when troubleshooting things like this.

1. Try to limit the complexity of the Twig code building the query. It might help to break it up into multiple, smaller queries instead of executing everything as one monolithic query.
2. Don't do this in Twig. Break out into a plugin/PHP using a [template hook](http://buildwithcraft.com/classreference/services/TemplatesService#hook-detail) and grab only the data you need using [query builder](http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder). Again, it might help to break things up into multiple, smaller queries even from PHP.
3. Enable [devMode](http://buildwithcraft.com/docs/config-settings#devMode) and find the resulting SQL that is generated. Use standard MySQL [slow query debugging techniques](https://www.google.com/search?q=debugging%20mysql%20slow%20queries) to see where the bottleneck is and optimize from that information.
4. Finally, you can try to throw more resources at your server and brute force your way though it.


---

