I have this class:

namespace App\Infrastructure\Services;

use Doctrine\ORM\EntityManagerInterface;

use App\Entity\MyModel

class FetchResultFromDb 
{

    public function __construct(public EntityManagerInterface $entityManager)
    {
    }


    public function getEarliestResult(int $rowNum)
    {

        $queryBuilder = $this->entityManager->getRepository(MyModel::class)->createQueryBuilder('m');

        return $queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

    }  

}

What I want to achieve is to do a simple select in the table my_model:

SELECT id from my_model order by sent_timestamp LIMIT :row_num

The Doctrine Entity that is used for the query is:

namespace App\Entity;

use App\Infrastructure\Repository\MailLogRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class MyModel
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;
 
    #[ORM\Column(name:'sent_timestamp',type: Types::DATETIME_IMMUTABLE)]
    private ?\DateTimeInterface $sentTimestamp = null;
    
}

But the Execution of the getEarliestResult takes ~74 second. Do you know why? This query is simple enough if run as is directly to the database, instead of using query builder, it takes ~ 0.4 seconds for 5020 rows.

Assuming the Model's table is named my_model, then the query:

     return $queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

Resulting executing a cartesian product of the same table:

SELECT m0_.id AS id_0 FROM my_model m1_, my_model m0_ ORDER BY m0_.sent_timestamp ASC LIMIT 5000;

This query is ineficient and it is the cause of your long query execution time.

You can simplify it by removing the call of the getRepository we remove a table reference from the final query.

  public function getEarliestResult(int $rowNum)
  {

        $queryBuilder = $this->entityManager->createQueryBuilder();

        return $queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

 }  

An alternate approach to acheive this is by removing the from in our built query:

  public function getEarliestResult(int $rowNum)
  {

        $queryBuilder = $this->entityManager->getRepository(MailLog::class)->createQueryBuilder('m');

        return $queryBuilder
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

 }  

Diagnosing Similar Results

A way to diagnose the issue is by dumping the generated sql whilst developing:

$sql = $queryBuilder
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getSQL();

var_dump($sql);

Then execute the generated query upon db and measure the response time