Category query very slow on multilingual site

Question

I have a directory of sea ports with a google map that loads about 450 records. The map and listings and pagination work fine. On their own, I get a 2.5 sec page load average.

However as soon as I add a search form that populates a chained select for countries and ports the load time goes up to 8 secs.

The search form code looks like this

{% set categories = craft.categories.group('locations').limit(null) %}

<label>Country</label>
<select id="countries" class="countries" name="country">
    <option value="">Select...</option>

    {% cache %}
        {% for country in categories %}
            {% if country.level == 1 %}
                <option value="{{ country.slug }}">{{ country.title }}</option>
            {% endif %}
        {% endfor %}
    {% endcache %}
</select>

<label>Ports</label>
<select id="ports" name="port" class="ports" disabled="disabled">
    <option value="">Select a country first</option>

    {% cache %}
        {% for port in categories %}
            <option value="{{ port.slug }}" class="{{ port.parent | lower }}">{{ port.title }}</option>
       {% endfor %}
    {% endcache %}

</select>

It seems that this code cost about 5 seconds of load time.

I have checked the profile output and seen the query for the categories. It's huge. I can't see how to get the page to load faster as I can't see eager loading applying to a category group, and caching doesn't really help here.

Any ideas on how to optimise this portion?

Answer 1

Your n+1 problem comes from accessing port.parent in the Ports menu loop. That is being called for each category, and each time it is triggering a new element query.

The right way to go about this is to load only the top-level categories (the Countries) using the level=1 param, eager-loaded with their children (the Ports):

{% set countries = craft.categories({
    group: 'locations',
    level: 1,
    limit: null,
    with: ['children']
}) %}

You can then build the Countries menu without worrying about which level the categories have:

<label>Country</label>
<select id="countries" class="countries" name="country">
    <option value="">Select...</option>

    {% for country in countries %}
        <option value="{{ country.slug }}">{{ country.title }}</option>
    {% endfor %}
</select>

And to build the Ports menu, just loop over the countries again, and for each one, loop through its eager-loaded children. Set each port option’s class attribute to the current country’s slug (since that’s what you’re setting the country menu options’ values to).

<label>Ports</label>
<select id="ports" name="port" class="ports" disabled="disabled">
    <option value="">Select a country first</option>

    {% for country in countries %}
        {% for port in country.children %}
            <option value="{{ port.slug }}" class="{{ country.slug }}">{{ port.title }}</option>
        {% endfor %}
    {% endfor %}
</select>

Answer 2

It's hard to know for sure without seeing your query profile output, but try eager loading your ports field like so:

{% set categories = craft.categories({
    group: 'locations',
    limit: null,
    with: ['ports']
}) %}