Getting Doctrine to use MySQL “FORCE INDEX”

Question

I have a query in Doctrine's DQL that needs to be able to use MySQL's "FORCE INDEX" functionality in order to massively reduce the query time. Below is what the query basically looks like in plain SQL:

SELECT id FROM items FORCE INDEX (best_selling_idx)
WHERE price = ... (etc)
LIMIT 200;

I assume I have to extend some Doctrine component to be able to do this with DQL (or is there a way to inject arbitrary SQL into one of Doctrin's queries?). Anyone have any ideas?

Thanks!

Answer 1

See the Native SQL part of the Doctrine documentation. You should be able to use RawSql to accomplish this.

Answer 2

If you don't like native SQL, you can use the following patch. https://gist.github.com/arnaud-lb/2704404

This patch suggests to create only one custom Walker and set it up as follows

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

Answer 3

I've found very few helpful Doctrine_RawSql examples online, so here's what I ended up doing to create my query.

$q = new Doctrine_RawSql();
$q->select('{b.id}, {b.description}, {c.description}')
  ->from('table1 b FORCE INDEX(best_selling_idx) INNER JOIN table2 c ON b.c_id = c.id')
  ->addComponent('b', 'Table1 b')
  ->addComponent('c', 'b.Table2 c');