Force doctrine to use an index in with Symfony 2

Databases usually are pretty good at using the correct indexes. But sometimes you want to use a specific index for a query. That is why we use

use index

But if you are using an abstraction layer like Doctrine it might not be supported. And that means that you would have to use a native query

Thankfully doctrine allows you to expand its capabilities pretty easy. In order to force the use of an index using DQL, first you have to add the following class

use Doctrine\ORM\Query\SqlWalker;

class UseIndexWalker extends SqlWalker
{
    const HINT_USE_INDEX = 'UseIndexWalker.UseIndex';

    public function walkFromClause($fromClause)
    {
        $sql = parent::walkFromClause($fromClause);
        $index = $this->getQuery()->getHint(self::HINT_USE_INDEX);

        return preg_replace('/( INNER JOIN| LEFT JOIN|$)/', sprintf(' USE INDEX(%s)\1', $index), $sql, 1);
    }
}

Then build your query as normal, get your query object and finally add a couple of hints.

$query = $em->createQueryBuilder()->select(‘food’)->from(‘Food’, ‘food’)->getQuery();
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, ‘UseIndexWalker’);
$query->setHint(UseIndexWalker::HINT_USE_INDEX, ‘some_index_name’);

And voila! You are now using your desired index. You can always confirm it verifying the runnable query from Symfony Profiler.

• The information and code here presented was taken from https://stackoverflow.com/questions/16729011/index-hinting-in-doctrine2