How do I optimise this colossal query?

Question

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?

Answer 1

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 and grab only the data you need using query builder. Again, it might help to break things up into multiple, smaller queries even from PHP.

3. Enable devMode and find the resulting SQL that is generated. Use standard MySQL slow query debugging techniques 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.