3350 Tor Didriksen 2011-07-22 [merge]
Merge opt-backporting => opt-team
modified:
mysql-test/suite/opt_trace/include/general2.inc
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
sql/sql_select.cc
3349 Tor Didriksen 2011-07-22 [merge]
NULL merge opt-backporting => opt-team
3348 Tor Didriksen 2011-07-22 [merge]
Automerge opt-backporting => opt-team
3347 Tor Didriksen 2011-07-21 [merge]
empty merge trunk => opt-team
=== modified file 'mysql-test/suite/opt_trace/include/general2.inc'
--- a/mysql-test/suite/opt_trace/include/general2.inc 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/include/general2.inc 2011-07-21 19:58:23 +0000
@@ -398,3 +398,167 @@ table3.col_varchar_10_utf8_key
select * from information_schema.optimizer_trace;
DROP TABLE t1,t2,t3;
+
+--echo
+--echo Tests of tracing of the "eq_ref optimization" of plan search
+--echo
+
+# test for trace point "chosen:true","pruned_by_cost:true" and
+# "added_to_eq_ref_extension:true" (from main.subquery_sj_none_jcl7)
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+
+set @old_opt_switch=@@optimizer_switch;
+# The SET below must not be output, because only servers supporting
+# semijoin will execute it (would make varying output).
+if (`select locate('semijoin', @@optimizer_switch) > 0`)
+{
+--disable_query_log
+ set optimizer_switch="semijoin=off,materialization=off";
+--enable_query_log
+}
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+
+select * from information_schema.optimizer_trace;
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+
+# test for trace point "added_to_eq_ref_extension:false" (from main.derived)
+
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+select * from information_schema.optimizer_trace;
+drop table t1,t2,t3;
+
+# test of multiple nested trace points "added_to_eq_ref_extension:true"
+# (eq_ref optimization finding a sequence of eq_ref-joined tables) (from
+# main.type_blob)
+
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+select * from information_schema.optimizer_trace;
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-07-21 19:58:23 +0000
@@ -2248,3 +2248,1723 @@ table3.col_varchar_10_utf8_key {
] /* steps */
} 0 0
DROP TABLE t1,t2,t3;
+
+Tests of tracing of the "eq_ref optimization" of plan search
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+set @old_opt_switch=@@optimizer_switch;
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select (`test`.`t2`.`a` + `test`.`t3`.`a`) from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true,
+ "evaluating_constant_where_conditions": [
+ ] /* evaluating_constant_where_conditions */
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t0",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t0",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.0171,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0171,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t0",
+ "attached": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t0",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4227,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`))"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "t3"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+497 ID NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from ((`test`.`t1` join `test`.`t2` on((`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))) join `test`.`t3` on((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`))) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`) and (`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "t2_PARID_IDX"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 9,
+ "cost": 5.9198
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "t2_ID_IDX",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "t2_PARID_IDX",
+ "usable": true,
+ "key_parts": [
+ "PARID"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "const_keys_added": {
+ "keys": [
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 8,
+ "cost": 5.7195
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t3_ID_IDX",
+ "records": 0.8,
+ "cost": 0.96,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 8,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 0.96,
+ "records_for_plan": 0.8,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.88,
+ "records_for_plan": 1.6,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 0.9,
+ "cost": 1.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.08,
+ "records_for_plan": 0.9,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from `test`.`t1` join `test`.`t2` join `test`.`t3` where multiple equal(NULL, `test`.`t3`.`ID`, `test`.`t2`.`ID`) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc) `tmp`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "tmp",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3;
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
+test^M
+job^M
+1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
+job^M
+1 1
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select replace(`test`.`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id`,min(`test`.`t3`.`_field_131`) AS `min(t3._field_131)`,min(`test`.`t3`.`_field_135`) AS `min(t3._field_135)`,min(`test`.`t3`.`_field_139`) AS `min(t3._field_139)`,min(`test`.`t3`.`_field_137`) AS `min(t3._field_137)`,min(`test`.`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`test`.`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`test`.`t3`.`_field_145`) AS `min(t3._field_145)`,min(`test`.`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`test`.`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id` from ((((((`test`.`t3` left join `test`.`t4` on((`test`.`t4`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t7` `link_alias_142` on((`test`.`t4`.`seq_1_id` = `test`.`link_alias!
_142`.`t7_id`))) left join `test`.`t6` on((`test`.`t6`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t1` `link_alias_133` on((`test`.`t6`.`seq_1_id` = `test`.`link_alias_133`.`t1_id`))) left join `test`.`t2` on((`test`.`t2`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t5` `link_alias_148` on((`test`.`t2`.`seq_1_id` = `test`.`link_alias_148`.`t5_id`))) where (`test`.`t3`.`t3_id` = 1) group by `test`.`t3`.`t3_id` order by `test`.`link_alias_142`.`_field_166`,`test`.`t3`.`_field_139`,`test`.`link_alias_133`.`_field_72`,`test`.`t3`.`_field_135`,`test`.`link_alias_148`.`_field_156`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t3`.`t3_id` = 1)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "t3_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "field": "t7_id",
+ "equals": "`test`.`t4`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "field": "t1_id",
+ "equals": "`test`.`t6`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "field": "t5_id",
+ "equals": "`test`.`t2`.`seq_1_id`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "const"
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5083
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4435,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7156
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t7_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_166",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.741
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t1_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_72",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_115",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_122",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5137
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t5_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_156",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_157",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_158",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_159",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_160",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_161",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t4",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.2,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 2.4,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 3.6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 4.8,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 7.2,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t4",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t4"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142"
+ },
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-07-21 19:58:23 +0000
@@ -2301,3 +2301,1723 @@ table3.col_varchar_10_utf8_key {
] /* steps */
} 0 0
DROP TABLE t1,t2,t3;
+
+Tests of tracing of the "eq_ref optimization" of plan search
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+set @old_opt_switch=@@optimizer_switch;
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select (`test`.`t2`.`a` + `test`.`t3`.`a`) from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true,
+ "evaluating_constant_where_conditions": [
+ ] /* evaluating_constant_where_conditions */
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t0",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t0",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.0171,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0171,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t0",
+ "attached": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t0",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4227,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`))"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "t3"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+497 ID NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from ((`test`.`t1` join `test`.`t2` on((`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))) join `test`.`t3` on((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`))) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`) and (`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "t2_PARID_IDX"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 9,
+ "cost": 5.9198
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "t2_ID_IDX",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "t2_PARID_IDX",
+ "usable": true,
+ "key_parts": [
+ "PARID"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "const_keys_added": {
+ "keys": [
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 8,
+ "cost": 5.7195
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t3_ID_IDX",
+ "records": 0.8,
+ "cost": 0.96,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 8,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 0.96,
+ "records_for_plan": 0.8,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.88,
+ "records_for_plan": 1.6,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 0.9,
+ "cost": 1.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.08,
+ "records_for_plan": 0.9,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from `test`.`t1` join `test`.`t2` join `test`.`t3` where multiple equal(NULL, `test`.`t3`.`ID`, `test`.`t2`.`ID`) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc) `tmp`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "tmp",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3;
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
+test^M
+job^M
+1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
+job^M
+1 1
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select replace(`test`.`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id`,min(`test`.`t3`.`_field_131`) AS `min(t3._field_131)`,min(`test`.`t3`.`_field_135`) AS `min(t3._field_135)`,min(`test`.`t3`.`_field_139`) AS `min(t3._field_139)`,min(`test`.`t3`.`_field_137`) AS `min(t3._field_137)`,min(`test`.`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`test`.`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`test`.`t3`.`_field_145`) AS `min(t3._field_145)`,min(`test`.`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`test`.`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id` from ((((((`test`.`t3` left join `test`.`t4` on((`test`.`t4`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t7` `link_alias_142` on((`test`.`t4`.`seq_1_id` = `test`.`link_alias!
_142`.`t7_id`))) left join `test`.`t6` on((`test`.`t6`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t1` `link_alias_133` on((`test`.`t6`.`seq_1_id` = `test`.`link_alias_133`.`t1_id`))) left join `test`.`t2` on((`test`.`t2`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t5` `link_alias_148` on((`test`.`t2`.`seq_1_id` = `test`.`link_alias_148`.`t5_id`))) where (`test`.`t3`.`t3_id` = 1) group by `test`.`t3`.`t3_id` order by `test`.`link_alias_142`.`_field_166`,`test`.`t3`.`_field_139`,`test`.`link_alias_133`.`_field_72`,`test`.`t3`.`_field_135`,`test`.`link_alias_148`.`_field_156`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t3`.`t3_id` = 1)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "t3_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "field": "t7_id",
+ "equals": "`test`.`t4`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "field": "t1_id",
+ "equals": "`test`.`t6`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "field": "t5_id",
+ "equals": "`test`.`t2`.`seq_1_id`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "const"
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5083
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4435,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7156
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t7_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_166",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.741
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t1_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_72",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_115",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_122",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5137
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t5_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_156",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_157",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_158",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_159",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_160",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_161",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t4",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.2,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 2.4,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 3.6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 4.8,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 7.2,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t4",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t4"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142"
+ },
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-07-21 07:21:32 +0000
+++ b/sql/sql_select.cc 2011-07-22 07:58:38 +0000
@@ -8967,6 +8967,7 @@ bool Optimize_table_order::best_extensio
if (eq_ref_extended == (table_map)0)
{
/* Try an EQ_REF-joined expansion of the partial plan */
+ Opt_trace_array trace_rest(trace, "rest_of_plan");
eq_ref_extended= real_table_bit |
eq_ref_extension_by_limited_search(
remaining_tables & ~real_table_bit,
@@ -8989,6 +8990,7 @@ bool Optimize_table_order::best_extensio
read_time,
current_read_time,
"pruned_by_eq_ref_heuristic"););
+ trace_one_table.add("pruned_by_eq_ref_heuristic", true);
backout_nj_sj_state(remaining_tables, s);
continue;
}
@@ -9142,151 +9144,164 @@ table_map Optimize_table_order::eq_ref_e
{
DBUG_ENTER("Optimize_table_order::eq_ref_extension_by_limited_search");
+ if (remaining_tables == 0)
+ DBUG_RETURN(0);
+
const bool has_sj=
!(join->select_lex->sj_nests.is_empty() || join->emb_sjm_nest);
/*
- The brackeded section below add 'eq_ref' joinable tables to
- the QEP in the order they are found in the 'remaining_tables' set.
+ The section below adds 'eq_ref' joinable tables to the QEP in the order
+ they are found in the 'remaining_tables' set.
See above description for why we can add these without greedy
cost analysis.
*/
- if (remaining_tables)
+ Opt_trace_context * const trace= &thd->opt_trace;
+ table_map eq_ref_ext(0);
+ JOIN_TAB *s;
+ JOIN_TAB *saved_refs[MAX_TABLES];
+ // Save 'best_ref[]' as we has to restore before return.
+ memcpy(saved_refs, join->best_ref + idx,
+ sizeof(JOIN_TAB*) * (join->tables-idx));
+
+ for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
- table_map eq_ref_ext(0);
- JOIN_TAB *s;
- JOIN_TAB *saved_refs[MAX_TABLES];
- // Save 'best_ref[]' as we has to restore before return.
- memcpy(saved_refs, join->best_ref + idx,
- sizeof(JOIN_TAB*) * (join->tables-idx));
+ const table_map real_table_bit= s->table->map;
- for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
- {
- const table_map real_table_bit= s->table->map;
+ /*
+ Don't move swap inside conditional code: All items
+ should be swapped to maintain '#rows' ordered tables.
+ This is critical for early pruning of bad plans.
+ */
+ swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
- /*
- Don't move swap inside conditional code: All items
- should be swapped to maintain '#rows' ordered tables.
- This is critical for early pruning of bad plans.
- */
- swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
+ /*
+ Consider table for 'eq_ref' heuristic if:
+ 1) It might use a keyref for best_access_path
+ 2) and, Table remains to be handled.
+ 3) and, It is independent of those not yet in partial plan.
+ 4) and, It passed the interleaving check.
+ */
+ if (s->keyuse && // 1)
+ (remaining_tables & real_table_bit) && // 2)
+ !(remaining_tables & s->dependent) && // 3)
+ (!idx || !check_interleaving_with_nj(s))) // 4)
+ {
+ Opt_trace_object trace_one_table(trace);
+ trace_one_table.add_utf8_table(s->table);
+ POSITION *const position= join->positions + idx;
+ POSITION loose_scan_pos;
+
+ /* Find the best access method from 's' to the current partial plan */
+ best_access_path(join, s, excluded_tables | remaining_tables,
+ idx, FALSE, record_count,
+ position, &loose_scan_pos);
/*
- Consider table for 'eq_ref' heuristic if:
- 1) It might use a keyref for best_access_path
- 2) and, Table remains to be handled.
- 3) and, It is independent of those not yet in partial plan.
- 4) and, It passed the interleaving check.
- */
- if (s->keyuse && // 1)
- (remaining_tables & real_table_bit) && // 2)
- !(remaining_tables & s->dependent) && // 3)
- (!idx || !check_interleaving_with_nj(s))) // 4)
- {
- POSITION *const position= join->positions + idx;
- POSITION loose_scan_pos;
-
- /* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, excluded_tables | remaining_tables,
- idx, FALSE, record_count,
- position, &loose_scan_pos);
-
- /* EQ_REF prune logic is based on that all joins
- in the ref_extension has the same #rows and cost.
- -> The total cost of the QEP is independent of the order
+ EQ_REF prune logic is based on that all joins
+ in the ref_extension has the same #rows and cost.
+ -> The total cost of the QEP is independent of the order
of joins within this 'ref_extension'.
- Expand QEP with all 'identical' REFs in
+ Expand QEP with all 'identical' REFs in
'join->positions' order.
- */
- if (position->key &&
- position->read_time == (position-1)->read_time &&
- position->records_read == (position-1)->records_read)
- {
- double current_record_count, current_read_time;
-
- /* Add the cost of extending the plan with 's' */
- current_record_count= record_count * position->records_read;
- current_read_time= read_time
- + position->read_time
- + current_record_count * ROW_EVALUATE_COST;
+ */
+ const bool added_to_eq_ref_extension=
+ position->key &&
+ position->read_time == (position-1)->read_time &&
+ position->records_read == (position-1)->records_read;
+ trace_one_table.add("added_to_eq_ref_extension",
+ added_to_eq_ref_extension);
+ if (added_to_eq_ref_extension)
+ {
+ double current_record_count, current_read_time;
+
+ /* Add the cost of extending the plan with 's' */
+ current_record_count= record_count * position->records_read;
+ current_read_time= read_time
+ + position->read_time
+ + current_record_count * ROW_EVALUATE_COST;
- if (has_sj)
- {
- /*
- Even if there are no semijoins, advance_sj_state() has a
- significant cost (takes 9% of time in a 20-table plan search),
- hence the if() above, which is also more efficient than the
- same if() inside advance_sj_state() would be.
- */
- advance_sj_state(remaining_tables, s, idx,
- ¤t_record_count, ¤t_read_time,
- &loose_scan_pos);
- }
- else
- position->sj_strategy= SJ_OPT_NONE;
+ trace_one_table.add("cost_for_plan", current_read_time).
+ add("records_for_plan", current_record_count);
- /* Expand only partial plans with lower cost than the best QEP so far */
- if (current_read_time >= join->best_read)
- {
- DBUG_EXECUTE("opt", print_plan(join, idx+1,
- current_record_count,
- read_time,
- current_read_time,
- "prune_by_cost"););
- backout_nj_sj_state(remaining_tables, s);
- continue;
- }
-
- eq_ref_ext= real_table_bit;
- if ((current_search_depth > 1) && (remaining_tables & ~real_table_bit))
- {
- DBUG_EXECUTE("opt", print_plan(join, idx + 1,
- current_record_count,
- read_time,
- current_read_time,
- "EQ_REF_extension"););
+ if (has_sj)
+ {
+ /*
+ Even if there are no semijoins, advance_sj_state() has a
+ significant cost (takes 9% of time in a 20-table plan search),
+ hence the if() above, which is also more efficient than the
+ same if() inside advance_sj_state() would be.
+ */
+ advance_sj_state(remaining_tables, s, idx,
+ ¤t_record_count, ¤t_read_time,
+ &loose_scan_pos);
+ }
+ else
+ position->sj_strategy= SJ_OPT_NONE;
- /* Recursively EQ_REF-extend the current partial plan */
- eq_ref_ext|=
- eq_ref_extension_by_limited_search(remaining_tables & ~real_table_bit,
- idx + 1,
- current_record_count,
- current_read_time,
- current_search_depth - 1);
- }
- else
- {
- plan_is_complete(idx, current_record_count, current_read_time);
- // TODO(didrik): add trace here.
- // trace_one_table.add("chosen", true);
- }
+ // Expand only partial plans with lower cost than the best QEP so far
+ if (current_read_time >= join->best_read)
+ {
+ DBUG_EXECUTE("opt", print_plan(join, idx+1,
+ current_record_count,
+ read_time,
+ current_read_time,
+ "prune_by_cost"););
+ trace_one_table.add("pruned_by_cost", true);
backout_nj_sj_state(remaining_tables, s);
- memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
- DBUG_RETURN(eq_ref_ext);
- } // if ('is eq_ref')
+ continue;
+ }
+
+ eq_ref_ext= real_table_bit;
+ if ((current_search_depth > 1) &&
+ (remaining_tables & ~real_table_bit))
+ {
+ DBUG_EXECUTE("opt", print_plan(join, idx + 1,
+ current_record_count,
+ read_time,
+ current_read_time,
+ "EQ_REF_extension"););
+ /* Recursively EQ_REF-extend the current partial plan */
+ Opt_trace_array trace_rest(trace, "rest_of_plan");
+ eq_ref_ext|=
+ eq_ref_extension_by_limited_search(remaining_tables &
+ ~real_table_bit,
+ idx + 1,
+ current_record_count,
+ current_read_time,
+ current_search_depth - 1);
+ }
+ else
+ {
+ plan_is_complete(idx, current_record_count, current_read_time);
+ trace_one_table.add("chosen", true);
+ }
backout_nj_sj_state(remaining_tables, s);
- } // check_interleaving_with_nj()
- } // for (...)
+ memcpy(join->best_ref + idx, saved_refs,
+ sizeof(JOIN_TAB*) * (join->tables - idx));
+ DBUG_RETURN(eq_ref_ext);
+ } // if (added_to_eq_ref_extension)
- memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
- /*
- 'eq_ref' heuristc didn't find a table to be appended to
- the query plan. We need to use the greedy search
- for finding the next table to be added.
- */
- DBUG_ASSERT(!eq_ref_ext);
- if (best_extension_by_limited_search(remaining_tables,
- idx,
- record_count,
- read_time,
- current_search_depth))
- DBUG_RETURN(~(table_map)0);
+ backout_nj_sj_state(remaining_tables, s);
+ } // if (... !check_interleaving_with_nj() ...)
+ } // for (JOIN_TAB **pos= ...)
- DBUG_RETURN(eq_ref_ext);
- }
+ memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
+ /*
+ 'eq_ref' heuristc didn't find a table to be appended to
+ the query plan. We need to use the greedy search
+ for finding the next table to be added.
+ */
+ DBUG_ASSERT(!eq_ref_ext);
+ if (best_extension_by_limited_search(remaining_tables,
+ idx,
+ record_count,
+ read_time,
+ current_search_depth))
+ DBUG_RETURN(~(table_map)0);
- DBUG_RETURN(0);
+ DBUG_RETURN(eq_ref_ext);
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (tor.didriksen:3347 to 3350) | Tor Didriksen | 22 Jul |
