USE INDEX / FORCE INDEX in a Doctrine2 DQL query

example.php

<?php

use UseIndexWalker;
use Doctrine\ORM\Query

$query = $em->createQuery("SELECT f FROM Foo f WHERE f.a = 1 and f.b = 2");

$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'UseIndexWalker');
$query->setHint(UseIndexWalker::HINT_USE_INDEX, 'some_index_name');

$query->getResult();

UseIndexWalker.php

<?php

use Doctrine\ORM\Query\SqlWalker;

/**
 * Quick hack to allow adding a USE INDEX on the query
 */
class UseIndexWalker extends SqlWalker
{
    const HINT_USE_INDEX = 'UseIndexWalker.UseIndex';

    public function walkFromClause($fromClause)
    {
        $result = parent::walkFromClause($fromClause);

        if ($index = $this->getQuery()->getHint(self::HINT_USE_INDEX)) {
            $result = preg_replace('#(\bFROM\s*\w+\s*\w+)#', '\1 USE INDEX (' . $index . ')', $result);
        }

        return $result;
    }
}

Cheers!

Here is modified version of walker,
contains FIX for join-queries

http://stackoverflow.com/questions/16729011/index-hinting-in-doctrine2

Here is mine that can use different indexes per DQL table aliases. Works with both createQuery and createQueryBuilder. Still a hack tho. Thx for the idea arnaud-lb!

https://github.com/ggergo/SqlIndexHintBundle
https://packagist.org/packages/ggergo/sqlindexhintbundle

@ggergo Thank you for sharing your version.

The only issue I encountered is that once the query hint is added we're forced to have all the DQL aliases listed. For example, sometimes you might only want to add a hint in a JOIN clause.

Once I updated the "walkFromClause" method to the following, it worked as expected:

public function walkFromClause($fromClause): string
{
    $sql = parent::walkFromClause($fromClause);
    $hints = $this->getQuery()->getHint(self::HINT_INDEX);

    foreach ($this->getIndexHintParameters($fromClause) as $params) {
        if (!array_key_exists($params['dqlAlias'], $hints)) {
            continue;
        }
        
        $sql = $this->insertIndex($params['sqlKey'], $params['sqlTableAlias'], $hints[$params['dqlAlias']], $sql);
    }

    return $sql;
}

The difference here is the "array_key_exists()" check, which will only insert an index hint if listed.

Best,

Renato.

@renatogcarvalho Thank you for noticing! I added your fix. 👍 Best regards, Gergő

Another composer package that works even for tables nor present in DQL, but present in SQL: https://github.com/shipmonk-rnd/doctrine-mysql-index-hints

Thanks for the solution ! 🙏
Small fix : in example.php line 8, you can use UseIndexWalker::class instead of 'UseIndexWalker'. It avoids having hardcoded strings in code + it manages if the file is in a different namespace. :)