Details
Major Description
In TODO-4276 a customer faced an issue when an efficient "eq_ref" access was replaced with "index" access due to present ORDER BY clause. Such replacements must not happen for JT_EQ_REF, JT_CONST and JT_SYSTEM join types.
Attachments
Activity
oleg.smirnov, please check what happens in the non-unique case and perhaps file an MDEV...
Note for the changelog:
Fixed optimization of subqueries in form (SELECT col FROM t1 WHERE unique_col=ouside_ref ORDER BY .. LIMIT 1) . Equality on unique column means one row will be produced so any sorting is unnecessary (and the question whether to do sorting or use a matching index is irrelevant).
The same test case, but now table t1 doesn't have a PRIMARY KEY, only a non-unique indexes on fields "a" and "b":
CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b));
|
CREATE TABLE t2 (a INT, b INT);
|
INSERT INTO t1 SELECT seq, seq+1 FROM seq_1_to_100;
|
INSERT INTO t2 VALUES (0, 1),(1, 2);
|
ANALYZE TABLE t1, t2 PERSISTENT FOR ALL;
|
|
|
EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
|
2 DEPENDENT SUBQUERY t1 ref a a 5 test.t2.b 1 Using where; Using filesort
|
We can see that "ref" access is not substituted in test_if_skip_sort_order(). psergei, I believe it's the correct behaviour?
Ok to push.