3258 Jorgen Loland 2011-01-18
WL#5741 Post-push fixes:
- made ps-protocol versions of optimizer_trace range and subquery
tests
- Added trace array around propagate_cond_constans()
added:
mysql-test/include/optimizer_trace_range.inc
mysql-test/include/optimizer_trace_subquery.inc
mysql-test/r/optimizer_trace_range_ps_prot.result
mysql-test/r/optimizer_trace_subquery_ps_prot.result
mysql-test/t/optimizer_trace_range_ps_prot.test
mysql-test/t/optimizer_trace_subquery_ps_prot.test
renamed:
mysql-test/r/optimizer_trace_range.result => mysql-test/r/optimizer_trace_range_no_prot.result
mysql-test/r/optimizer_trace_subquery.result => mysql-test/r/optimizer_trace_subquery_no_prot.result
mysql-test/t/optimizer_trace_range.test => mysql-test/t/optimizer_trace_range_no_prot.test
mysql-test/t/optimizer_trace_subquery.test => mysql-test/t/optimizer_trace_subquery_no_prot.test
modified:
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
sql/sql_select.cc
mysql-test/r/optimizer_trace_range_no_prot.result
mysql-test/r/optimizer_trace_subquery_no_prot.result
mysql-test/t/optimizer_trace_range_no_prot.test
mysql-test/t/optimizer_trace_subquery_no_prot.test
3257 Guilhem Bichot 2011-01-18
was confused again, ignore the previous commit for optimizer_trace2.result
and this one
modified:
mysql-test/r/optimizer_trace2.result
=== added file 'mysql-test/include/optimizer_trace_range.inc'
--- a/mysql-test/include/optimizer_trace_range.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc 2011-01-18 13:00:16 +0000
@@ -0,0 +1,239 @@
+# Test for optimizer tracing of range analysis
+
+--source include/have_optimizer_trace.inc
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE t1
+(
+ key1 INT NOT NULL,
+ INDEX i1(key1)
+);
+
+--echo Inserting 1024 records into t1
+
+--disable_query_log
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+
+let $1=7;
+set @d=8;
+while ($1)
+{
+ EVAL INSERT INTO t1 SELECT key1+@d FROM t1;
+ EVAL SET @d=@d*2;
+ DEC $1;
+}
+--enable_query_log
+
+ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
+ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
+ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
+ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
+ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
+ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
+ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
+
+UPDATE t1 SET
+ key2=key1,
+ key3=key1,
+ key4=key1,
+ key5=key1,
+ key6=key1,
+ key7=key1,
+ key8=1024-key1;
+
+CREATE TABLE t2 (
+ key1a INT NOT NULL,
+ key1b INT NOT NULL,
+ key2 INT NOT NULL,
+ key2_1 INT NOT NULL,
+ key2_2 INT NOT NULL,
+ key3 INT NOT NULL,
+ primary key i1a (key1a, key1b),
+ INDEX i1b (key1b, key1a),
+ INDEX i2_1(key2, key2_1),
+ INDEX i2_2(key2, key2_1)
+);
+
+INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
+
+# multiple ranges on one key
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# multiple ranges on one key, turn off range_optimizer tracing
+set @@optimizer_trace_features="range_optimizer=off";
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+set @@optimizer_trace_features="range_optimizer=on";
+
+
+# index merge
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# group without range
+--echo
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# distinct - group quick select without grouping attribute
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# group with range
+--echo
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+#intersect
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# union
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# range_scan_possible=false
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Multiple key parts in same index
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# more_expensive_than_table_scan
+--echo
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Range analysis on straight join
+--echo
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+ WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (
+ cola char(3) not null,
+ colb char(3) not null,
+ filler char(200),
+ key(cola),
+ key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+
+--echo Inserting records
+--disable_query_log
+let $1=9;
+while ($1)
+{
+ eval INSERT INTO t1 SELECT * FROM t1 WHERE cola = 'foo';
+ dec $1;
+}
+LET $1=13;
+WHILE ($1)
+{
+ eval INSERT INTO t1 SELECT * FROM t1 WHERE cola <> 'foo';
+ dec $1;
+}
+
+--enable_query_log
+
+--echo
+# Index roworder intersect
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+--echo
+# Range with escaped character should be printed escaped
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+# Test that range optimization is not shown for every outer record
+# when there is a dynamic range.
+CREATE TABLE t1(c INT);
+INSERT INTO t1 VALUES (),();
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (),(),();
+
+# First, enable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
+EXPLAIN SELECT 1 FROM
+ (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# Second, disable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
+EXPLAIN SELECT 1 FROM
+ (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
+
+CREATE TABLE `t1` (
+ `mot` varchar(4) NOT NULL,
+ `topic` int NOT NULL,
+ PRIMARY KEY (`mot`,`topic`)
+ );
+INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
+
+# rechecking_index_usage/no_indices_to_analyze
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+drop table t1;
+
+# Range analysis in test_if_skip_sort_order
+# (records_estimation_for_index_ordering)
+CREATE TABLE t1 (
+ i1 int,
+ i2 int,
+ c char(1),
+ KEY k1 (i1),
+ KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1;
=== added file 'mysql-test/include/optimizer_trace_subquery.inc'
--- a/mysql-test/include/optimizer_trace_subquery.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/optimizer_trace_subquery.inc 2011-01-18 13:00:16 +0000
@@ -0,0 +1,51 @@
+# Test for optimizer tracing of subqueries
+
+--source include/have_optimizer_trace.inc
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (2);
+INSERT INTO t2 VALUES (1,7),(2,7);
+
+--echo # Subselect execute is traced every time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+--echo # Subselect execute is traced only the first time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
+SET @@optimizer_trace_features="default";
+
+
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+
+# evaluate_subselect_cond_steps for build_equal_item()
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+ t1.a= (SELECT a FROM t2 LIMIT 1) ;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# evaluate_subselect_cond_steps for remove_eq_conds
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+ (SELECT * FROM t2 WHERE a = 50 AND b = 3);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1,t2;
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2011-01-18 08:57:55 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2011-01-18 13:00:16 +0000
@@ -944,6 +944,8 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
},
{
@@ -1168,7 +1170,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-13024
+13136
set optimizer_trace_max_mem_size=12700;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -1177,7 +1179,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-439
+560
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -1185,7 +1187,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-12585 1 1
+12576 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@query2) + length(@trace2)
@@ -1560,6 +1562,8 @@ explain SELECT c FROM t5 where c+1 in (s
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
},
{
@@ -1828,6 +1832,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
},
{
@@ -2038,6 +2044,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
},
{
@@ -2265,6 +2273,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
},
{
@@ -2515,6 +2525,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
},
{
@@ -3066,6 +3078,8 @@ where a1 in (select b1 from t2_16 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
},
{
@@ -3326,6 +3340,8 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))"
},
{
@@ -3996,6 +4012,8 @@ concat(c1,'y') IN
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
},
{
@@ -4875,6 +4893,8 @@ select * from t1 where (t1.a,t1.b) not i
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
},
{
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-17 20:41:34 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2011-01-18 13:00:16 +0000
@@ -928,6 +928,8 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
},
{
@@ -1152,16 +1154,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-12502
+12614
set optimizer_trace_max_mem_size=12700;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
-0
+1
SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-0
+27
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -1169,7 +1171,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-12502 1 1
+12587 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@query2) + length(@trace2)
@@ -1544,6 +1546,8 @@ explain SELECT c FROM t5 where c+1 in (s
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>((`test`.`t5`.`c` + 1),(`test`.`t5`.`c` + 1) in (/* select#2 */ select (`test`.`t6`.`d` + 1) from `test`.`t6` where isnull(`test`.`t6`.`d`)))"
},
{
@@ -1812,6 +1816,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
},
{
@@ -2022,6 +2028,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
},
{
@@ -2249,6 +2257,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
},
{
@@ -2499,6 +2509,8 @@ explain extended select * from t1 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
},
{
@@ -3050,6 +3062,8 @@ where a1 in (select b1 from t2_16 where
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
},
{
@@ -3310,6 +3324,8 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(<in_optimizer>(`test`.`t2`.`c2`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))) and multiple equal(`test`.`t1`.`c1`, `test`.`t2`.`c2`))"
},
{
@@ -3980,6 +3996,8 @@ concat(c1,'y') IN
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
},
{
@@ -4851,6 +4869,8 @@ select * from t1 where (t1.a,t1.b) not i
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
},
{
=== renamed file 'mysql-test/r/optimizer_trace_range.result' => 'mysql-test/r/optimizer_trace_range_no_prot.result'
--- a/mysql-test/r/optimizer_trace_range.result 2011-01-17 20:41:34 +0000
+++ b/mysql-test/r/optimizer_trace_range_no_prot.result 2011-01-18 13:00:16 +0000
@@ -3642,6 +3642,8 @@ SELECT * from t1 where topic = all (SELE
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
},
{
=== added file 'mysql-test/r/optimizer_trace_range_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_range_ps_prot.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-01-18 13:00:16 +0000
@@ -0,0 +1,4636 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE t1
+(
+key1 INT NOT NULL,
+INDEX i1(key1)
+);
+Inserting 1024 records into t1
+ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
+ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
+ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
+ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
+ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
+ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
+ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
+UPDATE t1 SET
+key2=key1,
+key3=key1,
+key4=key1,
+key5=key1,
+key6=key1,
+key7=key1,
+key8=1024-key1;
+CREATE TABLE t2 (
+key1a INT NOT NULL,
+key1b INT NOT NULL,
+key2 INT NOT NULL,
+key2_1 INT NOT NULL,
+key2_2 INT NOT NULL,
+key3 INT NOT NULL,
+primary key i1a (key1a, key1b),
+INDEX i1b (key1b, key1a),
+INDEX i2_1(key2, key2_1),
+INDEX i2_2(key2, key2_1)
+);
+INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range i2 i2 4 NULL 47 Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "key2 < 5",
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 47,
+ "cost": 58.41,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 47,
+ "ranges": [
+ "key2 < 5",
+ "1020 < key2"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 47,
+ "cost_for_plan": 58.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 47,
+ "cost": 58.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 58.41,
+ "records_for_plan": 47,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set @@optimizer_trace_features="range_optimizer=off";
+
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range i2 i2 4 NULL 47 Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": "...",
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": "...",
+ "analyzing_range_alternatives": "...",
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 47,
+ "ranges": [
+ "key2 < 5",
+ "1020 < key2"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 47,
+ "cost_for_plan": 58.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 47,
+ "cost": 58.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 58.41,
+ "records_for_plan": 47,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set @@optimizer_trace_features="range_optimizer=on";
+
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": true,
+ "key_parts": [
+ "key1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "key1 < 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 3,
+ "cost": 1.6526,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i1",
+ "cumulated_cost": 1.6526
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 42,
+ "cost": 10.282,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i2",
+ "cumulated_cost": 11.935
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 11.935,
+ "cost_sort_rowid_and_read_disk": 8.0666,
+ "cost_duplicate_removal": 38.361,
+ "total_cost": 58.363
+ }
+ ] /* analyzing_index_merge */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_merge",
+ "index_merge_of": [
+ {
+ "type": "range_scan",
+ "index": "i1",
+ "records": 3,
+ "ranges": [
+ "key1 < 3"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */
+ }
+ ] /* index_merge_of */
+ } /* range_access_plan */,
+ "records_for_plan": 45,
+ "cost_for_plan": 58.363,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 45,
+ "cost": 58.363,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 58.363,
+ "records_for_plan": 45,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range NULL i2_1 4 NULL 103 Using index for group-by
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` group by `test`.`t2`.`key2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "i2_1",
+ "i2_2"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "i2_1",
+ "cost": 235.03,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 103,
+ "cost_for_plan": 50.6,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 103,
+ "cost": 50.6,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 50.6,
+ "records_for_plan": 103,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range NULL i2_1 4 NULL 103 Using index for group-by
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT key2 FROM t2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select distinct `test`.`t2`.`key2` AS `key2` from `test`.`t2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "i2_1",
+ "i2_2"
+ ] /* keys */,
+ "cause": "distinct"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "i2_1",
+ "cost": 235.03,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 103,
+ "cost_for_plan": 50.6,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 103,
+ "cost": 50.6,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 50.6,
+ "records_for_plan": 103,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range i2_1,i2_2 i2_1 4 NULL 47 Using where; Using index
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` where (`test`.`t2`.`key2` < 5) group by `test`.`t2`.`key2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`key2` < 5)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t2`.`key2` < 5)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t2`.`key2` < 5)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t2`.`key2` < 5)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "i2_1",
+ "cost": 235.03,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "records": 5,
+ "cost": 31
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "records": 5,
+ "cost": 31
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 5,
+ "cost": 31,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2_1",
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 47,
+ "cost": 11.724,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i2_2",
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 47,
+ "cost": 11.724,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2_1",
+ "records": 47,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 47,
+ "cost_for_plan": 11.724,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 5,
+ "cost": 20.124,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 20.124,
+ "records_for_plan": 5,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`key2` < 5)",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`key2` < 5)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref i2_1,i2_2 i2_1 4 const 10 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "key2",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "key2",
+ "equals": "1",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2_1",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i2_2",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2_1",
+ "records": 10,
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 10,
+ "cost_for_plan": 13.01,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i2_1",
+ "records": 10,
+ "cost": 10,
+ "chosen": true
+ },
+ {
+ "access_type": "ref",
+ "index": "i2_2",
+ "records": 10,
+ "cost": 10,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": true,
+ "key_parts": [
+ "key3"
+ ] /* key_parts */
+ },
+ {
+ "index": "i4",
+ "usable": true,
+ "key_parts": [
+ "key4"
+ ] /* key_parts */
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "null_field_in_non_null_column"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i2",
+ "cumulated_cost": 2.21
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i3",
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i3",
+ "cumulated_cost": 4.42
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 4.42,
+ "use_roworder_union": true,
+ "cause": "always_cheaper_than_not_roworder_retrieval",
+ "analyzing_roworder_scans": [
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ },
+ {
+ "type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ }
+ ] /* analyzing_roworder_scans */,
+ "index_roworder_union_cost": 6.327,
+ "members": 2,
+ "chosen": true
+ }
+ ] /* analyzing_index_merge */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_union",
+ "union_of": [
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */
+ }
+ ] /* union_of */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 6.327,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 2,
+ "cost": 6.327,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 6.327,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL i2_1,i2_2 NULL NULL NULL 1024 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "range_scan_possible": false,
+ "cause": "condition_always_true",
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1024,
+ "cost": 8.25,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 8.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY,i1b PRIMARY 8 NULL 1 Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "key1a",
+ "equals": "5",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "key1a",
+ "key1b"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1b",
+ "usable": true,
+ "key_parts": [
+ "key1b",
+ "key1a"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2_2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "5 <= key1a <= 5 AND key1b < 10"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i1b",
+ "ranges": [
+ "key1b < 10 AND 5 <= key1a <= 5"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 9,
+ "cost": 11.81,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "records": 1,
+ "ranges": [
+ "5 <= key1a <= 5 AND key1b < 10"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 10,
+ "cost": 10.24,
+ "chosen": true
+ },
+ {
+ "access_type": "range",
+ "records": 1,
+ "cost": 2.21,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.21,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 1024 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": true,
+ "key_parts": [
+ "key1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "1 < key1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1023,
+ "cost": 227.35,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "2 < key2"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1022,
+ "cost": 227.13,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 0,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* analyzing_index_merge */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1024,
+ "cost": 10.25,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range i1,i2 i2 4 NULL 42 Using index condition; Using MRR
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.key1 10
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select straight_join `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8`,`test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`key1` = `test`.`t2`.`key1a`) and (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key1` = `test`.`t2`.`key1a`) and (`test`.`t1`.`key2` > 1020))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "key1",
+ "equals": "`test`.`t2`.`key1a`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "key1a",
+ "equals": "`test`.`t1`.`key1`",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 42,
+ "cost": 51.41,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 42,
+ "cost_for_plan": 51.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 1024,
+ "cost": 8
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "i1",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "records": 42,
+ "cost": 51.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 51.41,
+ "records_for_plan": 42
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 10,
+ "cost": 430.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 768,
+ "cost": 59.535,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 481.49,
+ "records_for_plan": 420
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`key2` > 1020)"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1,t2;
+CREATE TABLE t1 (
+cola char(3) not null,
+colb char(3) not null,
+filler char(200),
+key(cola),
+key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+Inserting records
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "cola",
+ "equals": "'foo'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "colb",
+ "equals": "'bar'",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8704,
+ "cost": 2184.8
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "key_parts": [
+ "cola"
+ ] /* key_parts */
+ },
+ {
+ "index": "colb",
+ "usable": true,
+ "key_parts": [
+ "colb"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "colb",
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "matching_records_now": 533,
+ "cumulated_cost": 320.38,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "colb",
+ "usable": true,
+ "matching_records_now": 32.639,
+ "cumulated_cost": 53.359,
+ "isect_covering_with_this_index": false
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_added_to_intersect": false,
+ "cause": "no_clustered_pk_index"
+ } /* clustered_pk */,
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "clustered_pk_scan": false,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "cola",
+ "records": 533,
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "colb",
+ "records": 533,
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 32,
+ "cost_for_plan": 53.359,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "cola",
+ "records": 533,
+ "cost": 533,
+ "chosen": true
+ },
+ {
+ "access_type": "ref",
+ "index": "colb",
+ "records": 533,
+ "cost": 533,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "records": 24,
+ "cost": 54.959,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 54.959,
+ "records_for_plan": 24,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref cola cola 3 const 1 Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no' {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`cola` = 'f\\no')"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`cola` = 'f\\no')",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal('f\\no', `test`.`t1`.`cola`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal('f\\no', `test`.`t1`.`cola`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal('f\\no', `test`.`t1`.`cola`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "cola",
+ "equals": "'f\\no'",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8704,
+ "cost": 2184.8
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "key_parts": [
+ "cola"
+ ] /* key_parts */
+ },
+ {
+ "index": "colb",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "f\no <= cola <= f\no"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "cola",
+ "records": 1,
+ "ranges": [
+ "f\no <= cola <= f\no"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "cola",
+ "records": 1,
+ "cost": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`cola` = 'f\\no')",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`cola` = 'f\\no')"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1;
+CREATE TABLE t1(c INT);
+INSERT INTO t1 VALUES (),();
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (),(),();
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+2 DERIVED t2 ALL b NULL NULL NULL 3 Range checked for each record (index map: 0x1)
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "attached_conditions_computation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "rechecking_index_usage": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 5.6
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "b",
+ "index_only": true,
+ "records": 18446744073709551615,
+ "cost": 0,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* rechecking_index_usage */
+ }
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ },
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ }
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1) `d2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "d2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+2 DERIVED t2 ALL b NULL NULL NULL 3 Range checked for each record (index map: 0x1)
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT 1 FROM
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+ "attached_conditions_computation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "rechecking_index_usage": {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 5.6
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "b",
+ "index_only": true,
+ "records": 18446744073709551615,
+ "cost": 0,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* rechecking_index_usage */
+ }
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation_per_record": {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "b",
+ "usable": true,
+ "key_parts": [
+ "b"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "b",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ {
+ "impossible_condition": {
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */
+ }
+ ] /* setup_range_conditions */,
+ "impossible_range": true
+ } /* range_analysis */
+ } /* records_estimation_per_record */
+ }
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by 1 limit 1) `d2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "d2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
+CREATE TABLE `t1` (
+`mot` varchar(4) NOT NULL,
+`topic` int NOT NULL,
+PRIMARY KEY (`mot`,`topic`)
+);
+INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
+mot topic
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`mot` AS `mot`,`test`.`t1`.`topic` AS `topic` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "HAVING",
+ "original_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))",
+ "steps": [
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5098
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "mot",
+ "topic"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4303,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "covering": true,
+ "usable": false,
+ "cause": "group_attribute_not_prefix_in_index"
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "rechecking_index_usage": {
+ } /* rechecking_index_usage */
+ }
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+drop table t1;
+CREATE TABLE t1 (
+i1 int,
+i2 int,
+c char(1),
+KEY k1 (i1),
+KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range k1,k2 k2 5 NULL 2 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`i1` > '2') order by `test`.`t1`.`i1`,`test`.`t1`.`i2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`i1` > '2')",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t1`.`i1` > '2')"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t1`.`i1` > '2')"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t1`.`i1` > '2')"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "k2",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "k1",
+ "records": 2,
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 3.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`i1` > '2')",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`i1` > '2')"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k2",
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "k2",
+ "records": 2,
+ "ranges": [
+ "2 < i1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 3.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index k1,k2 k1 5 NULL 2 Using where; Using index
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i1` >= '1') order by `test`.`t1`.`i1` desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`i1` >= '1')",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t1`.`i1` >= '1')"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t1`.`i1` >= '1')"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t1`.`i1` >= '1')"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": true,
+ "key_parts": [
+ "i1",
+ "i2"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "k2",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`i1` >= '1')",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`i1` >= '1')"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
+ },
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation_for_index_ordering": {
+ "database": "test",
+ "table": "t1",
+ "index": "k1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5049
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "k1",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "k2",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "k1",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "k1",
+ "covering": true,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "records": 3,
+ "cost": 1.6
+ },
+ {
+ "index": "k2",
+ "covering": true,
+ "records": 3,
+ "cost": 1.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "k1",
+ "group_attribute": null,
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 3,
+ "cost": 1.6,
+ "key_parts_used_for_access": [
+ "i1"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "k1",
+ "ranges": [
+ "1 <= i1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ } /* records_estimation_for_index_ordering */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1;
=== renamed file 'mysql-test/r/optimizer_trace_subquery.result' => 'mysql-test/r/optimizer_trace_subquery_no_prot.result'
--- a/mysql-test/r/optimizer_trace_subquery.result 2011-01-17 20:41:34 +0000
+++ b/mysql-test/r/optimizer_trace_subquery_no_prot.result 2011-01-18 13:00:16 +0000
@@ -434,6 +434,8 @@ t1.a= (SELECT a FROM t2 LIMIT 1) {
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))"
},
{
@@ -510,6 +512,8 @@ WHERE NOT EXISTS
},
{
"transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
"resulting_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))"
},
{
=== added file 'mysql-test/r/optimizer_trace_subquery_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_subquery_ps_prot.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace_subquery_ps_prot.result 2011-01-18 13:00:16 +0000
@@ -0,0 +1,593 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (2);
+INSERT INTO t2 VALUES (1,7),(2,7);
+# Subselect execute is traced every time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+(SELECT a FROM t1 WHERE t1.a=t2.a) a
+NULL 1
+2 2
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "('2' = `test`.`t2`.`a`)",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ },
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+# Subselect execute is traced only the first time it is executed
+SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
+(SELECT a FROM t1 WHERE t1.a=t2.a) a
+NULL 1
+2 2
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(`test`.`t1`.`a` = `test`.`t2`.`a`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "('2' = `test`.`t2`.`a`)",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
+SET @@optimizer_trace_features="default";
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) ;
+a
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_preparation": {
+ "select#": 3,
+ "steps": [
+ {
+ "expanded_query": "/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "((`test`.`t1`.`a` = NULL) and (NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": null
+ }
+ ] /* steps */
+ } /* condition_processing */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "Impossible WHERE"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+(SELECT * FROM t2 WHERE a = 50 AND b = 3);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+SELECT 1 FROM DUAL
+WHERE NOT EXISTS
+(SELECT * FROM t2 WHERE a = 50 AND b = 3) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* subselect_cond_removal */,
+ "resulting_condition": null
+ }
+ ] /* steps */
+ } /* condition_processing */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1,t2;
=== renamed file 'mysql-test/t/optimizer_trace_range.test' => 'mysql-test/t/optimizer_trace_range_no_prot.test'
--- a/mysql-test/t/optimizer_trace_range.test 2011-01-14 13:42:35 +0000
+++ b/mysql-test/t/optimizer_trace_range_no_prot.test 2011-01-18 13:00:16 +0000
@@ -1,239 +1,8 @@
-# Basic test for optimizer trace
-
---source include/have_optimizer_trace.inc
-
-SET optimizer_trace_max_mem_size=1048576; # 1MB
-SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
-
-CREATE TABLE t1
-(
- key1 INT NOT NULL,
- INDEX i1(key1)
-);
-
---echo Inserting 1024 records into t1
-
---disable_query_log
-INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
-
-let $1=7;
-set @d=8;
-while ($1)
-{
- EVAL INSERT INTO t1 SELECT key1+@d FROM t1;
- EVAL SET @d=@d*2;
- DEC $1;
-}
---enable_query_log
-
-ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
-ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
-ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
-ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
-ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
-ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
-ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
-
-UPDATE t1 SET
- key2=key1,
- key3=key1,
- key4=key1,
- key5=key1,
- key6=key1,
- key7=key1,
- key8=1024-key1;
-
-CREATE TABLE t2 (
- key1a INT NOT NULL,
- key1b INT NOT NULL,
- key2 INT NOT NULL,
- key2_1 INT NOT NULL,
- key2_2 INT NOT NULL,
- key3 INT NOT NULL,
- primary key i1a (key1a, key1b),
- INDEX i1b (key1b, key1a),
- INDEX i2_1(key2, key2_1),
- INDEX i2_2(key2, key2_1)
-);
-
-INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
-
-# multiple ranges on one key
---echo
-EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# multiple ranges on one key, turn off range_optimizer tracing
-set @@optimizer_trace_features="range_optimizer=off";
---echo
-EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-set @@optimizer_trace_features="range_optimizer=on";
-
-
-# index merge
---echo
-EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# group without range
---echo
-EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# distinct - group quick select without grouping attribute
-EXPLAIN SELECT DISTINCT key2 FROM t2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# group with range
---echo
-EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-#intersect
---echo
-EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# union
---echo
-EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# range_scan_possible=false
---echo
-EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# Multiple key parts in same index
---echo
-EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# more_expensive_than_table_scan
---echo
-EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-# Range analysis on straight join
---echo
-EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
- WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-DROP TABLE t1,t2;
-
-CREATE TABLE t1 (
- cola char(3) not null,
- colb char(3) not null,
- filler char(200),
- key(cola),
- key(colb)
-);
-INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
-
---echo Inserting records
---disable_query_log
-let $1=9;
-while ($1)
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
+ + $VIEW_PROTOCOL > 0`)
{
- eval INSERT INTO t1 SELECT * FROM t1 WHERE cola = 'foo';
- dec $1;
+ --skip Need normal protocol
}
-LET $1=13;
-WHILE ($1)
-{
- eval INSERT INTO t1 SELECT * FROM t1 WHERE cola <> 'foo';
- dec $1;
-}
-
---enable_query_log
-
---echo
-# Index roworder intersect
-EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
---echo
-# Range with escaped character should be printed escaped
-EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
-
-DROP TABLE t1;
-
-# Test that range optimization is not shown for every outer record
-# when there is a dynamic range.
-CREATE TABLE t1(c INT);
-INSERT INTO t1 VALUES (),();
-CREATE TABLE t2 (b INT, KEY(b));
-INSERT INTO t2 VALUES (),(),();
-
-# First, enable dynamic range optimization tracing
-SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
-EXPLAIN SELECT 1 FROM
- (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-# Second, disable dynamic range optimization tracing
-SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
-EXPLAIN SELECT 1 FROM
- (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-DROP TABLE t1,t2;
-
-CREATE TABLE `t1` (
- `mot` varchar(4) NOT NULL,
- `topic` int NOT NULL,
- PRIMARY KEY (`mot`,`topic`)
- );
-INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
-
-# rechecking_index_usage/no_indices_to_analyze
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-drop table t1;
-
-# Range analysis in test_if_skip_sort_order
-# (records_estimation_for_index_ordering)
-CREATE TABLE t1 (
- i1 int,
- i2 int,
- c char(1),
- KEY k1 (i1),
- KEY k2 (i1, i2)
-);
-INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
-
-EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-DROP TABLE t1;
+# The main testing script
+--source include/optimizer_trace_range.inc
=== added file 'mysql-test/t/optimizer_trace_range_ps_prot.test'
--- a/mysql-test/t/optimizer_trace_range_ps_prot.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace_range_ps_prot.test 2011-01-18 13:00:16 +0000
@@ -0,0 +1,8 @@
+if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0
+ OR $PS_PROTOCOL = 0`)
+{
+ --skip Need ps-protocol
+}
+
+# The main testing script
+--source include/optimizer_trace_range.inc
=== renamed file 'mysql-test/t/optimizer_trace_subquery.test' => 'mysql-test/t/optimizer_trace_subquery_no_prot.test'
--- a/mysql-test/t/optimizer_trace_subquery.test 2011-01-14 13:42:35 +0000
+++ b/mysql-test/t/optimizer_trace_subquery_no_prot.test 2011-01-18 13:00:16 +0000
@@ -1,49 +1,8 @@
---source include/have_optimizer_trace.inc
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
+ + $VIEW_PROTOCOL > 0`)
+{
+ --skip Need normal protocol
+}
-SET optimizer_trace_max_mem_size=1048576; # 1MB
-SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
-
-CREATE TABLE t1 (a INT);
-CREATE TABLE t2 (a INT, b INT);
-INSERT INTO t1 VALUES (2);
-INSERT INTO t2 VALUES (1,7),(2,7);
-
---echo # Subselect execute is traced every time it is executed
-SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
-SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
---echo # Subselect execute is traced only the first time it is executed
-SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
-SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-DROP TABLE t1,t2;
-SET @@optimizer_trace_features="default";
-
-
-CREATE TABLE t1 (a FLOAT(5,4) zerofill);
-CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
-
-# evaluate_subselect_cond_steps for build_equal_item()
-SELECT t1.a
-FROM t1
-WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
- t1.a= (SELECT a FROM t2 LIMIT 1) ;
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-# evaluate_subselect_cond_steps for remove_eq_conds
-SELECT 1 FROM DUAL
-WHERE NOT EXISTS
- (SELECT * FROM t2 WHERE a = 50 AND b = 3);
---echo
-SELECT * FROM information_schema.OPTIMIZER_TRACE;
---echo
-
-DROP TABLE t1,t2;
+# The main testing script
+--source include/optimizer_trace_subquery.inc
=== added file 'mysql-test/t/optimizer_trace_subquery_ps_prot.test'
--- a/mysql-test/t/optimizer_trace_subquery_ps_prot.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace_subquery_ps_prot.test 2011-01-18 13:00:16 +0000
@@ -0,0 +1,8 @@
+if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0
+ OR $PS_PROTOCOL = 0`)
+{
+ --skip Need ps-protocol
+}
+
+# The main testing script
+--source include/optimizer_trace_subquery.inc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-17 20:41:34 +0000
+++ b/sql/sql_select.cc 2011-01-18 13:00:16 +0000
@@ -14618,7 +14618,13 @@ optimize_cond(JOIN *join, Item *conds, L
{
Opt_trace_object step_wrapper(trace);
step_wrapper.add_alnum("transformation", "constant_propagation");
- propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);
+ {
+ Opt_trace_disable_I_S disable_trace_wrapper(trace,
+ !conds->with_subselect);
+ Opt_trace_array trace_subselect(trace,
+ "subselect_constant_propagation");
+ propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);
+ }
step_wrapper.add("resulting_condition", conds);
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3257 to 3258)WL#5741 | Jorgen Loland | 18 Jan |
