#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:jorgen.loland@stripped
3234 Jorgen Loland 2010-11-22
WL#5594 - Add optimizer traces to the range optimizer
Minor changes as requested by reviewer.
@ mysql-test/t/optimizer_trace_range.test
Added test for group quick select without grouping attribute
@ sql/sql_delete.cc
Indentation
@ sql/sql_update.cc
Indentation
modified:
mysql-test/r/optimizer_trace_charset.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/r/optimizer_trace_ps_prot.result
mysql-test/r/optimizer_trace_range.result
mysql-test/t/optimizer_trace_range.test
sql/opt_range.cc
sql/sql_delete.cc
sql/sql_select.cc
sql/sql_update.cc
=== modified file 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result 2010-11-22 09:59:17 +0000
@@ -38,7 +38,7 @@ explain extended select '�� "records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -50,8 +50,8 @@ explain extended select '��� from t1
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -87,7 +87,7 @@ explain extended select 'ÁÂÃÄÅ' fro
"table": "t1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -99,8 +99,8 @@ explain extended select 'ÁÂÃÄÅ' fro
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -151,7 +151,7 @@ select * from v1 where v1.col = '���
"table": "t1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -163,8 +163,8 @@ select * from v1 where v1.col = '���
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -203,7 +203,7 @@ select * from v1 where v1.col = '���
"table": "v1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -215,8 +215,8 @@ select * from v1 where v1.col = '���
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -351,13 +351,13 @@ select * from t1 where c < '��� {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "index"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-11-22 09:59:17 +0000
@@ -46,7 +46,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
]
},
@@ -58,8 +58,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
}
},
{
- "refine_plan": {
- }
+ "refine_plan": [
+ ]
}
]
}
@@ -134,18 +134,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-50.2096
+50.3069
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": {}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-5.9947
+6.0185
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -186,7 +186,7 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -198,8 +198,8 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -309,7 +309,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -321,8 +321,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -450,7 +450,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -462,8 +462,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -658,13 +658,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "",
"table": "",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -776,7 +776,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -894,13 +894,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "",
"table": "",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -918,7 +918,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-9316
+9257
set optimizer_trace_max_mem_size=8400;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -927,7 +927,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-1043
+972
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -935,7 +935,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8273 1 1
+8285 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@query2) + length(@trace2)
@@ -1031,7 +1031,7 @@ explain SELECT c FROM t5 where c+1 in (s
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
@@ -1211,12 +1211,12 @@ explain SELECT c FROM t5 where c+1 in (s
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1292,7 +1292,7 @@ explain SELECT c FROM t5 where c+1 in (s
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -1304,8 +1304,8 @@ explain SELECT c FROM t5 where c+1 in (s
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
},
{
"transformation": {
@@ -1435,12 +1435,12 @@ explain SELECT c FROM t5 where c+1 in (s
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1552,13 +1552,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1616,13 +1616,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1730,13 +1730,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1794,13 +1794,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1925,13 +1925,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2002,13 +2002,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2132,13 +2132,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2209,13 +2209,13 @@ explain extended select * from t1 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2369,18 +2369,18 @@ explain select * from t1,t2 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2456,18 +2456,18 @@ explain select * from t1,t2 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2634,13 +2634,13 @@ where a1 in (select b1 from t2_16 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1_16",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2711,13 +2711,13 @@ where a1 in (select b1 from t2_16 where
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2_16",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2901,18 +2901,18 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2983,13 +2983,13 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3086,13 +3086,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3163,13 +3163,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3234,13 +3234,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "",
"table": "",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3391,13 +3391,13 @@ concat(c1,'y') IN
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
},
{
"transformation": {
@@ -3471,13 +3471,13 @@ concat(c1,'y') IN
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3535,13 +3535,13 @@ concat(c1,'y') IN
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3586,14 +3586,14 @@ select * from t1,t2 {
"table": "t1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
"table": "t2",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
}
] /* records_estimation */
},
@@ -3605,8 +3605,8 @@ select * from t1,t2 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- } /* refine_plan */
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3720,21 +3720,21 @@ trace
"table": "t3",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
"table": "t1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
"table": "t2",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
@@ -3851,21 +3851,21 @@ trace
"table": "t3",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
"table": "t1",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
"table": "t2",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
@@ -4034,18 +4034,18 @@ select * from t1 left join t2 on t2.a=50
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4170,13 +4170,13 @@ select * from t1 where (t1.a,t1.b) not i
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4255,13 +4255,13 @@ select * from t1 where (t1.a,t1.b) not i
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4412,18 +4412,18 @@ trace
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4734,13 +4734,13 @@ insert into t6 select * from t6 where d>
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6",
"scan_type": "index"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4789,7 +4789,7 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
@@ -4882,13 +4882,13 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4937,7 +4937,7 @@ delete t6 from t5, t6 where d>7000 {
"table": "t5",
"records": 1,
"cost": 1,
- "table_type": "system_table"
+ "table_type": "system"
},
{
"database": "test",
@@ -5024,13 +5024,13 @@ delete t6 from t5, t6 where d>7000 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5253,13 +5253,13 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5338,13 +5338,13 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5420,13 +5420,13 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5441,30 +5441,15 @@ select * from t6 where d in (select f1()
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select * from t6 where d in (select f1() from t2 where s="c") {
+select sum(data) into ret from t1 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
},
{
"join_preparation": {
"select#": 1,
"steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- }
] /* steps */
} /* join_preparation */
},
@@ -5473,319 +5458,61 @@ select * from t6 where d in (select f1()
"select#": 1,
"steps": [
{
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- } /* transformation */
- },
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
- "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- {
- "condition": "t6.d= `f1`()",
- "null_rejecting": false
- }
- ] /* ref-optimizer-key-uses */
- },
- {
"constant_tables": [
] /* constant_tables */,
"records_estimation": [
{
"database": "test",
- "table": "t6",
- "range_analysis": {
- "table_scan": {
- "records": 4,
- "cost": 4.9068
- } /* table_scan */,
- "potential_range_indices": [
- {
- "index": "d",
- "usable": true,
- "key_parts": [
- "d"
- ] /* key_parts */
- }
- ] /* potential_range_indices */,
- "best_covering_index_scan": {
- "index": "d",
- "cost": 1.8698,
- "chosen": true
- } /* best_covering_index_scan */,
- "group_index_range": {
- "chosen": false,
- "cause": "not_single_table"
- } /* group_index_range */,
- "analyzing_range_alternatives": {
- "range_scan_alternatives": [
- {
- "index": "d",
- "ranges": [
- "3 <= d <= 3"
- ] /* ranges */,
- "index_only": true,
- "records": 1,
- "cost": 2.21,
- "rowid_ordered": true,
- "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": "t2",
+ "table": "t1",
"table_scan": {
- "records": 3,
+ "records": 2,
"cost": 2
} /* table_scan */
}
] /* records_estimation */
},
{
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
- },
- {
- "execution_plan_for_potential_materialization": {
- "steps": [
- {
- "considered_execution_plans": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0212,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0212,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* considered_execution_plans */
- }
- ] /* steps */
- } /* execution_plan_for_potential_materialization */
- },
- {
"considered_execution_plans": [
{
"database": "test",
- "table": "t2",
+ "table": "t1",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"using_join_cache": true,
- "records": 3,
- "cost": 2.0212,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0212,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "database": "test",
- "table": "t6",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "d",
- "records": 1,
- "cost": 3,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 1,
- "cost": 2.6076,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 4.6289,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 5.2289,
- "records": 1,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "database": "test",
- "table": "t6",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "d",
- "records": 1,
- "cost": 1,
+ "records": 2,
+ "cost": 2.0154,
"chosen": true
- },
- {
- "access_type": "scan",
- "cost": 2,
- "records": 4,
- "cause": "cost",
- "chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 1,
- "records_for_plan": 1,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 3.0213,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "recompute_best_access_paths": {
- "cause": "join_buffering_not_possible",
- "tables": [
- {
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "using_join_cache": true,
- "records": 3,
- "cost": 2.0213,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recompute_best_access_paths */,
- "cost": 3.0213,
- "records": 1,
- "chosen": true
- },
- {
- "strategy": "MaterializationLookup",
- "cost": 3.2212,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 3.6213,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
+ "cost_for_plan": 2.0154,
+ "records_for_plan": 2,
+ "chosen": true
}
] /* considered_execution_plans */
},
{
- "reconsidering_access_paths_for_semijoin": {
- "strategy": "FirstMatch",
- "database": "test",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "scan",
- "records": 3,
- "cost": 2.0212,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- } /* reconsidering_access_paths_for_semijoin */
- },
- {
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "original_condition": null,
"attached_conditions": [
{
"database": "test",
- "table": "t6",
- "attached": "(`test`.`t6`.`d` = `f1`())"
- },
- {
- "database": "test",
- "table": "t2",
- "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ "table": "t1",
+ "attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
- "database": "test",
- "table": "t6"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
- "table": "t2",
+ "table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5881,13 +5608,13 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6079,13 +5806,13 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6520,17 +6247,17 @@ select d into res from t6 where d in (se
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t6"
- } /* refine_plan_for_table */,
- "refine_plan_for_table": {
+ },
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6626,13 +6353,115 @@ select sum(data) into ret from t1 {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual"
+ }
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
+select sum(data) into ret from t1 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0154,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0154,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6743,13 +6572,13 @@ explain select * from v1 where id="b" {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6896,13 +6725,13 @@ explain select * from v1 where id="b" {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -6980,13 +6809,13 @@ explain select * from v1 where id="b" {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "",
"table": "v1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -7079,13 +6908,13 @@ VARIABLE_NAME="optimizer_trace" {
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "information_schema",
"table": "session_variables",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -7191,13 +7020,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "",
"table": "",
"scan_type": "table"
}
- }
+ ]
}
]
}
=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result 2010-11-06 09:56:51 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result 2010-11-22 09:59:17 +0000
@@ -1,5 +1,6 @@
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+set optimizer_trace_max_mem_size=1048576;
set @@session.optimizer_trace="enabled=on";
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
@@ -40,6 +41,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
"t5"
],
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
]
},
{
@@ -48,6 +56,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
"attached_conditions": [
]
}
+ },
+ {
+ "refine_plan": [
+ ]
}
]
}
@@ -78,10 +90,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
],
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- }
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ },
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ]
+ }
+ ],
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ },
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ },
+ "impossible_range": true
+ },
+ "records": 0,
+ "cause": "impossible_where_condition"
}
]
}
@@ -96,18 +134,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-45.1596
+50.3069
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": []},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"table": "t6","more_range_optimizer_trace": {"TODO?": "yes!"}}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": []}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.3688
+6.0185
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -143,6 +181,13 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
"t5"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
] /* records_estimation */
},
{
@@ -151,6 +196,10 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -181,10 +230,36 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -229,6 +304,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"t5"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
] /* records_estimation */
},
{
@@ -237,6 +319,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -267,10 +353,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -333,6 +445,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"t5"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
] /* records_estimation */
},
{
@@ -341,6 +460,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -371,10 +494,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -410,10 +559,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -439,6 +614,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 0,
@@ -450,13 +626,15 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
{
"considered_execution_plans": [
{
+ "database": "",
"table": "",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 0,
+ "cost": 10,
"chosen": true
}
] /* considered_access_paths */
@@ -472,11 +650,21 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -567,6 +755,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"t5"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
] /* records_estimation */
},
{
@@ -639,6 +834,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 0,
@@ -650,13 +846,15 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
{
"considered_execution_plans": [
{
+ "database": "",
"table": "",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 0,
+ "cost": 10,
"chosen": true
}
] /* considered_access_paths */
@@ -672,11 +870,21 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -694,16 +902,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-8173
+8802
set optimizer_trace_max_mem_size=8400;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
-0
+1
SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-0
+517
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -711,12 +919,12 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8173 1 1
+8285 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100)
-0
+1
select instr(@trace, @trace2) = 1;
instr(@trace, @trace2) = 1
1
@@ -734,7 +942,7 @@ select 1;
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
381 0
-set optimizer_trace_max_mem_size=default;
+set optimizer_trace_max_mem_size=1048576;
explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t5 system NULL NULL NULL NULL 1
@@ -803,10 +1011,59 @@ explain SELECT c FROM t5 where c+1 in (s
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
}
] /* records_estimation */
},
@@ -820,20 +1077,22 @@ explain SELECT c FROM t5 where c+1 in (s
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -870,20 +1129,22 @@ explain SELECT c FROM t5 where c+1 in (s
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -926,11 +1187,20 @@ explain SELECT c FROM t5 where c+1 in (s
"original_condition": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1001,6 +1271,13 @@ explain SELECT c FROM t5 where c+1 in (s
"t5"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
] /* records_estimation */
},
{
@@ -1011,6 +1288,10 @@ explain SELECT c FROM t5 where c+1 in (s
} /* attaching_conditions_to_tables */
},
{
+ "refine_plan": [
+ ] /* refine_plan */
+ },
+ {
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
@@ -1047,30 +1328,74 @@ explain SELECT c FROM t5 where c+1 in (s
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -1086,11 +1411,20 @@ explain SELECT c FROM t5 where c+1 in (s
"original_condition": "isnull(`test`.`t6`.`d`)",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "isnull(`test`.`t6`.`d`)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1158,6 +1492,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1169,13 +1504,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1191,11 +1528,21 @@ explain extended select * from t1 where
"original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1209,6 +1556,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1220,13 +1568,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1242,11 +1592,21 @@ explain extended select * from t1 where
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1310,6 +1670,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1321,13 +1682,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1343,11 +1706,21 @@ explain extended select * from t1 where
"original_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1361,6 +1734,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1372,13 +1746,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1394,11 +1770,21 @@ explain extended select * from t1 where
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1479,6 +1865,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1490,13 +1877,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1512,11 +1901,21 @@ explain extended select * from t1 where
"original_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1543,6 +1942,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1554,13 +1954,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1576,11 +1978,21 @@ explain extended select * from t1 where
"original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1660,6 +2072,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1671,13 +2084,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1693,11 +2108,21 @@ explain extended select * from t1 where
"original_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1724,6 +2149,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1735,13 +2161,15 @@ explain extended select * from t1 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -1757,11 +2185,21 @@ explain extended select * from t1 where
"original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1802,6 +2240,7 @@ explain select * from t1,t2 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -1809,6 +2248,7 @@ explain select * from t1,t2 {
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -1820,13 +2260,15 @@ explain select * from t1,t2 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0034,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0034,
"chosen": true
}
] /* considered_access_paths */
@@ -1835,13 +2277,15 @@ explain select * from t1,t2 {
"records_for_plan": 2,
"rest_of_plan": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0053,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0053,
"chosen": true
}
] /* considered_access_paths */
@@ -1853,13 +2297,15 @@ explain select * from t1,t2 {
] /* rest_of_plan */
},
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -1868,13 +2314,15 @@ explain select * from t1,t2 {
"records_for_plan": 3,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0036,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0036,
"chosen": true
}
] /* considered_access_paths */
@@ -1892,15 +2340,31 @@ explain select * from t1,t2 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
},
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1909,7 +2373,7 @@ explain select * from t1,t2 {
} 0 0
select @@optimizer_trace_features;
@@optimizer_trace_features
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on
set @@optimizer_trace_features="greedy_search=off";
explain select * from t1,t2;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1938,6 +2402,7 @@ explain select * from t1,t2 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -1945,6 +2410,7 @@ explain select * from t1,t2 {
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -1961,15 +2427,31 @@ explain select * from t1,t2 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
},
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2092,6 +2574,7 @@ where a1 in (select b1 from t2_16 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1_16",
"table_scan": {
"records": 3,
@@ -2103,13 +2586,15 @@ where a1 in (select b1 from t2_16 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1_16",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0293,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0293,
"chosen": true
}
] /* considered_access_paths */
@@ -2125,11 +2610,21 @@ where a1 in (select b1 from t2_16 where
"original_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1_16",
"attached": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1_16",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2156,6 +2651,7 @@ where a1 in (select b1 from t2_16 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2_16",
"table_scan": {
"records": 3,
@@ -2167,13 +2663,15 @@ where a1 in (select b1 from t2_16 where
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2_16",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0293,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0293,
"chosen": true
}
] /* considered_access_paths */
@@ -2189,11 +2687,21 @@ where a1 in (select b1 from t2_16 where
"original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
"attached_conditions": [
{
+ "database": "test",
"table": "t2_16",
"attached": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2_16",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2283,6 +2791,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2290,6 +2799,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2301,13 +2811,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2316,13 +2828,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
"records_for_plan": 3,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0054,
"chosen": true
}
] /* considered_access_paths */
@@ -2334,13 +2848,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* rest_of_plan */
},
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2356,15 +2872,31 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
"original_condition": "((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`))))"
},
{
+ "database": "test",
"table": "t2",
"attached": "(`test`.`t2`.`c2` = `test`.`t1`.`c1`)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2391,6 +2923,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2402,13 +2935,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2424,11 +2959,21 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
"original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))",
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2481,6 +3026,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2492,13 +3038,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2514,11 +3062,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"original_condition": "(`test`.`t1`.`c1` = 5)",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(`test`.`t1`.`c1` = 5)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2545,6 +3103,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2556,13 +3115,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2578,11 +3139,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"original_condition": "(`test`.`t2`.`c2` = 5)",
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": "(`test`.`t2`.`c2` = 5)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2603,6 +3174,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 1,
@@ -2614,13 +3186,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
{
"considered_execution_plans": [
{
+ "database": "",
"table": "",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.05,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 1,
+ "cost": 10.05,
"chosen": true
}
] /* considered_access_paths */
@@ -2636,11 +3210,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2747,6 +3331,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2758,13 +3343,15 @@ concat(c1,'y') IN
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2780,6 +3367,7 @@ concat(c1,'y') IN
"original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
}
@@ -2787,6 +3375,15 @@ concat(c1,'y') IN
} /* attaching_conditions_to_tables */
},
{
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ },
+ {
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
@@ -2814,6 +3411,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2825,13 +3423,15 @@ concat(c1,'y') IN
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2847,11 +3447,21 @@ concat(c1,'y') IN
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2865,6 +3475,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2876,13 +3487,15 @@ concat(c1,'y') IN
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0051,
"chosen": true
}
] /* considered_access_paths */
@@ -2898,11 +3511,21 @@ concat(c1,'y') IN
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2942,14 +3565,32 @@ select * from t1,t2 {
"t2"
] /* constant_tables */,
"records_estimation": [
- ] /* records_estimation */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
"attached_conditions": [
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3059,6 +3700,28 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
"table": "t4",
"table_scan": {
"records": 2,
@@ -3070,14 +3733,17 @@ trace
{
"pulled_out_semijoin_tables": [
{
+ "database": "test",
"table": "t2",
"constant": true
},
{
+ "database": "test",
"table": "t1",
"constant": true
},
{
+ "database": "test",
"table": "t4",
"functionally_dependent": true
}
@@ -3086,20 +3752,22 @@ trace
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t4",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "PRIMARY",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -3163,6 +3831,28 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
"table": "t4",
"table_scan": {
"records": 2,
@@ -3174,20 +3864,22 @@ trace
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t4",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "PRIMARY",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -3251,6 +3943,7 @@ select * from t1 left join t2 on t2.a=50
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3258,6 +3951,7 @@ select * from t1 left join t2 on t2.a=50
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3269,13 +3963,15 @@ select * from t1 left join t2 on t2.a=50
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0034,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0034,
"chosen": true
}
] /* considered_access_paths */
@@ -3284,13 +3980,14 @@ select * from t1 left join t2 on t2.a=50
"records_for_plan": 2,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 4.0068,
+ "access_type": "scan",
"records": 2,
+ "cost": 4.0068,
"chosen": true
}
] /* considered_access_paths */
@@ -3308,15 +4005,31 @@ select * from t1 left join t2 on t2.a=50
"original_condition": "isnull(`test`.`t2`.`a`)",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
},
{
+ "database": "test",
"table": "t2",
"attached": "(trigcond_if(found_match(t2), isnull(`test`.`t2`.`a`), true) and trigcond_if(is_not_null_compl(t2), (`test`.`t2`.`a` = 500), true))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3389,6 +4102,7 @@ select * from t1 where (t1.a,t1.b) not i
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3400,13 +4114,15 @@ select * from t1 where (t1.a,t1.b) not i
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0044,
"chosen": true
}
] /* considered_access_paths */
@@ -3422,11 +4138,21 @@ select * from t1 where (t1.a,t1.b) not i
"original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3461,6 +4187,7 @@ select * from t1 where (t1.a,t1.b) not i
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3472,13 +4199,15 @@ select * from t1 where (t1.a,t1.b) not i
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0044,
"chosen": true
}
] /* considered_access_paths */
@@ -3494,11 +4223,21 @@ select * from t1 where (t1.a,t1.b) not i
"original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3554,6 +4293,7 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3561,6 +4301,7 @@ trace
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3572,14 +4313,17 @@ trace
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
- "chosen": true
+ "cost": 2.0044,
+ "chosen": true,
+ "use_temp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -3587,13 +4331,15 @@ trace
"records_for_plan": 2,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0045,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0045,
"chosen": true
}
] /* considered_access_paths */
@@ -3605,13 +4351,15 @@ trace
] /* rest_of_plan */
},
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0044,
"chosen": true
}
] /* considered_access_paths */
@@ -3627,15 +4375,31 @@ trace
"original_condition": "(`test`.`t2`.`c` > -(1))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
},
{
+ "database": "test",
"table": "t2",
"attached": "(`test`.`t2`.`c` > -(1))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3696,6 +4460,61 @@ update t6 set d=5 where d is NULL {
"steps": [
{
"expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where isnull(`d`)"
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no_join"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "records": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -3706,6 +4525,57 @@ delete from t6 where d=5 {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`d` = 5)"
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "no_join"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "5 <= d <= 5"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "records": 1,
+ "ranges": [
+ "5 <= d <= 5"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -3756,23 +4626,67 @@ insert into t6 select * from t6 where d>
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7051
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "7 < d"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 2,
+ "cost": 3.41,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.2051,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.2051,
"chosen": true
}
] /* considered_access_paths */
@@ -3788,11 +4702,21 @@ insert into t6 select * from t6 where d>
"original_condition": "(`test`.`t6`.`d` > 7)",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "(`test`.`t6`.`d` > 7)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "scan_type": "index"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3837,23 +4761,80 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "records": 1,
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.21,
+ "access_type": "range",
"records": 1,
+ "cost": 2.21,
"chosen": true
}
] /* considered_access_paths */
@@ -3869,11 +4850,21 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
"original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -3918,23 +4909,74 @@ delete t6 from t5, t6 where d>7000 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t5",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "7000 < d"
+ ] /* 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 */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.6068,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 1,
+ "cost": 2.6068,
"chosen": true
}
] /* considered_access_paths */
@@ -3950,11 +4992,21 @@ delete t6 from t5, t6 where d>7000 {
"original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "(`test`.`t6`.`d` > 7000)"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4133,6 +5185,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4144,13 +5197,15 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -4166,11 +5221,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4205,6 +5270,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4216,13 +5282,15 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -4238,11 +5306,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4274,6 +5352,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4285,13 +5364,15 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -4307,11 +5388,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4326,30 +5417,15 @@ select * from t6 where d in (select f1()
d
select * from information_schema.OPTIMIZER_TRACE|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-select * from t6 where d in (select f1() from t2 where s="c") {
+select sum(data) into ret from t1 {
"steps": [
{
- "expanded_query": "/* select#1 */ select `test`.`t6`.`d` AS `d` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+ "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
},
{
"join_preparation": {
"select#": 1,
"steps": [
- {
- "join_preparation": {
- "select#": 2,
- "steps": [
- {
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- } /* transformation */
- }
- ] /* steps */
- } /* join_preparation */
- }
] /* steps */
} /* join_preparation */
},
@@ -4358,248 +5434,61 @@ select * from t6 where d in (select f1()
"select#": 1,
"steps": [
{
- "transformation": {
- "select#": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- } /* transformation */
- },
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
- "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
- "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
- "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
- } /* condition_processing */
- },
- {
- "ref-optimizer-key-uses": [
- {
- "condition": "t6.d= `f1`()",
- "null_rejecting": false
- }
- ] /* ref-optimizer-key-uses */
- },
- {
"constant_tables": [
] /* constant_tables */,
"records_estimation": [
{
- "table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
- },
- {
- "table": "t2",
+ "database": "test",
+ "table": "t1",
"table_scan": {
- "records": 3,
+ "records": 2,
"cost": 2
} /* table_scan */
}
] /* records_estimation */
},
{
- "pulled_out_semijoin_tables": [
- ] /* pulled_out_semijoin_tables */
- },
- {
- "execution_plan_for_potential_materialization": {
- "steps": [
- {
- "considered_execution_plans": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0212,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* considered_execution_plans */
- }
- ] /* steps */
- } /* execution_plan_for_potential_materialization */
- },
- {
"considered_execution_plans": [
{
- "table": "t2",
+ "database": "test",
+ "table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.0212,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t6",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "d",
- "cost": 3,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 2.6076,
- "records": 1,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 4.6289,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "DuplicatesWeedout",
- "cost": 5.2289,
- "records": 1,
- "duplicate_tables_left": true,
- "chosen": true
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
- },
- {
- "table": "t6",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "index",
- "index": "d",
- "cost": 1,
- "records": 1,
- "chosen": true
- },
- {
- "access_type": "table scan",
- "cost": 2,
- "records": 4,
- "chosen": false
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 1,
- "records_for_plan": 1,
- "semijoin_strategy_choice": [
- ] /* semijoin_strategy_choice */,
- "rest_of_plan": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 2.0213,
- "records": 3,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 3.0213,
- "records_for_plan": 3,
- "semijoin_strategy_choice": [
- {
- "strategy": "FirstMatch",
- "recompute_best_access_paths": {
- "cause": "join_buffering_not_possible",
- "tables": [
- {
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 2.0213,
- "records": 3,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- }
- ] /* tables */
- } /* recompute_best_access_paths */,
- "cost": 3.0213,
- "records": 1,
- "chosen": true
- },
- {
- "strategy": "MaterializationLookup",
- "cost": 3.2212,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- },
- {
- "strategy": "DuplicatesWeedout",
- "cost": 3.6213,
- "records": 1,
- "duplicate_tables_left": false,
- "chosen": false
- }
- ] /* semijoin_strategy_choice */,
- "chosen": true
- }
- ] /* rest_of_plan */
+ "cost_for_plan": 2.0154,
+ "records_for_plan": 2,
+ "chosen": true
}
] /* considered_execution_plans */
},
{
- "reconsidering_access_paths_for_semijoin": {
- "strategy": "FirstMatch",
- "table": "t2",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */
- } /* reconsidering_access_paths_for_semijoin */
- },
- {
"attaching_conditions_to_tables": {
- "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+ "original_condition": null,
"attached_conditions": [
{
- "table": "t6",
- "attached": "(`test`.`t6`.`d` = `f1`())"
- },
- {
- "table": "t2",
- "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+ "database": "test",
+ "table": "t1",
+ "attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4617,6 +5506,16 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4641,6 +5540,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4652,13 +5552,15 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -4674,11 +5576,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4694,6 +5606,16 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4747,10 +5669,36 @@ select d+1 into res from t6 where d= NAM
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -4790,6 +5738,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4801,13 +5750,15 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -4823,11 +5774,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -4895,10 +5856,36 @@ select @trace;
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "impossible_condition": {
+ "table": "t6",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -4998,12 +5985,55 @@ select d into res from t6 where d in (se
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t6",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- } /* more_range_optimizer_trace */
+ "range_analysis": {
+ "table_scan": {
+ "records": 4,
+ "cost": 4.9068
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "usable": true,
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "d",
+ "ranges": [
+ "3 <= d <= 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "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": "t2",
"table_scan": {
"records": 7,
@@ -5022,13 +6052,15 @@ select d into res from t6 where d in (se
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 7,
+ "cost": 2.0496,
"chosen": true
}
] /* considered_access_paths */
@@ -5047,20 +6079,22 @@ select d into res from t6 where d in (se
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t6",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "index",
+ "access_type": "ref",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 4,
+ "cause": "cost",
"chosen": false
}
] /* considered_access_paths */
@@ -5071,13 +6105,15 @@ select d into res from t6 where d in (se
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 7,
+ "cost": 2.0496,
"chosen": true
}
] /* considered_access_paths */
@@ -5091,13 +6127,15 @@ select d into res from t6 where d in (se
"cause": "join_buffering_not_possible",
"tables": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 7,
+ "cost": 2.0496,
"chosen": true
}
] /* considered_access_paths */
@@ -5129,13 +6167,15 @@ select d into res from t6 where d in (se
] /* rest_of_plan */
},
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 7,
+ "cost": 2.0496,
"chosen": true
}
] /* considered_access_paths */
@@ -5151,13 +6191,14 @@ select d into res from t6 where d in (se
{
"reconsidering_access_paths_for_semijoin": {
"strategy": "FirstMatch",
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
+ "access_type": "scan",
"records": 7,
+ "cost": 2.0496,
"chosen": true
}
] /* considered_access_paths */
@@ -5169,15 +6210,30 @@ select d into res from t6 where d in (se
"original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
"attached_conditions": [
{
+ "database": "test",
"table": "t6",
"attached": "(`test`.`t6`.`d` = `f1`())"
},
{
+ "database": "test",
"table": "t2",
"attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5195,6 +6251,16 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -5219,6 +6285,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -5230,13 +6297,117 @@ select sum(data) into ret from t1 {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
"cost": 2.0154,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0154,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+insert into t1 values("z",0) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual"
+ }
+ ] /* steps */
+} 0 0
+delete from t1 where id="z" {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* steps */
+} 0 0
+select sum(data) into ret from t1 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 2,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -5252,11 +6423,21 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5323,6 +6504,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -5334,13 +6516,15 @@ explain select * from v1 where id="b" {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -5356,11 +6540,21 @@ explain select * from v1 where id="b" {
"original_condition": "(`test`.`t1`.`id` = 'b')",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(`test`.`t1`.`id` = 'b')"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5384,6 +6578,16 @@ delete from v1 where data=100 {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where ((`data` = 100) and (`test`.`t1`.`id` < 'c'))"
+ },
+ {
+ "database": "test",
+ "table": "v1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -5437,6 +6641,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -5448,13 +6653,15 @@ explain select * from v1 where id="b" {
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0154,
"chosen": true
}
] /* considered_access_paths */
@@ -5470,11 +6677,21 @@ explain select * from v1 where id="b" {
"original_condition": "(`test`.`t1`.`id` < 'c')",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(`test`.`t1`.`id` < 'c')"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5508,6 +6725,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "v1",
"table_scan": {
"records": 2,
@@ -5519,13 +6737,15 @@ explain select * from v1 where id="b" {
{
"considered_execution_plans": [
{
+ "database": "",
"table": "v1",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 10.1,
"chosen": true
}
] /* considered_access_paths */
@@ -5541,11 +6761,21 @@ explain select * from v1 where id="b" {
"original_condition": "(`v1`.`id` = 'b')",
"attached_conditions": [
{
+ "database": "",
"table": "v1",
"attached": "(`v1`.`id` = 'b')"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "v1",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5594,6 +6824,7 @@ VARIABLE_NAME="optimizer_trace" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "information_schema",
"table": "session_variables",
"table_scan": {
"records": 2,
@@ -5605,13 +6836,15 @@ VARIABLE_NAME="optimizer_trace" {
{
"considered_execution_plans": [
{
+ "database": "information_schema",
"table": "session_variables",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 10.1,
"chosen": true
}
] /* considered_access_paths */
@@ -5627,11 +6860,21 @@ VARIABLE_NAME="optimizer_trace" {
"original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')",
"attached_conditions": [
{
+ "database": "information_schema",
"table": "session_variables",
"attached": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')"
}
] /* attached_conditions */
} /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "information_schema",
+ "table": "session_variables",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -5693,6 +6936,7 @@ select TRACE into dumpfile 'MYSQLTEST_VA
],
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 2,
@@ -5704,13 +6948,15 @@ select TRACE into dumpfile 'MYSQLTEST_VA
{
"considered_execution_plans": [
{
+ "database": "",
"table": "",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 2,
+ "cost": 10.1,
"chosen": true
}
]
@@ -5726,11 +6972,21 @@ select TRACE into dumpfile 'MYSQLTEST_VA
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
]
}
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "",
+ "table": "",
+ "scan_type": "table"
+ }
+ ]
}
]
}
=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2010-11-22 09:59:17 +0000
@@ -202,13 +202,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -319,13 +319,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -539,13 +539,13 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -729,13 +729,202 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range NULL i2_1 4 NULL 103 Using index for group-by
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT key2 FROM t2 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select distinct `key2` AS `key2` from `test`.`t2`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "i2_1",
+ "i2_2"
+ ] /* keys */,
+ "cause": "distinct"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i2_1",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_2",
+ "usable": true,
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "i2_1",
+ "cost": 235.03,
+ "chosen": false,
+ "cause": "cost"
+ } /* best_covering_index_scan */,
+ "group_index_range": {
+ "distinct_query": true,
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_covering"
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "null",
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "null",
+ "min_aggregate": false,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "records": 103,
+ "cost": 50.6,
+ "key_parts_used_for_access": [
+ "key2"
+ ] /* key_parts_used_for_access */,
+ "ranges": [
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 103,
+ "cost_for_plan": 50.6,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 103,
+ "cost": 50.6,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 50.6,
+ "records_for_plan": 103,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -956,13 +1145,13 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1152,12 +1341,12 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1405,13 +1594,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1539,13 +1728,13 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 <
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1641,7 +1830,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
{
"index": "PRIMARY",
"ranges": [
- "5 <= key1a <= 5 : key1b < 10"
+ "5 <= key1a <= 5 AND key1b < 10"
] /* ranges */,
"index_only": false,
"records": 1,
@@ -1652,7 +1841,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
{
"index": "i1b",
"ranges": [
- "key1b < 10 : 5 <= key1a <= 5"
+ "key1b < 10 AND 5 <= key1a <= 5"
] /* ranges */,
"index_only": false,
"records": 9,
@@ -1673,7 +1862,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
"index": "PRIMARY",
"records": 1,
"ranges": [
- "5 <= key1a <= 5 : key1b < 10"
+ "5 <= key1a <= 5 AND key1b < 10"
] /* ranges */
} /* range_access_plan */,
"records_for_plan": 1,
@@ -1725,13 +1914,13 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t2",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -1920,13 +2109,13 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2154,13 +2343,13 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1",
"scan_type": "table"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
@@ -2317,12 +2506,12 @@ o"
} /* attaching_conditions_to_tables */
},
{
- "refine_plan": {
- "refine_plan_for_table": {
+ "refine_plan": [
+ {
"database": "test",
"table": "t1"
- } /* refine_plan_for_table */
- } /* refine_plan */
+ }
+ ] /* refine_plan */
}
] /* steps */
} /* join_optimization */
=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test 2010-11-17 12:27:39 +0000
+++ b/mysql-test/t/optimizer_trace_range.test 2010-11-22 09:59:17 +0000
@@ -85,6 +85,11 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
--echo
SELECT * FROM information_schema.OPTIMIZER_TRACE;
+# distinct - group quick select without grouping attribute
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
# group with range
--echo
EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-11-17 12:27:39 +0000
+++ b/sql/opt_range.cc 2010-11-22 09:59:17 +0000
@@ -1995,9 +1995,10 @@ print_key2(String *out, const KEY_PART_I
if (field->real_maybe_null())
{
- /* Byte 0 of key is the null-byte. If set, key is NULL.
- Otherwise, print the key value starting immediately after the
- null-byte
+ /*
+ Byte 0 of key is the null-byte. If set, key is NULL.
+ Otherwise, print the key value starting immediately after the
+ null-byte
*/
if (*key)
{
@@ -2053,7 +2054,8 @@ public:
DBUG_RETURN(quick);
}
- void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
DBUG_ASSERT(param->using_real_indexes);
@@ -2062,7 +2064,7 @@ public:
const KEY cur_key= param->table->key_info[keynr_in_table];
const KEY_PART_INFO *key_part= cur_key.key_part;
- trace->add_str("type", "range_scan").
+ trace_object->add_str("type", "range_scan").
add_str("index", cur_key.name, strlen(cur_key.name)).
add("records", records);
@@ -2129,10 +2131,11 @@ public:
bool is_covering; /* TRUE if no row retrieval phase is necessary */
double index_scan_costs; /* SUM(cost(index_scan)) */
- void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace->add_str("type", "index_roworder_intersect").
+ trace_object->add_str("type", "index_roworder_intersect").
add("records", records).
add("cost", read_cost).
add("covering", is_covering).
@@ -2193,10 +2196,11 @@ public:
TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
TABLE_READ_PLAN **last_ror; /* end of the above array */
- void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace->add_str("type", "index_roworder_union");
+ trace_object->add_str("type", "index_roworder_union");
Opt_trace_array ota(param->thd->opt_trace, "union_of");
for (TABLE_READ_PLAN **current= first_ror;
current != last_ror;
@@ -2225,10 +2229,11 @@ public:
TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
TRP_RANGE **range_scans_end; /* end of the array */
- void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace->add_str("type", "index_merge");
+ trace_object->add_str("type", "index_merge");
Opt_trace_array ota(param->thd->opt_trace, "index_merge_of");
for (TRP_RANGE **current= range_scans;
current != range_scans_end;
@@ -2278,17 +2283,19 @@ public:
ha_rows quick_prefix_records;
public:
- void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const
{
#ifdef OPTIMIZER_TRACE
- trace->add_str("type", "index_group").
+ trace_object->add_str("type", "index_group").
add_str("index", index_info->name, strlen(index_info->name));
if (min_max_arg_part)
- trace->add_str("group_attribute", min_max_arg_part->field->field_name,
- strlen(min_max_arg_part->field->field_name));
+ trace_object->add_str("group_attribute",
+ min_max_arg_part->field->field_name,
+ strlen(min_max_arg_part->field->field_name));
else
- trace->add_str("group_attribute", "<none>");
- trace->add("min_aggregate", have_min).
+ trace_object->add_str("group_attribute", "null");
+ trace_object->add("min_aggregate", have_min).
add("max_aggregate", have_max).
add("distinct_aggregate", have_agg_distinct).
add("records", records).
@@ -2749,27 +2756,25 @@ int SQL_SELECT::test_quick_select(THD *t
DBUG_PRINT("info",("No range reads possible,"
" trying to construct index_merge"));
List_iterator_fast<SEL_IMERGE> it(tree->merges);
+ Opt_trace_array trace_idx_merge(trace,
+ "analyzing_index_merge",
+ Opt_trace_context::RANGE_OPTIMIZER);
+ while ((imerge= it++))
{
- Opt_trace_array trace_idx_merge(trace,
- "analyzing_index_merge",
- Opt_trace_context::RANGE_OPTIMIZER);
- while ((imerge= it++))
+ new_conj_trp= get_best_disjunct_quick(¶m, imerge,
+ best_read_time);
+ if (new_conj_trp)
+ set_if_smaller(param.table->quick_condition_rows,
+ new_conj_trp->records);
+ if (!best_conj_trp ||
+ (new_conj_trp &&
+ new_conj_trp->read_cost < best_conj_trp->read_cost))
{
- new_conj_trp= get_best_disjunct_quick(¶m, imerge,
- best_read_time);
- if (new_conj_trp)
- set_if_smaller(param.table->quick_condition_rows,
- new_conj_trp->records);
- if (!best_conj_trp ||
- (new_conj_trp &&
- new_conj_trp->read_cost < best_conj_trp->read_cost))
- {
- best_conj_trp= new_conj_trp;
- }
+ best_conj_trp= new_conj_trp;
}
- if (best_conj_trp)
- best_trp= best_conj_trp;
}
+ if (best_conj_trp)
+ best_trp= best_conj_trp;
}
}
}
@@ -4145,7 +4150,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
n_child_scans)))
DBUG_RETURN(NULL);
// Note: to_merge.end() is called to close this object after this for-loop.
- Opt_trace_array to_merge(trace,"indices_to_merge");
+ Opt_trace_array to_merge(trace, "indices_to_merge");
/*
Collect best 'range' scan for each of disjuncts, and, while doing so,
analyze possibility of ROR scans. Also calculate some values needed by
@@ -4234,10 +4239,10 @@ TABLE_READ_PLAN *get_best_disjunct_quick
Add one ROWID comparison for each row retrieved on non-CPK scan. (it
is done in QUICK_RANGE_SELECT::row_in_ranges)
*/
- double rowid_comp_cost= non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
- imerge_cost += rowid_comp_cost;
+ const double rid_comp_cost= non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+ imerge_cost+= rid_comp_cost;
trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan",
- rowid_comp_cost);
+ rid_comp_cost);
}
/* Calculate cost(rowid_to_row_scan) */
@@ -4319,54 +4324,56 @@ skip_to_ror_scan:
roru_total_records= 0;
cur_roru_plan= roru_read_plans;
+ /*
+ Note: trace_analyze_ror.end() is called to close this object after
+ this for-loop.
+ */
+ Opt_trace_array trace_analyze_ror(trace, "analyzing_roworder_scans");
/* Find 'best' ROR scan for each of trees in disjunction */
-
+ for (ptree= imerge->trees, cur_child= range_scans;
+ ptree != imerge->trees_next;
+ ptree++, cur_child++, cur_roru_plan++)
{
- Opt_trace_array ota(trace, "analyzing_roworder_scans");
- for (ptree= imerge->trees, cur_child= range_scans;
- ptree != imerge->trees_next;
- ptree++, cur_child++, cur_roru_plan++)
- {
-
- Opt_trace_object trp_info(trace);
- (*cur_child)->trace_basic_info(param, &trp_info);
+ Opt_trace_object trp_info(trace);
+ (*cur_child)->trace_basic_info(param, &trp_info);
- /*
- Assume the best ROR scan is the one that has cheapest
- full-row-retrieval scan cost.
- Also accumulate index_only scan costs as we'll need them to
- calculate overall index_intersection cost.
- */
- double cost;
- if ((*cur_child)->is_ror)
- {
- /* Ok, we have index_only cost, now get full rows scan cost */
- cost= param->table->file->
- read_time(param->real_keynr[(*cur_child)->key_idx], 1,
- (*cur_child)->records) +
- rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
- }
- else
- cost= read_time;
+ /*
+ Assume the best ROR scan is the one that has cheapest
+ full-row-retrieval scan cost.
+ Also accumulate index_only scan costs as we'll need them to
+ calculate overall index_intersection cost.
+ */
+ double cost;
+ if ((*cur_child)->is_ror)
+ {
+ /* Ok, we have index_only cost, now get full rows scan cost */
+ cost= param->table->file->
+ read_time(param->real_keynr[(*cur_child)->key_idx], 1,
+ (*cur_child)->records) +
+ rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
+ }
+ else
+ cost= read_time;
- TABLE_READ_PLAN *prev_plan= *cur_child;
- if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
- &dummy)))
- {
- if (prev_plan->is_ror)
- *cur_roru_plan= prev_plan;
- else
- DBUG_RETURN(imerge_trp);
- roru_index_costs += (*cur_roru_plan)->read_cost;
- }
+ TABLE_READ_PLAN *prev_plan= *cur_child;
+ if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
+ &dummy)))
+ {
+ if (prev_plan->is_ror)
+ *cur_roru_plan= prev_plan;
else
- roru_index_costs +=
- ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
- roru_total_records += (*cur_roru_plan)->records;
- roru_intersect_part *= (*cur_roru_plan)->records /
- param->table->file->stats.records;
+ DBUG_RETURN(imerge_trp);
+ roru_index_costs += (*cur_roru_plan)->read_cost;
}
+ else
+ roru_index_costs +=
+ ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
+ roru_total_records += (*cur_roru_plan)->records;
+ roru_intersect_part *= (*cur_roru_plan)->records /
+ param->table->file->stats.records;
}
+ // Note: trace_analyze_ror trace object is closed here
+ trace_analyze_ror.end();
/*
rows to retrieve=
@@ -5017,38 +5024,43 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
ROR_SCAN_INFO **intersect_scans_best;
cur_ror_scan= tree->ror_scans;
intersect_scans_best= intersect_scans;
- {
- Opt_trace_array ota(param->thd->opt_trace, "intersecting_indices");
- while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
- {
- Opt_trace_object trace_idx(param->thd->opt_trace);
- char *idx_name= param->table->key_info[(*cur_ror_scan)->keynr].name;
- trace_idx.add_str("index", idx_name, strlen(idx_name));
- /* S= S + first(R); R= R - first(R); */
- if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
- {
- trace_idx.add("usable", false).
- add_str("cause", "does_not_reduce_cost_of_intersect");
- cur_ror_scan++;
- continue;
- }
+ /*
+ Note: trace_isect_idx.end() is called to close this object after
+ this while-loop.
+ */
+ Opt_trace_array trace_isect_idx(param->thd->opt_trace,
+ "intersecting_indices");
+ while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
+ {
+ Opt_trace_object trace_idx(param->thd->opt_trace);
+ char *idx_name= param->table->key_info[(*cur_ror_scan)->keynr].name;
+ trace_idx.add_str("index", idx_name, strlen(idx_name));
+ /* S= S + first(R); R= R - first(R); */
+ if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "does_not_reduce_cost_of_intersect");
+ cur_ror_scan++;
+ continue;
+ }
- trace_idx.add("usable", true).
- add("matching_records_now", intersect->out_rows).
- add("cumulated_cost", intersect->total_cost).
- add("isect_covering_with_this_index", intersect->is_covering);
-
- *(intersect_scans_end++)= *(cur_ror_scan++);
-
- if (intersect->total_cost < min_cost)
- {
- /* Local minimum found, save it */
- ror_intersect_cpy(intersect_best, intersect);
- intersect_scans_best= intersect_scans_end;
- min_cost = intersect->total_cost;
- }
+ trace_idx.add("usable", true).
+ add("matching_records_now", intersect->out_rows).
+ add("cumulated_cost", intersect->total_cost).
+ add("isect_covering_with_this_index", intersect->is_covering);
+
+ *(intersect_scans_end++)= *(cur_ror_scan++);
+
+ if (intersect->total_cost < min_cost)
+ {
+ /* Local minimum found, save it */
+ ror_intersect_cpy(intersect_best, intersect);
+ intersect_scans_best= intersect_scans_end;
+ min_cost = intersect->total_cost;
}
}
+ // Note: trace_isect_idx trace object is closed here
+ trace_isect_idx.end();
if (intersect_scans_best == intersect_scans)
{
@@ -5086,13 +5098,13 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
else
Opt_trace_object (param->thd->opt_trace, "clustered_pk").
add("clustered_pk_added_to_intersect", false).add_str("cause", "cost");
- }
- else
+ }
+ else
{
Opt_trace_object trace_cpk(param->thd->opt_trace, "clustered_pk");
- trace_cpk.add("clustered_pk_added_to_intersect", false);
- cpk_scan ? trace_cpk.add_str("cause", "ror_is_covering")
- : trace_cpk.add_str("cause", "no_clustered_pk_index");
+ trace_cpk.add("clustered_pk_added_to_intersect", false).
+ add_str("cause", cpk_scan ?
+ "roworder_is_covering" : "no_clustered_pk_index");
}
/* Ok, return ROR-intersect plan if we have found one */
@@ -6360,16 +6372,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
field->type() == MYSQL_TYPE_DATETIME))
field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
{
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
- /*
- if we have a condition like
- field=stored_function_SELECTing_table(),
- save_in_field() call below will evaluate the function and thus
- this will add a join_preparation object which does not fit well
- (sometimes crashes).
- */
- Opt_trace_disable_I_S otd(param->thd->opt_trace, TRUE);
-#endif
err= value->save_in_field_no_warnings(field, 1);
}
if (err > 0)
@@ -8018,16 +8020,17 @@ static void step_down_to(String *s, SEL_
{
#ifdef OPTIMIZER_TRACE
- if (arg->param->thd->opt_trace && arg->param->thd->opt_trace->is_started())
+ if (unlikely(arg->param->thd->opt_trace != NULL) &&
+ arg->param->thd->opt_trace->is_started())
{
- /*
+ /*
Stepping down will append the range for the current keypart (in
key_tree) to seq. Trace range here since this is where it is
human readable.
*/
- const KEY_PART_INFO *key_part=
+ const KEY_PART_INFO *key_part=
arg->param->table->key_info[arg->real_keyno].key_part + key_tree->part;
-
+
uint16 length= key_part->length;
append_range(s, key_part,
key_tree->min_value, length,
@@ -8173,7 +8176,7 @@ walk_right_n_up:
MAX_KEY);
#ifdef OPTIMIZER_TRACE
- if (seq->param->thd->opt_trace &&
+ if (unlikely(seq->param->thd->opt_trace != NULL) &&
seq->param->thd->opt_trace->is_started())
{
// Trace the range we just stored
@@ -10152,8 +10155,6 @@ get_best_group_min_max(PARAM *param, SEL
Item_field *item_field;
bool is_agg_distinct;
List<Item_field> agg_distinct_flds;
- KEY *cur_index_info= table->key_info;
- KEY *cur_index_info_end= cur_index_info + table->s->keys;
/* Cost-related variables for the best index so far. */
double best_read_cost= DBL_MAX;
ha_rows best_records= 0;
@@ -10162,11 +10163,6 @@ get_best_group_min_max(PARAM *param, SEL
uint best_param_idx= 0;
List_iterator<Item> select_items_it;
- const uint pk= param->table->s->primary_key;
- SEL_ARG *cur_index_tree= NULL;
- ha_rows cur_quick_prefix_records= 0;
- uint cur_param_idx=MAX_KEY;
-
DBUG_ENTER("get_best_group_min_max");
Opt_trace_object trace_group(thd->opt_trace, "group_index_range",
@@ -10186,7 +10182,7 @@ get_best_group_min_max(PARAM *param, SEL
}
if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
{
- cause= "is_rollup";
+ cause= "rollup";
goto return_null_not_chosen_cause;
}
if (table->s->keys == 0) /* There are no indexes to use. */
@@ -10248,7 +10244,7 @@ get_best_group_min_max(PARAM *param, SEL
/* Check (SA5). */
if (join->select_distinct)
{
- trace_group.add("is_distinct_query", true);
+ trace_group.add("distinct_query", true);
while ((item= select_items_it++))
{
if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -10272,6 +10268,12 @@ get_best_group_min_max(PARAM *param, SEL
first one. Here we set the variables: group_prefix_len and index_info.
*/
{
+ const uint pk= param->table->s->primary_key;
+ KEY *cur_index_info= table->key_info;
+ KEY *cur_index_info_end= cur_index_info + table->s->keys;
+ SEL_ARG *cur_index_tree= NULL;
+ ha_rows cur_quick_prefix_records= 0;
+ uint cur_param_idx= MAX_KEY;
Opt_trace_array ota(thd->opt_trace, "potential_group_range_indices");
for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
cur_index_info++, cur_index++)
@@ -10628,7 +10630,7 @@ get_best_group_min_max(PARAM *param, SEL
read_plan->records= best_records;
if (read_time < best_read_cost && is_agg_distinct)
{
- trace_group.add("is_index_scan", true);
+ trace_group.add("index_scan", true);
read_plan->read_cost= 0;
read_plan->use_index_scan();
}
@@ -12356,7 +12358,7 @@ static void append_range(String *out,
const uint flag)
{
if (out->length() > 0)
- out->append(STRING_WITH_LEN(" : "));
+ out->append(STRING_WITH_LEN(" AND "));
if (!(flag & NO_MIN_RANGE))
{
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2010-11-17 12:27:39 +0000
+++ b/sql/sql_delete.cc 2010-11-22 09:59:17 +0000
@@ -192,26 +192,26 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_RETURN(TRUE);
{ // Enter scope for optimizer trace wrapper
- Opt_trace_object wrapper(thd->opt_trace);
- wrapper.add_str("database", table_list->db,
- strlen(table_list->db)).
- add_str("table", table_list->alias, strlen(table_list->alias));
+ Opt_trace_object wrapper(thd->opt_trace);
+ wrapper.add_str("database", table_list->db,
+ strlen(table_list->db)).
+ add_str("table", table_list->alias, strlen(table_list->alias));
- if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
- {
- delete select;
- free_underlaid_joins(thd, select_lex);
- /*
- Error was already created by quick select evaluation (check_quick()).
- TODO: Add error code output parameter to Item::val_xxx() methods.
- Currently they rely on the user checking DA for
- errors when unwinding the stack after calling Item::val_xxx().
- */
- if (thd->is_error())
- DBUG_RETURN(TRUE);
- my_ok(thd, 0);
- DBUG_RETURN(0); // Nothing to delete
- }
+ if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
+ {
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ /*
+ Error was already created by quick select evaluation (check_quick()).
+ TODO: Add error code output parameter to Item::val_xxx() methods.
+ Currently they rely on the user checking DA for
+ errors when unwinding the stack after calling Item::val_xxx().
+ */
+ if (thd->is_error())
+ DBUG_RETURN(TRUE);
+ my_ok(thd, 0);
+ DBUG_RETURN(0); // Nothing to delete
+ }
} // Ends scope for optimizer trace wrapper
/* If running in safe sql mode, don't allow updates without keys */
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-11-17 12:27:39 +0000
+++ b/sql/sql_select.cc 2010-11-22 09:59:17 +0000
@@ -4947,7 +4947,7 @@ make_join_statistics(JOIN *join, TABLE_L
{
trace_table.add("records", 1).add("cost", 1);
trace_table.add_str("table_type", (s->type == JT_SYSTEM) ?
- "system_table": "const_table");
+ "system": "const");
/* Only one matching row */
s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
@@ -6564,7 +6564,7 @@ static void trace_indices_added_group_di
const key_map new_keys,
const char* cause)
{
- if (trace == NULL || !trace->is_started())
+ if (likely(trace == NULL) || !trace->is_started())
return;
KEY *key_info= join_tab->table->key_info;
@@ -11155,7 +11155,7 @@ make_join_readinfo(JOIN *join, ulonglong
Opt_trace_context * const trace= join->thd->opt_trace;
Opt_trace_object wrapper(trace);
- Opt_trace_object trace_refine_plan(trace, "refine_plan");
+ Opt_trace_array trace_refine_plan(trace, "refine_plan");
if (!join->select_lex->sj_nests.is_empty() &&
setup_semijoin_dups_elimination(join, options, no_jbuf_after))
@@ -11181,7 +11181,7 @@ make_join_readinfo(JOIN *join, ulonglong
table->status=STATUS_NO_RECORD;
pick_table_access_method (tab);
- Opt_trace_object trace_refine_table(trace, "refine_plan_for_table");
+ Opt_trace_object trace_refine_table(trace);
trace_refine_table.add_str("database", join->tables_list->db,
strlen(join->tables_list->db)).
add_str("table", table->alias, strlen(table->alias));
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2010-11-17 12:27:39 +0000
+++ b/sql/sql_update.cc 2010-11-22 09:59:17 +0000
@@ -342,30 +342,30 @@ int mysql_update(THD *thd,
select= make_select(table, 0, 0, conds, 0, &error);
{ // Enter scope for optimizer trace wrapper
- Opt_trace_object wrapper(thd->opt_trace);
- wrapper.add_str("database", table_list->db,
- strlen(table_list->db)).
- add_str("table", table_list->alias, strlen(table_list->alias));
+ Opt_trace_object wrapper(thd->opt_trace);
+ wrapper.add_str("database", table_list->db,
+ strlen(table_list->db)).
+ add_str("table", table_list->alias, strlen(table_list->alias));
- if (error || !limit ||
- (select && select->check_quick(thd, safe_update, limit)))
- {
- delete select;
- free_underlaid_joins(thd, select_lex);
- /*
- There was an error or the error was already sent by
- the quick select evaluation.
- TODO: Add error code output parameter to Item::val_xxx() methods.
- Currently they rely on the user checking DA for
- errors when unwinding the stack after calling Item::val_xxx().
- */
- if (error || thd->is_error())
+ if (error || !limit ||
+ (select && select->check_quick(thd, safe_update, limit)))
{
- DBUG_RETURN(1); // Error in where
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ /*
+ There was an error or the error was already sent by
+ the quick select evaluation.
+ TODO: Add error code output parameter to Item::val_xxx() methods.
+ Currently they rely on the user checking DA for
+ errors when unwinding the stack after calling Item::val_xxx().
+ */
+ if (error || thd->is_error())
+ {
+ DBUG_RETURN(1); // Error in where
+ }
+ my_ok(thd); // No matching records
+ DBUG_RETURN(0);
}
- my_ok(thd); // No matching records
- DBUG_RETURN(0);
- }
} // Ends scope for optimizer trace wrapper
/* If running in safe sql mode, don't allow updates without keys */
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101122095917-72wbo7qtkh12ry8w.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3234) WL#5594 | Jorgen Loland | 22 Nov |
