title: SQL query very slow within Symfony, but very fast in PhpMyAdmin
body: <p>I'm working on a Business Intelligence App. I'm running the following query within a Symfony Repository :</p>
  
  <pre><code>public function countSubscribers()
  {
      $sql = "SELECT COUNT(DISTINCT(a.id))
      FROM account a
      INNER JOIN customer c ON c.account = a.id
      INNER JOIN payment p ON p.customer = c.id
      WHERE p.init != 1
      AND p.cancel_date IS NULL
      AND p.unpaid != 1
      AND p.abo = 1
      AND p.value != 0
      AND c.date_next_payement &gt; NOW()";
  
      $connection = $this-&gt;getEntityManager()-&gt;getConnection();
      $statement = $connection-&gt;prepare($sql);
      $statement-&gt;execute();
      $result = $statement-&gt;fetchColumn();
  
      return $result;
  }
  </code></pre>
  
  <p>The page is very slow to load, and according to the symfony profiler, the query took more than 15 seconds to execute :</p>
  
  <pre><code>SELECT COUNT(DISTINCT(a.id)) FROM account a INNER JOIN customer c ON c.account = a.id INNER JOIN payment p ON p.customer = c.id WHERE p.init != 1 AND p.cancel_date IS NULL AND p.unpaid != 1 AND p.abo = 1 AND p.value != 0 AND c.date_next_payement &gt; NOW() Parameters: { }
  [Hide runnable query]
  Time: 14553.83 ms [ - Explain query ]
  Explanation:
  id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  c   ALL     PRIMARY,UNIQ_81398E097D3656A4               179226  Using where
  1   SIMPLE  a   eq_ref  PRIMARY     PRIMARY     8   evotest.c.account   1   Using index
  1   SIMPLE  p   ref     IDX_6D28840D81398E09    IDX_6D28840D81398E09    9   evotest.c.id    1   Using where
  </code></pre>
  
  <p>So I tried to run this query in PhpMyAdmin, and the query executes in less than 1 second. Any idea why such a difference ? My web page executes a bunch of others queries, but this is the query which uses almost all of the total loading time.</p>
  
  <p><strong>UPDATED QUERY :</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(c.id)) 
  FROM customer c 
  INNER JOIN payment p ON p.customer = c.id 
  WHERE p.init != 1 
  AND p.cancel_date IS NULL 
  AND p.unpaid != 1 
  AND p.abo = 1 
  AND p.value != 0 
  AND c.date_next_payement &gt; NOW()
  </code></pre>
  
  <p>This query is still very slow. Is there any way to optimize it ? I want to count the number of customers having at least 1 payment with given filters. the relation is on c.id = p.customer</p>