Answers.Body: <p>Are the <code>nutrition</code> entries associated with <code>restaurant</code> entries through a single field or multiple fields? If it's only one field and you only want to query for restaurants that have <em>any</em> nutrition associated with them, you don't need to load all the IDs. Instead, you can just use <code>:notempty:</code> to find restaurants where the nutrition entries field is not empty. Assuming your entries field is named <code>nutrition</code>:</p>
  <pre><code>{% set restaurants = craft.entries()
      .section('restaurants')
      .orderBy('title')
      .limit(40)
      .nutrition(':notempty:')
      .all()
  %}
  </code></pre>
  <p>See <a href="https://craftcms.com/docs/3.x/entries-fields.html#querying-elements-with-entries-fields" rel="nofollow noreferrer">Querying Elements with Entries Fields</a> in the documentation.</p>
  <p>Now if the relation to nutrition entries is spread across <em>multiple</em> fields, you'll probably have to write a <a href="https://craftcms.com/docs/3.x/element-queries.html#advanced-element-queries" rel="nofollow noreferrer">custom where clause</a> to achieve the same result.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-21T13:14:25.377Z
Answers.Text: Moritz, entries in the restaurants channel have no relations (entries field) to nutrition. Instead, all entries in the nutrition channel are associated to a restaurant. https://i.imgur.com/F6knHlU.png
Answers.UserDisplayName: Mark H.
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-21T15:28:08.460Z
Answers.Text: @MarkH. Ah I didn't catch that, that's unfortunate. In this case, you could use a custom `innerJoin()` statement to the `relations` table to filter out any restaurants that don't have a relation in that field. I've recently done something similar, see the final code example [in my question here](https://craftcms.stackexchange.com/questions/38207/find-related-entries-by-specific-position-in-the-entries-field). If that doesn't work is is still not fast enough, you could give the [Reverse Relations](https://plugins.craftcms.com/reverserelations) plugin a try to be able to filter like that.
Answers.UserDisplayName: MoritzLost
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-20T07:47:51.980Z
Answers.LastActivityDate: 2021-09-20T07:47:51.980Z
Answers.UserDisplayName: MoritzLost
Body: <p>Given:</p>
  <p>Channel: Nutrition. 550k entries. It has entries field pointing to the &quot;restaurants&quot; channel.</p>
  <p>Channel: Restaurants. 300k entries.</p>
  <p>I'm trying to display restaurants that have at least one nutrition associated with them. Currently using the following snippet:</p>
  <pre><code>                {% set nutritionIds = craft.entries ({
                      section: 'nutrition',
                  }).ids() %}
  
                  {% set entries = craft.entries ({
                      section: 'restaurants',
                      orderBy: 'title',
                      limit: 40,
                      relatedTo: {
                          sourceElement: nutritionIds,
                      }
                  }) %}
  </code></pre>
  <p>Please help me to optimize this query. Page load time is 50 seconds, memory usage is 250mb. Dedicated server.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-09-21T13:57:51.783Z
Comments.Text: As a last resort, you could write a job that compiles a single optimized database table (or two). A lot of effort but you will bypass all of Craft complexity guaranteeing a very fast query.
Comments.UserDisplayName: gioppe
Tags: <performance><relatedto><speed>
Title: Optimize relatedTo query
ThreadText: # Optimize relatedTo query
  
  (Asked by: username_0 on 2021-09-19)
  
  Given:
  
  Channel: Nutrition. 550k entries. It has entries field pointing to the "restaurants" channel.
  
  Channel: Restaurants. 300k entries.
  
  I'm trying to display restaurants that have at least one nutrition associated with them. Currently using the following snippet:
  
  ```
                  {% set nutritionIds = craft.entries ({
                      section: 'nutrition',
                  }).ids() %}
  
                  {% set entries = craft.entries ({
                      section: 'restaurants',
                      orderBy: 'title',
                      limit: 40,
                      relatedTo: {
                          sourceElement: nutritionIds,
                      }
                  }) %}
  
  ```
  
  Please help me to optimize this query. Page load time is 50 seconds, memory usage is 250mb. Dedicated server.
  
  **Tags:** performance, relatedto, speed
  
  > **Comments:**
  > **username_1**: As a last resort, you could write a job that compiles a single optimized database table (or two). A lot of effort but you will bypass all of Craft complexity guaranteeing a very fast query.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2021-09-20. Score: 1
  
  Are the `nutrition` entries associated with `restaurant` entries through a single field or multiple fields? If it's only one field and you only want to query for restaurants that have *any* nutrition associated with them, you don't need to load all the IDs. Instead, you can just use `:notempty:` to find restaurants where the nutrition entries field is not empty. Assuming your entries field is named `nutrition`:
  
  ```
  {% set restaurants = craft.entries()
      .section('restaurants')
      .orderBy('title')
      .limit(40)
      .nutrition(':notempty:')
      .all()
  %}
  
  ```
  
  See [Querying Elements with Entries Fields](https://craftcms.com/docs/3.x/entries-fields.html#querying-elements-with-entries-fields) in the documentation.
  
  Now if the relation to nutrition entries is spread across *multiple* fields, you'll probably have to write a [custom where clause](https://craftcms.com/docs/3.x/element-queries.html#advanced-element-queries) to achieve the same result.
  
  > **Comments:**
  > **username_0**: Moritz, entries in the restaurants channel have no relations (entries field) to nutrition. Instead, all entries in the nutrition channel are associated to a restaurant. https://i.imgur.com/F6knHlU.png  
  > **username_2**: @MarkH. Ah I didn't catch that, that's unfortunate. In this case, you could use a custom `innerJoin()` statement to the `relations` table to filter out any restaurants that don't have a relation in that field. I've recently done something similar, see the final code example [in my question here](https://craftcms.stackexchange.com/questions/38207/find-related-entries-by-specific-position-in-the-entries-field). If that doesn't work is is still not fast enough, you could give the [Reverse Relations](https://plugins.craftcms.com/reverserelations) plugin a try to be able to filter like that.  
  
  ---
  
  