title: Symfony4 - Doctrine pagination low performance of the counting query (634484 records)
body: <p>I have a table (no relations) that needs pagination. For doing this I use Doctrine Pagination tool.</p>
  
  <p>The table contains 634484 records. Doctrine performs following queries:</p>
  
  <pre><code>SELECT p0_.id AS id_0, p0_.name AS name_1, p0_.level AS level_2, p0_.alignment AS alignment_3, p0_.account_id AS account_id_4 FROM player.player p0_ ORDER BY p0_.level DESC LIMIT 25;
  </code></pre>
  
  <p>explain:</p>
  
  <pre><code>id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
  1   SIMPLE  p0_     index       search  1       25  100.00
  </code></pre>
  
  <p>time of execution:
  1.04 ms</p>
  
  <p><strong>problematic query</strong>:</p>
  
  <pre><code>SELECT 
    COUNT(*) AS dctrn_count 
  FROM 
    (
      SELECT 
        DISTINCT id_0 
      FROM 
        (
          SELECT 
            p0_.id AS id_0, 
            p0_.name AS name_1, 
            p0_.level AS level_2, 
            p0_.alignment AS alignment_3 
          FROM 
            player.player p0_ 
          ORDER BY 
            p0_.level DESC
        ) dctrn_result
    ) dctrn_table
  </code></pre>
  
  <p>explain:</p>
  
  <pre><code>id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
  1   PRIMARY &lt;derived2&gt;      ALL                 634484  100.00  
  2   DERIVED p0_     ALL PRIMARY             634484  100.00  Using temporary
  </code></pre>
  
  <p>Time of execution: <strong>2414.72 ms</strong></p>
  
  <p>Doctrine uses the DISTINCT keyword to find out how many rows the table contains, which is probably the reason why the query was run so long.</p>
  
  <p>Execution time without DISTINCT keyword: 1.26 ms</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>Repository:</p>
  
  <pre><code>public function findLatestPaginated(int $page = 1): array
  {
      $queryBuilder = $this-&gt;createQueryBuilder('c')
          -&gt;addOrderBy('c.level', 'DESC');
  
  
      return $this-&gt;paginate($queryBuilder, $page, Character::PER_PAGE);
  }
  </code></pre>
  
  <p>Paginate method:</p>
  
  <pre><code>&lt;?php
  
  namespace App\Core\Repository;
  
  use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository as BaseServiceEntityRepository;
  use Doctrine\ORM\Query;
  use Doctrine\ORM\QueryBuilder;
  use Doctrine\ORM\Tools\Pagination\Paginator;
  
  abstract class ServiceEntityRepository extends BaseServiceEntityRepository
  {
      protected function paginate(QueryBuilder $queryBuilder, int $currentPage, int $perPage)
      {
          $currentPage = $currentPage &lt; 1 ? 1 : $currentPage;
          $firstResult = ($currentPage - 1) * $perPage;
  
          /** @var Query $query */
          $query = $queryBuilder
              -&gt;setFirstResult($firstResult)
              -&gt;setMaxResults($perPage)
              -&gt;getQuery();
  
          $paginator = new Paginator($query, false);
          $numResults = $paginator-&gt;count();
          $hasPreviousPage = $currentPage &gt; 1;
          $hasNextPage = ($currentPage * $perPage) &lt; $numResults;
  
          return [
              'result' =&gt; $paginator-&gt;getIterator(),
              'currentPage' =&gt; $currentPage,
              'hasPreviousPage' =&gt; $hasPreviousPage,
              'hasNextPage' =&gt; $hasNextPage,
              'previousPage' =&gt; $hasPreviousPage ? $currentPage - 1 : null,
              'nextPage' =&gt; $hasNextPage ? $currentPage + 1 : null,
              'numPages' =&gt; (int)ceil($numResults / $perPage),
              'haveToPaginate' =&gt; $numResults &gt; $perPage,
          ];
      }
  }
  </code></pre>
  
  <p>Questions:</p>
  
  <ul>
  <li>Why does Doctrine use the distinct keyword in a couting query? (by <code>id</code> which is primary key)</li>
  <li>How can I prevent that?</li>
  </ul>