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?
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');
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With