0

I've stumbled upon the optimizer hints MySQL feature here that I did not know existed and tried to figure out how to use this with Yii2.

MySQL supports setting specific options by using optimizer hints, e.g.:

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

Is there Yii2 native way of setting those query hints?

Theo
  • 2,262
  • 3
  • 23
  • 49
  • 1
    I consider them them there as a option of last resort. Getting the indexes right ahead of time and letting the optimizer hopefully make good decisions is my preference. – danblack Aug 02 '21 at 08:14

1 Answers1

1

You can use second argument of select() to pass select options:

$query = (new Query())
    ->select('f1', '/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */')
    ->from('t3');
rob006
  • 21,383
  • 5
  • 53
  • 74