title: Pagerfanta and Doctrine2 COUNT optimization
body: <p>I'm using Pagerfanta and Doctrine Adapters with Symfony2 and Silex. As my database became bigger I noticed huge load on admin stats pages that display big data with pagination. I checked profiler and saw unbelievably inefficient queries:</p>
  
  <pre><code>SELECT DISTINCT id16
  FROM (
      SELECT f0_.username AS username0, ..., f0_.added_on AS added_on20
      FROM fos_user f0_ ORDER BY f0_.id DESC
  ) dctrn_result
  LIMIT 50 OFFSET 0;
  
  SELECT COUNT(*) AS dctrn_count
  FROM (
      SELECT f0_.username AS username0, ..., f0_.added_on AS added_on20
      FROM fos_user f0_ ORDER BY f0_.id DESC
  ) dctrn_result
  LIMIT 50 OFFSET 0;`
  </code></pre>
  
  <p>First query was easy to fix by creating fixed version of <code>DoctrineORMAdapter</code> class. The code that generates <code>COUNT()</code> query is more complicated so I decided to ask if there's any solution for this. </p>
  
  <p>So is there any way to make Pagerfanta not running nested queries?</p>