title: Doctrine2 ORM slow query optimization
body: <p>I'm struggling to make a query efficient enough. I'm using Doctrine2 ORM (the query is build with QueryBuilder) and part of my query is running very slow - takes about 4s with table of 5000 rows.</p>
  
  <p>This is the relevant part of db schema:</p>
  
  <pre><code>TABLE user
  id (primary)
  ... (plenty of rows, not relevant to the query)
  
  TABLE slot
  id (primary)
  user_id (foreign for user)
  date (datetime)
  </code></pre>
  
  <p>And this is how my query looks like (it's the basic version, there's a lot of filters to be applied, but these work like fine for now)</p>
  
  <pre><code>SELECT 
    u.id AS uid, 
    COUNT(DISTINCT s_order.id) AS sclr_1, 
    COUNT(DISTINCT s_filter.id) AS sclr_2 
  FROM 
    user u 
    LEFT JOIN slot s_order ON (s_order.user_id = u.id) 
    LEFT JOIN slot s_filter ON (s_filter.user_id = u.id) 
  WHERE 
    (
      (
        (
          s_order.date BETWEEN ? 
          AND ?
        ) 
        AND (
          s_filter.date BETWEEN ? 
          AND ?
        )
      ) 
      AND (u.deleted_at IS NULL)
    ) 
    AND u.userType IN ('2') 
  GROUP BY 
    u.id 
  HAVING 
    sclr_2 &gt; 0 
  ORDER BY 
    sclr_1 DESC 
  LIMIT 
    12
  </code></pre>
  
  <p>Let me explain what I'm trying to achieve here:
  I need to filter users who has any slots between 1 week ago and 1 week ahead, then order them by count of slots available between now and 1 week ahead. The part of query causing issues is LEFT JOIN of s_filter and I'm wondering whether perhaps there's a way to improve the performance of that query?</p>
  
  <p>Any help appreciated really, even if it's only plain SQL I'll try to convert it to DQL myself!</p>
  
  <p>@UPDATE
  Just an additional info that I forgot, the LIMIT in query is for pagination purposes!</p>
  
  <p>@UPDATE 2
  After a while of tweaking the query I figured out that I can use JOIN for filtering instead of LEFT JOIN + COUNT, so my query does look like that now:</p>
  
  <pre><code>SELECT 
      u.id AS uid, COUNT(DISTINCT s_order.id) AS ordinal
  FROM
      langu_user u
          LEFT JOIN
      slot s_order ON (s_order.user_id = u.id) AND s_order.date BETWEEN '2017-02-03 14:03:22' AND '2017-02-10 14:03:22'
          JOIN
      slot s_filter ON (s_filter.user_id = u.id) AND s_filter.date BETWEEN '2017-01-27 14:03:22' AND '2017-02-10 14:03:22'
  WHERE
  u.deleted_at IS NULL
  AND u.userType IN ('2')
  GROUP BY u.id
  ORDER BY ordinal DESC
  LIMIT 12
  </code></pre>
  
  <p>And it went down from 4.1-4.3s to 3.6~</p>