Answers.Body: <p>The solution is below. 
  <strong>EDIT: only for MySQL 5.7.4 and lower! Higher versions break.</strong></p>
  
  <p>For whatever reason, the mechanics of the <code>group</code> filter in this case was producing a new query for every Entry. Below, resolves this by doing the same job in a more performant way. I'm not entirely sure of the mechanics of how this works, but the answer was supplied over on the Discord support channel and I thought it worth cross-posting to here. Thanks Sebastian Lenz!</p>
  
  <pre><code>{% cache globally %}
      {% set postDates = craft.entries({
          section: 'newsItems',
          select: 'postDate',
          limit: null,
          groupBy: ['DATE_FORMAT(postDate, "%Y-%m")'],
      }).column() %}
  
      &lt;div class="archives widget"&gt;
          &lt;h2&gt;News Archive&lt;/h2&gt;
          &lt;ul&gt;
              {% for year, postDatesInYear in postDates|group("object|date('Y')") %}
                  &lt;li&gt;
                      &lt;a href="/news/archive/{{ year }}"&gt;{{ year }}&lt;/a&gt;
                      &lt;ul&gt;
                          {% for month, postDatesInMonth in postDatesInYear|group("object|date('m')")|reverse(true) %}
                              &lt;li&gt;
                                  &lt;a href="/news/archive/{{ year }}/{{ month }}"&gt;
                                      {{ postDatesInMonth[0]|date('F') }}
                                  &lt;/a&gt;
                              &lt;/li&gt;
                          {% endfor %}
                      &lt;/ul&gt;
                  &lt;/li&gt;
              {% endfor %}
          &lt;/ul&gt;
      &lt;/div&gt;&lt;!-- .archives --&gt;
  {% endcache %}
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-09-18T15:11:32.597Z
Answers.Text: NOTE: This appears to only work with MySQL versions below 5.7.5.
Answers.UserDisplayName: Matt Wilcox
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-09-11T14:13:23.753Z
Answers.LastActivityDate: 2019-09-18T15:48:24.823Z
Answers.LastEditDate: 2019-09-18T15:48:24.823Z
Answers.UserDisplayName: Matt Wilcox
Body: <p>I have a Twig include that generates a News Archive side-bar. It works great, but I've just noticed that on a particular project it's contributing in the region of 800 DB Queries when caching is off.</p>
  
  <p>What have I missed about the mechanics of the group filter? Because I can see no other reason why this should generate more than one query?</p>
  
  <pre><code>{% cache globally %}
      {% set allEntries = craft.entries.section('newsItems').limit(null).all() %}
      &lt;div class="archives widget"&gt;
          &lt;h2&gt;News Archive&lt;/h2&gt;
          &lt;ul&gt;
              {% for year, entriesInYear in allEntries | group("postDate | date('Y')") %}
                  &lt;li&gt;
                      &lt;a href="/news/archive/{{ year }}"&gt;{{ year }}&lt;/a&gt;
                      &lt;ul&gt;
                          {% for month, entriesInMonth in entriesInYear | group("postDate|date('m')") | reverse(true) %}
                              &lt;li&gt;
                                  &lt;a href="/news/archive/{{ year }}/{{ month }}"&gt;
                                      {{ entriesInMonth[0].postDate|date('F') }}
                                  &lt;/a&gt;
                              &lt;/li&gt;
                          {% endfor %}
                      &lt;/ul&gt;
                  &lt;/li&gt;
              {% endfor %}
          &lt;/ul&gt;
      &lt;/div&gt;&lt;!-- .archives --&gt;
  {% endcache %}
  </code></pre>
  
Tags: <templating><database>
Title: Why is this Twig code generating large numbers of DB queries?
ThreadText: # Why is this Twig code generating large numbers of DB queries?
  
  (Asked by: username_0 on 2019-09-10)
  
  I have a Twig include that generates a News Archive side-bar. It works great, but I've just noticed that on a particular project it's contributing in the region of 800 DB Queries when caching is off.
  
  What have I missed about the mechanics of the group filter? Because I can see no other reason why this should generate more than one query?
  
  ```
  {% cache globally %}
      {% set allEntries = craft.entries.section('newsItems').limit(null).all() %}
      <div class="archives widget">
          <h2>News Archive</h2>
          <ul>
              {% for year, entriesInYear in allEntries | group("postDate | date('Y')") %}
                  <li>
                      <a href="/news/archive/{{ year }}">{{ year }}</a>
                      <ul>
                          {% for month, entriesInMonth in entriesInYear | group("postDate|date('m')") | reverse(true) %}
                              <li>
                                  <a href="/news/archive/{{ year }}/{{ month }}">
                                      {{ entriesInMonth[0].postDate|date('F') }}
                                  </a>
                              </li>
                          {% endfor %}
                      </ul>
                  </li>
              {% endfor %}
          </ul>
      </div><!-- .archives -->
  {% endcache %}
  
  ```
  
  **Tags:** templating, database
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2019-09-11. Score: 1
  
  The solution is below.
  **EDIT: only for MySQL 5.7.4 and lower! Higher versions break.**
  
  For whatever reason, the mechanics of the `group` filter in this case was producing a new query for every Entry. Below, resolves this by doing the same job in a more performant way. I'm not entirely sure of the mechanics of how this works, but the answer was supplied over on the Discord support channel and I thought it worth cross-posting to here. Thanks Sebastian Lenz!
  
  ```
  {% cache globally %}
      {% set postDates = craft.entries({
          section: 'newsItems',
          select: 'postDate',
          limit: null,
          groupBy: ['DATE_FORMAT(postDate, "%Y-%m")'],
      }).column() %}
  
      <div class="archives widget">
          <h2>News Archive</h2>
          <ul>
              {% for year, postDatesInYear in postDates|group("object|date('Y')") %}
                  <li>
                      <a href="/news/archive/{{ year }}">{{ year }}</a>
                      <ul>
                          {% for month, postDatesInMonth in postDatesInYear|group("object|date('m')")|reverse(true) %}
                              <li>
                                  <a href="/news/archive/{{ year }}/{{ month }}">
                                      {{ postDatesInMonth[0]|date('F') }}
                                  </a>
                              </li>
                          {% endfor %}
                      </ul>
                  </li>
              {% endfor %}
          </ul>
      </div><!-- .archives -->
  {% endcache %}
  
  ```
  
  > **Comments:**
  > **username_0**: NOTE: This appears to only work with MySQL versions below 5.7.5.  
  
  ---
  
  