让 Doctrine 使用 MySQL “FORCE INDEX"

2022-01-16 00:00:00 php mysql doctrine

我在 Doctrine 的 DQL 中有一个查询，需要能够使用 MySQL 的FORCE INDEX".功能，以大大减少查询时间.下面是查询在普通 SQL 中的基本样子:

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;

我假设我必须扩展一些 Doctrine 组件才能使用 DQL 执行此操作(或者有没有办法将任意 SQL 注入到 Doctrin 的查询之一中?).有人有什么想法吗?

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?

推荐答案

我在网上找到了很少有帮助的 Doctrine_RawSql 示例，所以这就是我最终创建查询的方法.

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');

相关文章