Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 11.2(EOL)
Description
This is coming from a discussion on the Optimizer Call.
According to igor, the query below should be handled by derived_with_keys optimization but isn't.
First, fill the table and try a query that does work:
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, notes TEXT NOT NULL); |
INSERT INTO t1 VALUES (1, 'test1'), (2, 'test2'); |
insert into t1 select seq, seq from seq_10_to_10000; |
analyze table t1; |
explain SELECT dt.* |
FROM |
((SELECT id, notes FROM t1 LIMIT 100) UNION |
(SELECT id, notes FROM t1 LIMIT 200)) dt, seq_1_to_10 SEQ |
WHERE
|
dt.id = SEQ.seq;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY SEQ index PRIMARY PRIMARY 8 NULL 10 Using index
|
1 PRIMARY <derived2> ref key0 key0 4 test.SEQ.seq 10 Using index condition
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9993
|
3 UNION t1 ALL NULL NULL NULL NULL 9993
|
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
|
Ok, derived_with_keys is used.
But in this query it is not used:
explain SELECT dt.* |
FROM |
((SELECT * FROM t1 LIMIT 100) UNION |
(SELECT * FROM t1 LIMIT 200)) dt |
WHERE |
id = 1;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 300 Using where
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9993
|
3 UNION t1 ALL NULL NULL NULL NULL 9993
|
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
|
Derived_with_keys is not used.