#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem@stripped
3202 Guilhem Bichot 2010-09-08
test of optimizer_trace vs {query with a stored function, or a stored procedure,
or a trigger, or a view, or an INFORMATION_SCHEMA table}. Test that
first and second EXECUTE of a prepared statement give the same trace.
@ sql/sql_select.cc
more disabling of range optimizer's tracing
modified:
WL4800_TODO.txt
mysql-test/include/optimizer_trace.inc
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
sql/sql_select.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt 2010-09-05 16:08:07 +0000
+++ b/WL4800_TODO.txt 2010-09-08 10:27:44 +0000
@@ -22,10 +22,13 @@ rsync -avz /m/doxygen/mysql-wl4800 [URL]
fix bugs recorded in optimizer_trace_bugs.test and delete that test.
-test with VIEWS (mergeable or not), INFORMATION_SCHEMA tables.
-
Put in the trace some way to relate "select #N" to the corresponding
-SELECT.
+SELECT. Idea:
+top_query: "SELECT ... UNION SELECT ... WHERE x IN (SELECT ...)"
+select# : "^1 ^2 ^3"
+This is not a complete solution, because for "SELECT stored_func()",
+the stored func can contain SELECTs, each of them has a select#. Same
+for "CALL stored_proc()".
Is String an acceptable way of memory management for the trace, for a
first version?
=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc 2010-09-03 08:18:12 +0000
+++ b/mysql-test/include/optimizer_trace.inc 2010-09-08 10:27:44 +0000
@@ -251,9 +251,83 @@ select * from information_schema.OPTIMIZ
insert into D select * from D where D>7;
select * from information_schema.OPTIMIZER_TRACE;
-# TODO: what about CALL, stored functions, triggers?
+# Stored functions
-# TODO: PREPARE/EXECUTE/EXECUTE
+create table t1 (
+ id char(16) not null default '',
+ data int not null
+);
+create table t2 (
+ s char(16),
+ i int,
+ d double
+);
+delimiter |;
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+ return (select sum(data) from t1)|
+select f1()|
+select * from information_schema.OPTIMIZER_TRACE|
+select s, f1() from t2 order by s desc|
+select * from information_schema.OPTIMIZER_TRACE|
+select * from D where d in (select f1() from t2 where s="c")|
+select * from information_schema.OPTIMIZER_TRACE|
+# Stored procedures
+
+create procedure p1(arg char(1))
+begin
+ declare res int;
+ select d into res from D where d in (select f1() from t2 where s=arg);
+ select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+select * from information_schema.OPTIMIZER_TRACE|
+
+# Triggers
+create trigger trg1 before insert on t2 for each row
+begin
+ set new.s=f1();
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+select * from t2|
+
+delimiter ;|
+
+# PREPARE/EXECUTE/EXECUTE
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+set @param="c";
+execute stmt using @param;
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+# second EXECUTE should give same trace
+execute stmt using @param;
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+
+# Views
+# merge-able
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+select * from information_schema.OPTIMIZER_TRACE;
+drop view v1;
+# not merge-able
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+select * from information_schema.OPTIMIZER_TRACE;
+drop view v1;
+
+# I_S tables
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+select * from information_schema.OPTIMIZER_TRACE;
+
+drop table t1,t2;
DROP TABLE C,D;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2010-09-04 17:40:51 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-09-08 10:27:44 +0000
@@ -3456,5 +3456,2247 @@ QUERY_ID TRACE
}
] /* steps */
}
+create table t1 (
+id char(16) not null default '',
+data int not null
+);
+create table t2 (
+s char(16),
+i int,
+d double
+);
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select f1()",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select s, f1() from t2 order by s desc|
+s f1()
+c 3
+b 3
+a 3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select s, f1() from t2 order by s desc",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t2",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select * from D where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select * from D where d in (select f1() from t2 where s="c")",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 3,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2.6076,
+ "records": 1,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 1,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2,
+ "records": 4,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from D where d in (select f1() from t2 where s=arg);
+select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "call p1("c")",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 3,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2.6076,
+ "records": 1,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 1,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2,
+ "records": 4,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= (res@1 + 1)",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+create trigger trg1 before insert on t2 for each row
+begin
+set new.s=f1();
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "insert into t2 select d,100,200 from D where d is not null",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` is not null)",
+ "after_equality_propagation": "(`test`.`D`.`d` is not null)",
+ "after_constant_propagation": "(`test`.`D`.`d` is not null)",
+ "after_trivial_conditions_removal": "(`test`.`D`.`d` is not null)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0068,
+ "records": 4,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0068,
+ "records_for_plan": 4,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`D`.`d` is not null)",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` is not null)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select * from t2|
+s i d
+a 1 1
+b 2 2
+c 3 3
+3 100 200
+3 100 200
+3 100 200
+3 100 200
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "prepare stmt from 'call p1(?)'",
+ "steps": [
+ ] /* steps */
+}
+set @param="c";
+execute stmt using @param;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+@trace
+
+{
+ "top_query": "execute stmt using @param",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+ "after_constant_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+ "after_trivial_conditions_removal": "((`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "table_scan": {
+ "records": 4,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 7,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0496,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0496,
+ "records_for_plan": 7,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0068,
+ "records": 4,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0068,
+ "records_for_plan": 4,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0499,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0567,
+ "records_for_plan": 28,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0499,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 4.0567,
+ "records": 4,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 4.6064,
+ "records": 4,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 15.257,
+ "records": 4,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0496,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0496,
+ "records_for_plan": 7,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 8.1982,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= (res@1 + 1)",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+execute stmt using @param;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+@trace=@trace2
+1
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "explain select * from v1 where id="b"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`id` = 'b') and (`test`.`t1`.`id` < 'c'))",
+ "after_equality_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+ "after_constant_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+ "after_trivial_conditions_removal": "multiple equal('b', `test`.`t1`.`id`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`id` = 'b')",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "(`test`.`t1`.`id` = 'b')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop view v1;
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "explain select * from v1 where id="b"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`id` < 'c')",
+ "after_equality_propagation": "(`test`.`t1`.`id` < 'c')",
+ "after_constant_propagation": "(`test`.`t1`.`id` < 'c')",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`id` < 'c')"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`id` < 'c')",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "(`test`.`t1`.`id` < 'c')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`v1`.`id` = 'b')",
+ "after_equality_propagation": "multiple equal('b', `v1`.`id`)",
+ "after_constant_propagation": "multiple equal('b', `v1`.`id`)",
+ "after_trivial_conditions_removal": "multiple equal('b', `v1`.`id`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "v1",
+ "table_scan": {
+ "records": 2,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "v1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 10.1,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.1,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`v1`.`id` = 'b')",
+ "attached_conditions": [
+ {
+ "table": "v1",
+ "attached": "(`v1`.`id` = 'b')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop view v1;
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+VARIABLE_NAME VARIABLE_VALUE
+OPTIMIZER_TRACE enabled=on,end_marker=on,one_line=off,skip_plan=off
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_equality_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_constant_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_trivial_conditions_removal": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "SESSION_VARIABLES",
+ "table_scan": {
+ "records": 2,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "SESSION_VARIABLES",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 10.1,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.1,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "attached_conditions": [
+ {
+ "table": "SESSION_VARIABLES",
+ "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop table t1,t2;
DROP TABLE C,D;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2010-09-04 17:40:51 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2010-09-08 10:27:44 +0000
@@ -3432,5 +3432,2247 @@ QUERY_ID TRACE
}
] /* steps */
}
+create table t1 (
+id char(16) not null default '',
+data int not null
+);
+create table t2 (
+s char(16),
+i int,
+d double
+);
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select f1()",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select s, f1() from t2 order by s desc|
+s f1()
+c 3
+b 3
+a 3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select s, f1() from t2 order by s desc",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t2",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select * from D where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "select * from D where d in (select f1() from t2 where s="c")",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 3,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2.6076,
+ "records": 1,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 1,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2,
+ "records": 4,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from D where d in (select f1() from t2 where s=arg);
+select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "call p1("c")",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "transformation": {
+ "select #": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": true
+ } /* transformation */
+ },
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+ "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= `f1`()",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 3,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0212,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 3,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2.6076,
+ "records": 1,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.6289,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 5.2289,
+ "records": 1,
+ "duplicate_tables_left": true,
+ "chosen": true
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "d",
+ "cost": 1,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "table scan",
+ "cost": 2,
+ "records": 4,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.0213,
+ "records_for_plan": 3,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0213,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 3.0213,
+ "records": 1,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 3.2212,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 3.6213,
+ "records": 1,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0212,
+ "records": 3,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= (res@1 + 1)",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+create trigger trg1 before insert on t2 for each row
+begin
+set new.s=f1();
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID TRACE
+0
+{
+ "top_query": "insert into t2 select d,100,200 from D where d is not null",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` is not null)",
+ "after_equality_propagation": "(`test`.`D`.`d` is not null)",
+ "after_constant_propagation": "(`test`.`D`.`d` is not null)",
+ "after_trivial_conditions_removal": "(`test`.`D`.`d` is not null)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0068,
+ "records": 4,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0068,
+ "records_for_plan": 4,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`D`.`d` is not null)",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` is not null)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+select * from t2|
+s i d
+a 1 1
+b 2 2
+c 3 3
+3 100 200
+3 100 200
+3 100 200
+3 100 200
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "prepare stmt from 'call p1(?)'",
+ "steps": [
+ ] /* steps */
+}
+set @param="c";
+execute stmt using @param;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+@trace
+
+{
+ "top_query": "execute stmt using @param",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "after_equality_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+ "after_constant_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+ "after_trivial_conditions_removal": "((`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "table_scan": {
+ "records": 4,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "table": "t2",
+ "table_scan": {
+ "records": 7,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "pulled_out_semijoin_tables": [
+ ] /* pulled_out_semijoin_tables */
+ },
+ {
+ "execution_plan_for_potential_materialization": {
+ "steps": [
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0496,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0496,
+ "records_for_plan": 7,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */
+ } /* execution_plan_for_potential_materialization */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "D",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0068,
+ "records": 4,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0068,
+ "records_for_plan": 4,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "rest_of_plan": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0499,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0567,
+ "records_for_plan": 28,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "FirstMatch",
+ "recompute_best_access_paths": {
+ "cause": "join_buffering_not_possible",
+ "tables": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0499,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ }
+ ] /* tables */
+ } /* recompute_best_access_paths */,
+ "cost": 4.0567,
+ "records": 4,
+ "chosen": true
+ },
+ {
+ "strategy": "MaterializationLookup",
+ "cost": 4.6064,
+ "records": 4,
+ "duplicate_tables_left": false,
+ "chosen": false
+ },
+ {
+ "strategy": "DuplicatesWeedout",
+ "cost": 15.257,
+ "records": 4,
+ "duplicate_tables_left": false,
+ "chosen": false
+ }
+ ] /* semijoin_strategy_choice */,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0496,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0496,
+ "records_for_plan": 7,
+ "semijoin_strategy_choice": [
+ ] /* semijoin_strategy_choice */,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "reconsidering_access_paths_for_semijoin": {
+ "strategy": "FirstMatch",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 8.1982,
+ "records": 7,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */
+ } /* reconsidering_access_paths_for_semijoin */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+ "attached_conditions": [
+ {
+ "table": "D",
+ "attached": "(`test`.`D`.`d` = `f1`())"
+ },
+ {
+ "table": "t2",
+ "attached": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 3,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 3,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<null>",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "<null>"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+ "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+ "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "D.d= (res@1 + 1)",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "D",
+ "more_range_optimizer_trace": {
+ "TODO?": "yes!"
+ } /* more_range_optimizer_trace */
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+execute stmt using @param;
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+@trace=@trace2
+1
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "explain select * from v1 where id="b"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`id` = 'b') and (`test`.`t1`.`id` < 'c'))",
+ "after_equality_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+ "after_constant_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+ "after_trivial_conditions_removal": "multiple equal('b', `test`.`t1`.`id`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`id` = 'b')",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "(`test`.`t1`.`id` = 'b')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop view v1;
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "explain select * from v1 where id="b"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`id` < 'c')",
+ "after_equality_propagation": "(`test`.`t1`.`id` < 'c')",
+ "after_constant_propagation": "(`test`.`t1`.`id` < 'c')",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`id` < 'c')"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 2.0103,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0103,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`id` < 'c')",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "(`test`.`t1`.`id` < 'c')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`v1`.`id` = 'b')",
+ "after_equality_propagation": "multiple equal('b', `v1`.`id`)",
+ "after_constant_propagation": "multiple equal('b', `v1`.`id`)",
+ "after_trivial_conditions_removal": "multiple equal('b', `v1`.`id`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "v1",
+ "table_scan": {
+ "records": 2,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "v1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 10.1,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.1,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`v1`.`id` = 'b')",
+ "attached_conditions": [
+ {
+ "table": "v1",
+ "attached": "(`v1`.`id` = 'b')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop view v1;
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+VARIABLE_NAME VARIABLE_VALUE
+OPTIMIZER_TRACE enabled=on,end_marker=on,one_line=off,skip_plan=off
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID TRACE
+0
+{
+ "top_query": "select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace"",
+ "steps": [
+ {
+ "join_preparation": {
+ "select #": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select #": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_equality_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_constant_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "after_trivial_conditions_removal": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "table": "SESSION_VARIABLES",
+ "table_scan": {
+ "records": 2,
+ "cost": 10
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "SESSION_VARIABLES",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "table scan",
+ "cost": 10.1,
+ "records": 2,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.1,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+ "attached_conditions": [
+ {
+ "table": "SESSION_VARIABLES",
+ "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop table t1,t2;
DROP TABLE C,D;
set optimizer_trace=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-09-05 16:08:07 +0000
+++ b/sql/sql_select.cc 2010-09-08 10:27:44 +0000
@@ -4970,6 +4970,20 @@ make_join_statistics(JOIN *join, TABLE_L
(s->table->pos_in_table_list->embedding && // (3)
s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
{
+#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
+ /*
+ 1) Range optimizer tracing needs to be re-thought
+ 2) if we have a condition like
+ field=stored_function_SELECTing_table(),
+ get_quick_record_count() below will evaluate the function and thus
+ this will add a join_preparation object which does not fit well
+ (has a no-key object whereas the current OOA is an object
+ ("records_estimation").
+ */
+ Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
+ add("yes!", "TODO?");
+ Opt_trace_disable_I_S otd(join->thd->opt_trace, TRUE);
+#endif
ha_rows records;
SQL_SELECT *select;
select= make_select(s->table, found_const_table_map,
@@ -4978,10 +4992,6 @@ make_join_statistics(JOIN *join, TABLE_L
1, &error);
if (!select)
goto error;
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
- add("yes!", "TODO?");
-#endif
records= get_quick_record_count(join->thd, select, s->table,
&s->const_keys, join->row_limit);
s->quick= select->quick;
Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100908102744-onwirlk3x4z11ni9.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (guilhem:3202) | Guilhem Bichot | 8 Sep |
