Symfony4 - Doctrine pagination low performance of the counting query (634484 records)

I have a table (no relations) that needs pagination. For doing this I use Doctrine Pagination tool.

The table contains 634484 records. Doctrine performs following queries:

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;

explain:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  p0_     index       search  1       25  100.00

time of execution: 1.04 ms

problematic query:

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

explain:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>      ALL                 634484  100.00  
2   DERIVED p0_     ALL PRIMARY             634484  100.00  Using temporary

Time of execution: 2414.72 ms

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.

Execution time without DISTINCT keyword: 1.26 ms

EDIT:

Repository:

public function findLatestPaginated(int $page = 1): array
{
    $queryBuilder = $this->createQueryBuilder('c')
        ->addOrderBy('c.level', 'DESC');


    return $this->paginate($queryBuilder, $page, Character::PER_PAGE);
}

Paginate method:

<?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 < 1 ? 1 : $currentPage;
        $firstResult = ($currentPage - 1) * $perPage;

        /** @var Query $query */
        $query = $queryBuilder
            ->setFirstResult($firstResult)
            ->setMaxResults($perPage)
            ->getQuery();

        $paginator = new Paginator($query, false);
        $numResults = $paginator->count();
        $hasPreviousPage = $currentPage > 1;
        $hasNextPage = ($currentPage * $perPage) < $numResults;

        return [
            'result' => $paginator->getIterator(),
            'currentPage' => $currentPage,
            'hasPreviousPage' => $hasPreviousPage,
            'hasNextPage' => $hasNextPage,
            'previousPage' => $hasPreviousPage ? $currentPage - 1 : null,
            'nextPage' => $hasNextPage ? $currentPage + 1 : null,
            'numPages' => (int)ceil($numResults / $perPage),
            'haveToPaginate' => $numResults > $perPage,
        ];
    }
}

Questions:

• Why does Doctrine use the distinct keyword in a couting query? (by id which is primary key)
• How can I prevent that?