<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>

CC BY-SA 4.0
2019-09-18T15:11:32.597Z
NOTE: This appears to only work with MySQL versions below 5.7.5.
Matt Wilcox
CC BY-SA 4.0
2019-09-11T14:13:23.753Z
2019-09-18T15:48:24.823Z
2019-09-18T15:48:24.823Z
Matt Wilcox
<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>

<templating><database>
Why is this Twig code generating large numbers of DB queries?
# 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() %}
    
        News Archive
        
            {% for year, entriesInYear in allEntries | group("postDate | date('Y')") %}
                
                    {{ year }}
                    
                        {% for month, entriesInMonth in entriesInYear | group("postDate|date('m')") | reverse(true) %}
                            
                                
                                    {{ entriesInMonth[0].postDate|date('F') }}
                                
                            
                        {% endfor %}
                    
                
            {% endfor %}
        
    
{% 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() %}

    
        News Archive
        
            {% for year, postDatesInYear in postDates|group("object|date('Y')") %}
                
                    {{ year }}
                    
                        {% for month, postDatesInMonth in postDatesInYear|group("object|date('m')")|reverse(true) %}
                            
                                
                                    {{ postDatesInMonth[0]|date('F') }}
                                
                            
                        {% endfor %}
                    
                
            {% endfor %}
        
    
{% endcache %}

> **Comments:**
> **username_0**: NOTE: This appears to only work with MySQL versions below 5.7.5.  

---

