Description:
I added NO_INDEX_MERGE to a query to get rid of an index merge intersection.
The intersection went away. So did the range scan the query had been using
before, and it fell back to a full table scan.
The manual describes the hint as Index Merge only:
"The NO_INDEX_MERGE hint disables Index Merge combinations that involve any of
the specified indexes. If the hint specifies no indexes, Index Merge is not
permitted for the table."
That isn't what happens. Once an index is named in the hint, the optimizer
won't use it for a plain single-index range scan either. No Index Merge plan
is involved. Without an index list it's every index on the table. Ref access
still works, so a = 5 is fine. a IN (5, 6) and a BETWEEN 5 AND 6 are not.
FORCE INDEX doesn't bring the range scan back. WL#9167 uses this exact case
as an example of what should work:
"/*+ NO_INDEX_MERGE(t1 idx1, idx2) */ ... FORCE INDEX idx1, idx2
Index merge is disallowed for idx1, idx2 but optimizer is forced to use either
idx1 or idx2 for range or ref access."
The optimizer trace rejects the index during the regular range analysis, not
during Index Merge planning:
"range_scan_alternatives": [
{"index": "idx_a", "chosen": false, "cause": "index_merge_hint"}
]
The rejection comes from this check in get_key_scans_params()
(sql/range_optimizer/index_range_scan_plan.cc, 8.4.13):
if (!compound_hint_key_enabled(param->table, keynr,
INDEX_MERGE_HINT_ENUM)) {
trace_idx.add("chosen", false).add_alnum("cause", "index_merge_hint");
continue;
}
test_quick_select() goes through the same function to plan the plain range
scan, so it hits the check too.
The optimizer_switch does what I expected the hint to do.
/*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ blocks the
intersection and the range scan stays. With a = 5 AND b = 10 both the hint
and the switch replace the intersection with ref on idx_b. With
a = 5 AND b IN (10, 11) the switch keeps the range on idx_b (202 rows), but
the hint drops it and the optimizer falls back to ref on the much less
selective idx_a (1000 rows).
A similar hint-scope bug with NO_SKIP_SCAN was fixed in 8.0.31 (Bug #104670).
How to repeat:
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a INT NOT NULL,
b INT NOT NULL,
KEY idx_a (a),
KEY idx_b (b)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 200000;
INSERT INTO t (a, b)
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)
SELECT n % 100 + 1, n % 997 FROM seq;
ANALYZE TABLE t;
-- 1. No hint: range on idx_a, rows=2000
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t WHERE a IN (5, 6);
-- 2. Hint on idx_a: type=ALL (expected: range on idx_a)
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_a) */ * FROM t WHERE a IN (5, 6);
-- 3. Hint plus FORCE INDEX: still type=ALL (expected: range on idx_a, per WL#9167)
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_a) */ * FROM t FORCE INDEX (idx_a) WHERE a IN (5, 6);
-- 4. Hint without index list: type=ALL (expected: range on idx_a)
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t) */ * FROM t WHERE a IN (5, 6);
-- 5. Hint with BETWEEN: type=ALL (expected: range on idx_a)
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_a) */ * FROM t WHERE a BETWEEN 5 AND 6;
-- 6. Hint on another index: range on idx_a, as expected
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_b) */ * FROM t WHERE a IN (5, 6);
-- 7. Equality on the hinted index: ref on idx_a, as expected
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_a) */ * FROM t WHERE a = 5;
-- 8. Equality on both columns: index_merge, Using intersect(idx_b,idx_a)
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t WHERE a = 5 AND b = 10;
-- 9. Hint on the same query: ref on idx_b, as expected
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t) */ * FROM t WHERE a = 5 AND b = 10;
-- 10. optimizer_switch on the same query: ref on idx_b, same as the hint
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ * FROM t WHERE a = 5 AND b = 10;
-- 11. Hint with IN on b: ref on idx_a, rows=1000 (expected: range on idx_b, rows=202)
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t) */ * FROM t WHERE a = 5 AND b IN (10, 11);
-- 12. optimizer_switch instead of the hint: range on idx_b, rows=202
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ * FROM t WHERE a = 5 AND b IN (10, 11);
-- Trace for case 3
SET optimizer_trace = 'enabled=on';
EXPLAIN FORMAT=TRADITIONAL SELECT /*+ NO_INDEX_MERGE(t idx_a) */ * FROM t FORCE INDEX (idx_a) WHERE a IN (5, 6);
SELECT JSON_EXTRACT(TRACE, '$**.range_scan_alternatives') FROM information_schema.OPTIMIZER_TRACE;
Suggested fix:
The check in get_key_scans_params() matters for the union callers in
range_optimizer.cc, get_best_disjunct_quick() and the ROR union loop. That's
the only place the hint gets applied to union children. The plain range call
in test_quick_select() doesn't need it, and intersection is covered anyway,
since get_best_ror_intersect() checks the hint for every candidate on its own.
So the plain range call could skip the check when the hint is NO_INDEX_MERGE.
A new parameter telling get_key_scans_params() whether it's planning an Index
Merge child would be enough. A positive INDEX_MERGE hint can keep working the
way it does now.
