#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:guilhem@stripped
3229 Jorgen Loland 2010-10-22
WL#5594 - Add optimizer traces to the range optimizer
Add optimizer trace points to the range optimizer.
@ mysql-test/include/optimizer_trace.inc
Increase optimizer_trace_max_mem_size to 1MB
@ mysql-test/r/optimizer_trace_charset.result
Traces now include range optimizer tracepoints. Added test for range with special characters.
@ mysql-test/r/optimizer_trace_no_prot.result
Adds range information to trace of existing queries
@ mysql-test/r/optimizer_trace_range.result
Tests for tracing of range optimizer
@ mysql-test/t/optimizer_trace_charset.test
Added test for range with special characters.
@ mysql-test/t/optimizer_trace_range.test
Tests for tracing of range optimizer
@ sql/handler.cc
Enable ifndef'ed tracepoint belonging to range optimizer
@ sql/opt_range.cc
Add tracepoints to range optimizer
@ sql/opt_range.h
Add tracepoints to range optimizer
@ sql/opt_trace.cc
Add RANGE_OPTIMIZER trace flag
@ sql/opt_trace.h
Add RANGE_OPTIMIZER trace flag
@ sql/sql_select.cc
Add tracepoints to range optimizer
added:
mysql-test/r/optimizer_trace_range.result
mysql-test/t/optimizer_trace_range.test
modified:
mysql-test/include/optimizer_trace.inc
mysql-test/r/optimizer_trace_charset.result
mysql-test/r/optimizer_trace_no_prot.result
mysql-test/t/optimizer_trace_charset.test
sql/handler.cc
sql/opt_range.cc
sql/opt_range.h
sql/opt_trace.cc
sql/opt_trace.h
sql/sql_select.cc
=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc 2010-10-21 15:22:32 +0000
+++ b/mysql-test/include/optimizer_trace.inc 2010-10-22 08:31:43 +0000
@@ -24,6 +24,8 @@
# should be empty
select * from information_schema.OPTIMIZER_TRACE;
+let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
+eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
set @@session.optimizer_trace="enabled=on";
select * from information_schema.OPTIMIZER_TRACE;
@@ -108,7 +110,7 @@ select * from information_schema.OPTIMIZ
set optimizer_trace_max_mem_size=0;
select 1;
select * from information_schema.OPTIMIZER_TRACE;
-set optimizer_trace_max_mem_size=default;
+eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
# semijoin
=== modified file 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result 2010-10-09 15:04:30 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result 2010-10-22 08:31:43 +0000
@@ -33,6 +33,13 @@ explain extended select '��� from t1
"t1"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -71,6 +78,13 @@ explain extended select 'ÁÂÃÄÅ' fro
"t1"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -124,6 +138,13 @@ select * from v1 where v1.col = '���
"t1"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -165,6 +186,13 @@ select * from v1 where v1.col = '���
"v1"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "",
+ "table": "v1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -181,4 +209,135 @@ select * from v1 where v1.col = '���
}
drop table t1;
drop view v1;
+create table t1(c char(4) primary key);
+insert into t1 values ('aaa'), ('��');
+select * from t1 where c < '���;
+c
+aaa
+��
+select query, trace from information_schema.OPTIMIZER_TRACE;
+query trace
+select * from t1 where c < '��� {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where (`c` < '?????')"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`c` < '?????')",
+ "after_equality_propagation": "(`test`.`t1`.`c` < '?????')",
+ "after_constant_propagation": "(`test`.`t1`.`c` < '?????')",
+ "after_trivial_conditions_removal": "(`test`.`t1`.`c` < '?????')"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "c"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4213,
+ "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": "PRIMARY",
+ "ranges": [
+ "c <= ��"
+ ] /* 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": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 2,
+ "cost": 2.0034,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.0034,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`c` < '?????')",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`c` < '?????')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+}
+drop table t1;
set optimizer_trace=default;
=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result 2010-10-21 15:22:32 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-10-22 08:31:43 +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 D WHERE d = c) AS
"C"
],
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
]
},
{
@@ -78,10 +86,36 @@ SELECT (SELECT 1 FROM D WHERE d = c) AS
],
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ },
+ "impossible_range": true
+ },
+ "records": 0,
+ "cause": "impossible_where_condition"
}
]
}
@@ -96,18 +130,18 @@ SELECT (SELECT 1 FROM D 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.3699
+50.1160
set optimizer_trace="one_line=on";
SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C ;
RESULT
NULL
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["C"],"records_estimation": []},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`D`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "D.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"table": "D","more_range_optimizer_trace": {"TODO?": "yes!"}}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+SELECT (SELECT 1 FROM D WHERE d = c) AS RESULT FROM C {"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`D` where (`d` = `c`)) AS `RESULT` from `test`.`C`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["C"],"records_estimation": [{"database": "test","table": "C","records": 1,"cost": 1,"cause": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`D`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`D`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`D`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "D.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "D","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": "D","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.4238
+6.0729
set optimizer_trace="one_line=off,end_marker=on";
EXPLAIN SELECT (SELECT 1 FROM D WHERE d = ifnull(c,null)) AS RESULT FROM C ;
id select_type table type possible_keys key key_len ref rows Extra
@@ -143,6 +177,13 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -181,10 +222,36 @@ EXPLAIN SELECT (SELECT 1 FROM D WHERE d
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "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 +296,13 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -267,10 +341,36 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "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 +433,13 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -371,10 +478,36 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "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 +543,36 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "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 +598,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 0,
@@ -450,13 +610,15 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
{
"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,6 +634,7 @@ SELECT (SELECT 1 FROM D WHERE d = ifnull
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
@@ -583,6 +746,13 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -655,6 +825,7 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 0,
@@ -666,13 +837,15 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
{
"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 */
@@ -688,6 +861,7 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
@@ -710,7 +884,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-8594
+8953
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
@@ -719,7 +893,7 @@ SELECT * FROM C WHERE 5 IN (SELECT 1 FRO
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-306
+671
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -727,7 +901,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-8288 1 1
+8282 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)
@@ -750,7 +924,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 C where c+1 in (select d+1 from D where d is null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY C system NULL NULL NULL NULL 1
@@ -819,10 +993,59 @@ explain SELECT c FROM C where c+1 in (se
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
"table": "D",
- "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 */
},
@@ -836,21 +1059,23 @@ explain SELECT c FROM C where c+1 in (se
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -886,21 +1111,23 @@ explain SELECT c FROM C where c+1 in (se
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -942,6 +1169,7 @@ explain SELECT c FROM C where c+1 in (se
"original_condition": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(isnull(`test`.`D`.`d`) and ((NULL + 1) = (`test`.`D`.`d` + 1)))"
}
@@ -1017,6 +1245,13 @@ explain SELECT c FROM C where c+1 in (se
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -1063,31 +1298,75 @@ explain SELECT c FROM C where c+1 in (se
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -1102,6 +1381,7 @@ explain SELECT c FROM C where c+1 in (se
"original_condition": "isnull(`test`.`D`.`d`)",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "isnull(`test`.`D`.`d`)"
}
@@ -1174,6 +1454,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1185,13 +1466,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 */
@@ -1207,6 +1490,7 @@ 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`)))"
}
@@ -1225,6 +1509,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1236,13 +1521,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 */
@@ -1258,6 +1545,7 @@ explain extended select * from t1 where
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -1326,6 +1614,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1337,13 +1626,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 */
@@ -1359,6 +1650,7 @@ 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`)))"
}
@@ -1377,6 +1669,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1388,13 +1681,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 */
@@ -1410,6 +1705,7 @@ explain extended select * from t1 where
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -1495,6 +1791,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1506,13 +1803,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 */
@@ -1528,6 +1827,7 @@ 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`)))"
}
@@ -1559,6 +1859,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1570,13 +1871,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 */
@@ -1592,6 +1895,7 @@ 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`)"
}
@@ -1676,6 +1980,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1687,13 +1992,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 */
@@ -1709,6 +2016,7 @@ 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`))))"
}
@@ -1740,6 +2048,7 @@ explain extended select * from t1 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -1751,13 +2060,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 */
@@ -1773,6 +2084,7 @@ 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`))"
}
@@ -1818,6 +2130,7 @@ explain select * from t1,t2 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -1825,6 +2138,7 @@ explain select * from t1,t2 {
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -1836,13 +2150,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 */
@@ -1851,13 +2167,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 */
@@ -1869,13 +2187,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 */
@@ -1884,13 +2204,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 */
@@ -1908,10 +2230,12 @@ explain select * from t1,t2 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
},
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -1925,7 +2249,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
@@ -1954,6 +2278,7 @@ explain select * from t1,t2 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -1961,6 +2286,7 @@ explain select * from t1,t2 {
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -1977,10 +2303,12 @@ explain select * from t1,t2 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
},
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -2108,6 +2436,7 @@ where a1 in (select b1 from t2_16 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1_16",
"table_scan": {
"records": 3,
@@ -2119,13 +2448,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 */
@@ -2141,6 +2472,7 @@ 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`))))"
}
@@ -2172,6 +2504,7 @@ where a1 in (select b1 from t2_16 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2_16",
"table_scan": {
"records": 3,
@@ -2183,13 +2516,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 */
@@ -2205,6 +2540,7 @@ 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`))"
}
@@ -2299,6 +2635,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2306,6 +2643,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2317,13 +2655,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 */
@@ -2332,13 +2672,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 */
@@ -2350,13 +2692,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 */
@@ -2372,10 +2716,12 @@ 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`)"
}
@@ -2407,6 +2753,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2418,13 +2765,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 */
@@ -2440,6 +2789,7 @@ 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))"
}
@@ -2497,6 +2847,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2508,13 +2859,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 */
@@ -2530,6 +2883,7 @@ 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)"
}
@@ -2561,6 +2915,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2572,13 +2927,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 */
@@ -2594,6 +2951,7 @@ 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)"
}
@@ -2619,6 +2977,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 1,
@@ -2630,13 +2989,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 */
@@ -2652,6 +3013,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
@@ -2763,6 +3125,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -2774,13 +3137,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 */
@@ -2796,6 +3161,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`)))"
}
@@ -2830,6 +3196,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2841,13 +3208,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 */
@@ -2863,6 +3232,7 @@ concat(c1,'y') IN
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
}
@@ -2881,6 +3251,7 @@ concat(c1,'y') IN
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 3,
@@ -2892,13 +3263,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 */
@@ -2914,6 +3287,7 @@ concat(c1,'y') IN
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t2",
"attached": null
}
@@ -2958,6 +3332,20 @@ select * from t1,t2 {
"t2"
] /* constant_tables */,
"records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ }
] /* records_estimation */
},
{
@@ -3075,6 +3463,28 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
"table": "t4",
"table_scan": {
"records": 2,
@@ -3086,14 +3496,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
}
@@ -3102,21 +3515,23 @@ trace
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t4",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "PRIMARY",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -3179,6 +3594,28 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
"table": "t4",
"table_scan": {
"records": 2,
@@ -3190,21 +3627,23 @@ trace
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t4",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "PRIMARY",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 2,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -3267,6 +3706,7 @@ select * from t1 left join t2 on t2.a=50
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3274,6 +3714,7 @@ select * from t1 left join t2 on t2.a=50
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3285,13 +3726,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 */
@@ -3300,13 +3743,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 */
@@ -3324,10 +3768,12 @@ 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))"
}
@@ -3413,6 +3859,7 @@ select * from t1 where (t1.a,t1.b) not i
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3424,13 +3871,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 */
@@ -3446,6 +3895,7 @@ 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))))))"
}
@@ -3485,6 +3935,7 @@ select * from t1 where (t1.a,t1.b) not i
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3496,13 +3947,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 */
@@ -3518,6 +3971,7 @@ 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))"
}
@@ -3578,6 +4032,7 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -3585,6 +4040,7 @@ trace
} /* table_scan */
},
{
+ "database": "test",
"table": "t2",
"table_scan": {
"records": 2,
@@ -3596,14 +4052,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 */,
@@ -3611,13 +4070,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 */
@@ -3629,13 +4090,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 */
@@ -3651,10 +4114,12 @@ trace
"original_condition": "(`test`.`t2`.`c` > -(1))",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
},
{
+ "database": "test",
"table": "t2",
"attached": "(`test`.`t2`.`c` > -(1))"
}
@@ -3720,6 +4185,59 @@ update D set d=5 where D is NULL {
"steps": [
{
"expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`D` where isnull(`D`)"
+ },
+ {
+ "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_in_plan": 1,
+ "cost_of_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -3730,6 +4248,55 @@ delete from D where d=5 {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`d` = 5)"
+ },
+ {
+ "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_in_plan": 1,
+ "cost_of_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -3780,23 +4347,67 @@ insert into D select * from D where D>7
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
"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 */
@@ -3812,6 +4423,7 @@ insert into D select * from D where D>7
"original_condition": "(`test`.`D`.`d` > 7)",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(`test`.`D`.`d` > 7)"
}
@@ -3861,23 +4473,80 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
"table": "D",
- "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_in_plan": 1,
+ "cost_of_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
}
] /* records_estimation */
},
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "D",
"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 */
@@ -3893,6 +4562,7 @@ update C,D set D.d=D.d+C.c+4-C.c-4 where
"original_condition": "(`test`.`D`.`d` > 7000)",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(`test`.`D`.`d` > 7000)"
}
@@ -3942,23 +4612,74 @@ delete D from C,D where D>7000 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
+ "table": "C",
+ "records": 1,
+ "cost": 1,
+ "cause": "system_table"
+ },
+ {
+ "database": "test",
"table": "D",
- "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": "D",
"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 */
@@ -3974,6 +4695,7 @@ delete D from C,D where D>7000 {
"original_condition": "(`test`.`D`.`d` > 7000)",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(`test`.`D`.`d` > 7000)"
}
@@ -4157,6 +4879,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4168,13 +4891,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 */
@@ -4190,6 +4915,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -4229,6 +4955,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4240,13 +4967,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 */
@@ -4262,6 +4991,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -4298,6 +5028,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4309,13 +5040,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 */
@@ -4331,6 +5064,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -4411,12 +5145,55 @@ select * from D where d in (select f1()
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": 3,
@@ -4435,13 +5212,15 @@ select * from D where d in (select f1()
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0212,
"chosen": true
}
] /* considered_access_paths */
@@ -4460,13 +5239,15 @@ select * from D where d in (select f1()
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0212,
"chosen": true
}
] /* considered_access_paths */
@@ -4477,20 +5258,22 @@ select * from D where d in (select f1()
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
+ "database": "test",
"table": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 3,
"records": 1,
+ "cost": 3,
"chosen": true
},
{
- "access_type": "table scan",
- "cost": 2.6076,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 1,
+ "cost": 2.6076,
"chosen": true
}
] /* considered_access_paths */
@@ -4511,21 +5294,23 @@ select * from D where d in (select f1()
] /* rest_of_plan */
},
{
+ "database": "test",
"table": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 4,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -4535,13 +5320,15 @@ select * from D where d in (select f1()
] /* semijoin_strategy_choice */,
"rest_of_plan": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0213,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0213,
"chosen": true
}
] /* considered_access_paths */
@@ -4555,13 +5342,15 @@ select * from D where d in (select f1()
"cause": "join_buffering_not_possible",
"tables": [
{
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0213,
+ "access_type": "scan",
+ "using_join_cache": true,
"records": 3,
+ "cost": 2.0213,
"chosen": true
}
] /* considered_access_paths */
@@ -4597,13 +5386,14 @@ select * from D where d in (select f1()
{
"reconsidering_access_paths_for_semijoin": {
"strategy": "FirstMatch",
+ "database": "test",
"table": "t2",
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
+ "access_type": "scan",
"records": 3,
+ "cost": 2.0212,
"chosen": true
}
] /* considered_access_paths */
@@ -4615,10 +5405,12 @@ select * from D where d in (select f1()
"original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(`test`.`D`.`d` = `f1`())"
},
{
+ "database": "test",
"table": "t2",
"attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
}
@@ -4641,6 +5433,14 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4665,6 +5465,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4676,13 +5477,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 */
@@ -4698,6 +5501,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -4718,6 +5522,14 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4771,10 +5583,36 @@ select d+1 into res from D where d= NAME
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -4814,6 +5652,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -4825,13 +5664,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 */
@@ -4847,6 +5688,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -4919,10 +5761,36 @@ select @trace;
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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": "D",
+ "field": "d",
+ "cause": "comparison_with_null_always_false"
+ } /* impossible_condition */,
+ "impossible_range": true
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -5022,12 +5890,55 @@ select d into res from D where d in (sel
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "D",
- "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,
@@ -5046,13 +5957,15 @@ select d into res from D where d in (sel
{
"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 */
@@ -5071,21 +5984,23 @@ select d into res from D where d in (sel
{
"considered_execution_plans": [
{
+ "database": "test",
"table": "D",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "index",
"index": "d",
- "cost": 1,
"records": 1,
+ "cost": 1,
"chosen": true
},
{
- "access_type": "table scan",
+ "access_type": "scan",
"cost": 2,
"records": 4,
- "chosen": false
+ "chosen": false,
+ "cause": "index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -5095,13 +6010,15 @@ select d into res from D where d in (sel
] /* 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 */
@@ -5115,13 +6032,15 @@ select d into res from D where d in (sel
"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 */
@@ -5153,13 +6072,15 @@ select d into res from D where d in (sel
] /* 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 */
@@ -5175,13 +6096,14 @@ select d into res from D where d in (sel
{
"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 */
@@ -5193,10 +6115,12 @@ select d into res from D where d in (sel
"original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
"attached_conditions": [
{
+ "database": "test",
"table": "D",
"attached": "(`test`.`D`.`d` = `f1`())"
},
{
+ "database": "test",
"table": "t2",
"attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
}
@@ -5219,6 +6143,14 @@ delete from t1 where id="z" {
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -5243,6 +6175,7 @@ select sum(data) into ret from t1 {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -5254,13 +6187,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 */
@@ -5276,6 +6211,7 @@ select sum(data) into ret from t1 {
"original_condition": null,
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": null
}
@@ -5347,6 +6283,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 2,
@@ -5358,13 +6295,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 */
@@ -5380,6 +6319,7 @@ explain select * from v1 where id="b" {
"original_condition": "(`test`.`t1`.`id` = 'b')",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(`test`.`t1`.`id` = 'b')"
}
@@ -5424,6 +6364,14 @@ delete from v1 where data=100 {
},
{
"expanded_query": "/* select#1 */ select from dual where (`data` = 100)"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -5477,6 +6425,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "test",
"table": "t1",
"table_scan": {
"records": 3,
@@ -5488,13 +6437,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 */
@@ -5510,6 +6461,7 @@ explain select * from v1 where id="b" {
"original_condition": "(`test`.`t1`.`id` < 'c')",
"attached_conditions": [
{
+ "database": "test",
"table": "t1",
"attached": "(`test`.`t1`.`id` < 'c')"
}
@@ -5548,6 +6500,7 @@ explain select * from v1 where id="b" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "",
"table": "v1",
"table_scan": {
"records": 2,
@@ -5559,13 +6512,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 */
@@ -5581,6 +6536,7 @@ explain select * from v1 where id="b" {
"original_condition": "(`v1`.`id` = 'b')",
"attached_conditions": [
{
+ "database": "",
"table": "v1",
"attached": "(`v1`.`id` = 'b')"
}
@@ -5634,6 +6590,7 @@ VARIABLE_NAME="optimizer_trace" {
] /* constant_tables */,
"records_estimation": [
{
+ "database": "information_schema",
"table": "SESSION_VARIABLES",
"table_scan": {
"records": 2,
@@ -5645,13 +6602,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 */
@@ -5667,6 +6626,7 @@ 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')"
}
@@ -5733,6 +6693,7 @@ select TRACE into dumpfile 'MYSQLTEST_VA
],
"records_estimation": [
{
+ "database": "",
"table": "",
"table_scan": {
"records": 2,
@@ -5744,13 +6705,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
}
]
@@ -5766,6 +6729,7 @@ select TRACE into dumpfile 'MYSQLTEST_VA
"original_condition": null,
"attached_conditions": [
{
+ "database": "",
"table": "",
"attached": null
}
=== added file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/optimizer_trace_range.result 2010-10-22 08:31:43 +0000
@@ -0,0 +1,2112 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE t1
+(
+key1 INT NOT NULL,
+INDEX i1(key1)
+);
+Inserting 1024 records into t1
+ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
+ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
+ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
+ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
+ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
+ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
+ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
+UPDATE t1 SET
+key2=key1,
+key3=key1,
+key4=key1,
+key5=key1,
+key6=key1,
+key7=key1,
+key8=1024-key1;
+CREATE TABLE t2 (
+key1a INT NOT NULL,
+key1b INT NOT NULL,
+key2 INT NOT NULL,
+key2_1 INT NOT NULL,
+key2_2 INT NOT NULL,
+key3 INT NOT NULL,
+primary key i1a (key1a, key1b),
+INDEX i1b (key1b, key1a),
+INDEX i2_1(key2, key2_1),
+INDEX i2_2(key2, key2_1)
+);
+INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range i2 i2 4 NULL 47 Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`key2` < 5) or (`key2` > 1020))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "after_equality_propagation": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "after_constant_propagation": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "after_trivial_conditions_removal": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "key2 < 5",
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 47,
+ "cost": 58.41,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 47,
+ "ranges": [
+ "key2 < 5",
+ "1020 < key2"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 47,
+ "cost_of_plan": 58.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 47,
+ "cost": 58.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 58.41,
+ "records_for_plan": 47,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`key1` < 3) or (`key2` > 1020))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "after_equality_propagation": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "after_constant_propagation": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "after_trivial_conditions_removal": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": true,
+ "key_parts": [
+ "key1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "key1 < 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 3,
+ "cost": 1.6526,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i1",
+ "cumulated_cost": 1.6526
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 42,
+ "cost": 10.282,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i2",
+ "cumulated_cost": 11.935
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 11.935,
+ "cost_sort_rowid_and_read_disk": 8.0666,
+ "cost_duplicate_removal": 38.361,
+ "total_cost": 58.363
+ }
+ ] /* analyzing_index_merge */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_merge",
+ "index_merge_of": [
+ {
+ "type": "range_scan",
+ "index": "i1",
+ "records": 3,
+ "ranges": [
+ "key1 < 3"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ] /* ranges */
+ }
+ ] /* index_merge_of */
+ } /* range_access_plan */,
+ "records_in_plan": 45,
+ "cost_of_plan": 58.363,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 45,
+ "cost": 58.363,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 58.363,
+ "records_for_plan": 45,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range NULL i2_1 4 NULL 103 Using index for group-by
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `key2` AS `key2`,min(`key2_1`) AS `MIN(key2_1)` from `test`.`t2` group by `key2`"
+ },
+ {
+ "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": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_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": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "covering": false
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "covering": false
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "key_parts_used_for_access": 1,
+ "records": 103,
+ "cost": 50.6,
+ "ranges": [
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "key_parts_used_for_access": 1,
+ "records": 103,
+ "cost": 50.6,
+ "ranges": [
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 103,
+ "cost_of_plan": 50.6,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 103,
+ "cost": 50.6,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 50.6,
+ "records_for_plan": 103,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range i2_1,i2_2 i2_1 4 NULL 47 Using where; Using index
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `key2` AS `key2`,min(`key2_1`) AS `MIN(key2_1)` from `test`.`t2` where (`key2` < 5) group by `key2`"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t2`.`key2` < 5)",
+ "after_equality_propagation": "(`test`.`t2`.`key2` < 5)",
+ "after_constant_propagation": "(`test`.`t2`.`key2` < 5)",
+ "after_trivial_conditions_removal": "(`test`.`t2`.`key2` < 5)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_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": {
+ "potential_group_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "covering": false
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "covering": false
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "records": 5,
+ "cost": 31
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "records": 5,
+ "cost": 31
+ }
+ ] /* potential_group_range_indices */
+ } /* group_index_range */,
+ "best_group_range_summary": {
+ "type": "index_group",
+ "index": "i2_1",
+ "group_attribute": "key2_1",
+ "min_aggregate": true,
+ "max_aggregate": false,
+ "distinct_aggregate": false,
+ "key_parts_used_for_access": 1,
+ "records": 5,
+ "cost": 31,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "chosen": true
+ } /* best_group_range_summary */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2_1",
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 47,
+ "cost": 11.724,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i2_2",
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 47,
+ "cost": 11.724,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2_1",
+ "records": 47,
+ "ranges": [
+ "key2 < 5"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 47,
+ "cost_of_plan": 11.724,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 5,
+ "cost": 20.124,
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 20.124,
+ "records_for_plan": 5,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t2`.`key2` < 5)",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(`test`.`t2`.`key2` < 5)"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref i2_1,i2_2 i2_1 4 const 10 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t2` where ((`key2` = 1) and ((`key2_1` = 1) or (`key3` = 5)))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))",
+ "after_equality_propagation": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))",
+ "after_constant_propagation": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))",
+ "after_trivial_conditions_removal": "((multiple equal(1, `test`.`t2`.`key2_1`) or multiple equal(5, `test`.`t2`.`key3`)) and multiple equal(1, `test`.`t2`.`key2`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t2.key2= 1",
+ "null_rejecting": false
+ },
+ {
+ "condition": "t2.key2= 1",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_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 */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2_1",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i2_2",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "i2_1",
+ "records": 10,
+ "ranges": [
+ "1 <= key2 <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 10,
+ "cost_of_plan": 13.01,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "i2_1",
+ "records": 10,
+ "cost": 10,
+ "chosen": true
+ },
+ {
+ "access_type": "index",
+ "index": "i2_2",
+ "records": 10,
+ "cost": 10,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "chosen": false,
+ "cause": "heuristic_index_must_be_cheaper"
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`key2` = 10) or (`key3` = 3) or (`key4` <=> NULL))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))",
+ "after_equality_propagation": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))",
+ "after_constant_propagation": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))",
+ "after_trivial_conditions_removal": "(multiple equal(10, `test`.`t1`.`key2`) or multiple equal(3, `test`.`t1`.`key3`) or (`test`.`t1`.`key4` <=> NULL))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": true,
+ "key_parts": [
+ "key3"
+ ] /* key_parts */
+ },
+ {
+ "index": "i4",
+ "usable": true,
+ "key_parts": [
+ "key4"
+ ] /* key_parts */
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "impossible_condition": {
+ "table": "t1",
+ "field": "key4",
+ "cause": "null_field_in_non_null_column"
+ } /* impossible_condition */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i2",
+ "cumulated_cost": 2.21
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i3",
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "index_to_merge": "i3",
+ "cumulated_cost": 4.42
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 4.42,
+ "use_roworder_union": true,
+ "cause": "always_cheaper_than_not_roworder_retrieval",
+ "analyzing_roworder_scans": [
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ },
+ {
+ "type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ }
+ ] /* analyzing_roworder_scans */,
+ "index_roworder_union_cost": 6.327,
+ "members": 2,
+ "chosen": true
+ }
+ ] /* analyzing_index_merge */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_union",
+ "union_of": [
+ {
+ "type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ] /* ranges */
+ }
+ ] /* union_of */
+ } /* range_access_plan */,
+ "records_in_plan": 2,
+ "cost_of_plan": 6.327,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 2,
+ "cost": 6.327,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 6.327,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL i2_1,i2_2 NULL NULL NULL 1024 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t2` where ((`key2_1` < 79) or (`key2` = 2))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))",
+ "after_equality_propagation": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))",
+ "after_constant_propagation": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))",
+ "after_trivial_conditions_removal": "((`test`.`t2`.`key2_1` < 79) or multiple equal(2, `test`.`t2`.`key2`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": false,
+ "cause": "not_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 */,
+ "range_scan_possible": false,
+ "cause": "condition_always_true",
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1024,
+ "cost": 8.25,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 8.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY,i1b PRIMARY 8 NULL 1 Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10 {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t2` where ((`key1a` = 5) and (`key1b` < 10))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))",
+ "after_equality_propagation": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))",
+ "after_constant_propagation": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))",
+ "after_trivial_conditions_removal": "((`test`.`t2`.`key1b` < 10) and multiple equal(5, `test`.`t2`.`key1a`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t2.key1a= 5",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "key1a",
+ "key1b"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1b",
+ "usable": true,
+ "key_parts": [
+ "key1b",
+ "key1a"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2_1",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i2_2",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "5 <= key1a <= 5 : key1b < 10"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i1b",
+ "ranges": [
+ "key1b < 10"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 9,
+ "cost": 11.81,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "records": 1,
+ "ranges": [
+ "5 <= key1a <= 5 : key1b < 10"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 1,
+ "cost_of_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "PRIMARY",
+ "records": 10,
+ "cost": 10.24,
+ "chosen": true
+ },
+ {
+ "access_type": "range",
+ "records": 1,
+ "cost": 2.21,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.21,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 1024 Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2) {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`key1` > 1) or (`key2` > 2))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "after_equality_propagation": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "after_constant_propagation": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "after_trivial_conditions_removal": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": true,
+ "key_parts": [
+ "key1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i2",
+ "usable": true,
+ "key_parts": [
+ "key2"
+ ] /* key_parts */
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "1 < key1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1023,
+ "cost": 227.35,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "2 < key2"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1022,
+ "cost": 227.13,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* indices_to_merge */,
+ "cost_of_reading_ranges": 0,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* analyzing_index_merge */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 1024,
+ "cost": 10.25,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 10.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1,t2;
+CREATE TABLE t1 (
+cola char(3) not null,
+colb char(3) not null,
+filler char(200),
+key(cola),
+key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+Inserting records
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`cola` = 'foo') and (`colb` = 'bar'))"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`cola` = 'foo') and (`test`.`t1`.`colb` = 'bar'))",
+ "after_equality_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
+ "after_constant_propagation": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))",
+ "after_trivial_conditions_removal": "(multiple equal('foo', `test`.`t1`.`cola`) and multiple equal('bar', `test`.`t1`.`colb`))"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t1.cola= 'foo'",
+ "null_rejecting": false
+ },
+ {
+ "condition": "t1.colb= 'bar'",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8704,
+ "cost": 2184.8
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "key_parts": [
+ "cola"
+ ] /* key_parts */
+ },
+ {
+ "index": "colb",
+ "usable": true,
+ "key_parts": [
+ "colb"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "colb",
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 533,
+ "cost": 640.61,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "cola",
+ "used_in_intersect": true,
+ "matching_records_now": 533,
+ "cumulated_cost": 320.38,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "colb",
+ "used_in_intersect": true,
+ "matching_records_now": 32.639,
+ "cumulated_cost": 53.359,
+ "isect_covering_with_this_index": false
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "cpk_added_to_intersect": false,
+ "cause": "no_clustered_pk_index"
+ } /* clustered_pk */,
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 32,
+ "cost": 53.359,
+ "covering": false,
+ "cpk_scan": false,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "cola",
+ "records": 533,
+ "ranges": [
+ "foo <= cola <= foo"
+ ] /* ranges */
+ },
+ {
+ "type": "range_scan",
+ "index": "colb",
+ "records": 533,
+ "ranges": [
+ "bar <= colb <= bar"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_in_plan": 32,
+ "cost_of_plan": 53.359,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "cola",
+ "records": 533,
+ "cost": 533,
+ "chosen": true
+ },
+ {
+ "access_type": "index",
+ "index": "colb",
+ "records": 533,
+ "cost": 533,
+ "chosen": false
+ },
+ {
+ "access_type": "range",
+ "records": 24,
+ "cost": 54.959,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 54.959,
+ "records_for_plan": 24,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref cola cola 3 const 1 Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no' {
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where (`cola` = 'f\\no')"
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t1`.`cola` = 'f\\no')",
+ "after_equality_propagation": "multiple equal('f\\no', `test`.`t1`.`cola`)",
+ "after_constant_propagation": "multiple equal('f\\no', `test`.`t1`.`cola`)",
+ "after_trivial_conditions_removal": "multiple equal('f\\no', `test`.`t1`.`cola`)"
+ } /* condition_processing */
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t1.cola= 'f\\no'",
+ "null_rejecting": false
+ }
+ ] /* ref-optimizer-key-uses */
+ },
+ {
+ "constant_tables": [
+ ] /* constant_tables */,
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8704,
+ "cost": 2184.8
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "usable": true,
+ "key_parts": [
+ "cola"
+ ] /* key_parts */
+ },
+ {
+ "index": "colb",
+ "usable": false,
+ "cause": "not_usable_by_conditions"
+ }
+ ] /* potential_range_indices */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "f
+o <= cola <= f
+o"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "cola",
+ "records": 1,
+ "ranges": [
+ "f
+o <= cola <= f
+o"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_in_plan": 1,
+ "cost_of_plan": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "cola",
+ "records": 1,
+ "cost": 1,
+ "chosen": true
+ },
+ {
+ "access_type": "range",
+ "chosen": false,
+ "cause": "heuristic_index_must_be_cheaper"
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(`test`.`t1`.`cola` = 'f\\no')",
+ "attached_conditions": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(`test`.`t1`.`cola` = 'f\\no')"
+ }
+ ] /* attached_conditions */
+ } /* attaching_conditions_to_tables */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ }
+ ] /* steps */
+} 0 0
+DROP TABLE t1;
+set optimizer_trace=default;
=== modified file 'mysql-test/t/optimizer_trace_charset.test'
--- a/mysql-test/t/optimizer_trace_charset.test 2010-10-09 15:04:30 +0000
+++ b/mysql-test/t/optimizer_trace_charset.test 2010-10-22 08:31:43 +0000
@@ -32,4 +32,11 @@ select @query, @trace;
drop table t1;
drop view v1;
+
+create table t1(c char(4) primary key);
+insert into t1 values ('aaa'), ('��');
+select * from t1 where c < '���;
+select query, trace from information_schema.OPTIMIZER_TRACE;
+drop table t1;
+
set optimizer_trace=default;
=== added file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/optimizer_trace_range.test 2010-10-22 08:31:43 +0000
@@ -0,0 +1,158 @@
+# Basic test for optimizer trace
+
+--source include/have_optimizer_trace.inc
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE t1
+(
+ key1 INT NOT NULL,
+ INDEX i1(key1)
+);
+
+--echo Inserting 1024 records into t1
+
+--disable_query_log
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+
+let $1=7;
+set @d=8;
+while ($1)
+{
+ EVAL INSERT INTO t1 SELECT key1+@d FROM t1;
+ EVAL SET @d=@d*2;
+ DEC $1;
+}
+--enable_query_log
+
+ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
+ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
+ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
+ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
+ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
+ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
+ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
+
+UPDATE t1 SET
+ key2=key1,
+ key3=key1,
+ key4=key1,
+ key5=key1,
+ key6=key1,
+ key7=key1,
+ key8=1024-key1;
+
+CREATE TABLE t2 (
+ key1a INT NOT NULL,
+ key1b INT NOT NULL,
+ key2 INT NOT NULL,
+ key2_1 INT NOT NULL,
+ key2_2 INT NOT NULL,
+ key3 INT NOT NULL,
+ primary key i1a (key1a, key1b),
+ INDEX i1b (key1b, key1a),
+ INDEX i2_1(key2, key2_1),
+ INDEX i2_2(key2, key2_1)
+);
+
+INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
+
+# multiple ranges on one key
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+
+# index merge
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# group without range
+--echo
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# group with range
+--echo
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+#intersect
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# union
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# range_scan_possible=false
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Multiple key parts in same index
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# more_expensive_than_table_scan
+--echo
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (
+ cola char(3) not null,
+ colb char(3) not null,
+ filler char(200),
+ key(cola),
+ key(colb)
+);
+INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
+
+--echo Inserting records
+--disable_query_log
+let $1=9;
+while ($1)
+{
+ eval INSERT INTO t1 SELECT * FROM t1 WHERE cola = 'foo';
+ dec $1;
+}
+LET $1=13;
+WHILE ($1)
+{
+ eval INSERT INTO t1 SELECT * FROM t1 WHERE cola <> 'foo';
+ dec $1;
+}
+
+--enable_query_log
+
+--echo
+# Index roworder intersect
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+--echo
+# Range with escaped character should be printed escaped
+EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP TABLE t1;
+
+set optimizer_trace=default;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2010-09-27 09:26:39 +0000
+++ b/sql/handler.cc 2010-10-22 08:31:43 +0000
@@ -4381,14 +4381,7 @@ handler::multi_range_read_info_const(uin
*bufsz= 0;
seq_it= seq->init(seq_init_param, n_ranges, *flags);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- /*
- Given that this method can be overridden by engines, it doesn't sound
- smart to do tracing here. seq->next() produces tracing when it's equal to
- sel_arg_range_seq_next() (prints the range).
- */
Opt_trace_array ota(thd->opt_trace, "ranges");
-#endif
while (!seq->next(seq_it, &range))
{
if (unlikely(thd->killed != 0))
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-09-27 09:26:39 +0000
+++ b/sql/opt_range.cc 2010-10-22 08:31:43 +0000
@@ -30,7 +30,8 @@
and builds lists of intervals (in index/partitioning space), such that
all possible records that match the condition are contained within the
intervals.
- The entry point for the range analysis module is get_mm_tree() function.
+ The entry point for the range analysis module is get_mm_tree()
+ (mm=min_max) function.
The lists are returned in form of complicated structure of interlinked
SEL_TREE/SEL_IMERGE/SEL_ARG objects.
@@ -756,7 +757,7 @@ class TABLE_READ_PLAN;
class TRP_RANGE;
class TRP_ROR_INTERSECT;
class TRP_ROR_UNION;
- class TRP_ROR_INDEX_MERGE;
+ class TRP_INDEX_MERGE;
class TRP_GROUP_MIN_MAX;
struct st_ror_scan_info;
@@ -803,13 +804,12 @@ static void print_ror_scans_arr(TABLE *t
struct st_ror_scan_info **end);
#endif
#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg);
-#endif
-static void print_range(Opt_trace_context *out, KEY_PART_INFO *key_parts,
- const uchar *min_key, uint16 min_length,
- const uchar *max_key, uint16 max_length,
- uint flag);
+static void append_range(String *out,
+ const KEY_PART_INFO *key_parts,
+ const uchar *min_key, const uint16 min_length,
+ const uchar *max_key, const uint16 max_length,
+ const uint flag);
#endif
static SEL_TREE *tree_and(RANGE_OPT_PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2);
@@ -1956,12 +1956,68 @@ public:
static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ }
virtual ~TABLE_READ_PLAN() {} /* Remove gcc warning */
+#ifdef OPTIMIZER_TRACE
+ /**
+ Add basic info for this TABLE_READ_PLAN to the optimizer trace.
+
+ @param param Parameters for range analysis of this table
+ @param trace_object The optimizer trace object the info is appended to
+ */
+ virtual void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace_object) const =0;
+#endif
};
-class TRP_ROR_INTERSECT;
-class TRP_ROR_UNION;
-class TRP_INDEX_MERGE;
+/**
+ Print a key into a stream
+ @param[out] out String the key is appended to
+ @param[in] key_part Index components description
+ @param[in] key Key tuple
+ @param[in] used_length Key tuple length
+*/
+static void
+print_key2(String *out, const KEY_PART_INFO *key_part, const uchar *key,
+ uint used_length)
+{
+ const uchar *key_end= key + used_length;
+ String tmp;
+ uint store_length;
+ TABLE *table= key_part->field->table;
+ my_bitmap_map *old_sets[2];
+
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+
+ for (; key < key_end; key+= store_length, key_part++)
+ {
+ Field *field= key_part->field;
+ store_length= key_part->store_length;
+
+ 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
+ */
+ if (*key)
+ {
+ out->append(STRING_WITH_LEN("NULL"));
+ continue;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void) field->val_int_as_str(&tmp, 1);
+ else
+ field->val_str(&tmp);
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
+ if (key + store_length < key_end)
+ out->append('\'');
+ }
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+}
/*
Plan for a QUICK_RANGE_SELECT scan.
@@ -1996,8 +2052,65 @@ public:
}
DBUG_RETURN(quick);
}
+
+#ifdef OPTIMIZER_TRACE
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ DBUG_ASSERT(param->using_real_indexes);
+ uint keynr_in_table= param->real_keynr[key_idx];
+
+ 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").
+ add_str("index", cur_key.name, strlen(cur_key.name)).
+ add("records", records);
+
+ Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
+ for (const SEL_ARG *current= key;
+ current;
+ current= current->next)
+ {
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ for (const SEL_ARG *part= current;
+ part;
+ part= part->next_key_part)
+ {
+ const KEY_PART_INFO *cur_key_part= key_part + part->part;
+ append_range(&range_info, cur_key_part,
+ part->min_value, cur_key_part->length,
+ part->max_value, cur_key_part->length,
+ part->min_flag | part->max_flag);
+ }
+ trace_range.add_str(range_info.ptr(), range_info.length());
+ }
+ }
+#endif
};
+typedef struct st_ror_scan_info
+{
+ uint idx; ///< # of used key in param->keys
+ uint keynr; ///< # of used key in table
+ ha_rows records; ///< estimate of # records this scan will return
+
+ /** Set of intervals over key fields that will be used for row retrieval. */
+ SEL_ARG *sel_arg;
+
+ /** Fields used in the query and covered by this ROR scan. */
+ MY_BITMAP covered_fields;
+ uint used_fields_covered; ///< # of set bits in covered_fields
+ int key_rec_length; ///< length of key record (including rowid)
+
+ /**
+ Cost of reading all index records with values in sel_arg intervals set
+ (assuming there is no need to access full table records)
+ */
+ double index_read_cost;
+ uint first_uncovered_field; ///< first unused bit in covered_fields
+ uint key_components; ///< # of parts in the key
+} ROR_SCAN_INFO;
/* Plan for QUICK_ROR_INTERSECT_SELECT scan. */
@@ -2015,6 +2128,52 @@ public:
struct st_ror_scan_info *cpk_scan; /* Clustered PK scan, if there is one */
bool is_covering; /* TRUE if no row retrieval phase is necessary */
double index_scan_costs; /* SUM(cost(index_scan)) */
+
+#ifdef OPTIMIZER_TRACE
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add_str("type", "index_roworder_intersect").
+ add("records", records).
+ add("cost", read_cost).
+ add("covering", is_covering).
+ add("cpk_scan", cpk_scan != NULL);
+
+ Opt_trace_context *trace_ctx= param->thd->opt_trace;
+ Opt_trace_array ota(trace_ctx,"intersect_of");
+ for (st_ror_scan_info **cur_scan= first_scan;
+ cur_scan != last_scan;
+ cur_scan++)
+ {
+ const KEY cur_key= param->table->key_info[(*cur_scan)->keynr];
+ const KEY_PART_INFO *key_part= cur_key.key_part;
+
+ Opt_trace_object trace_isect_idx(trace_ctx);
+ trace_isect_idx.add_str("type", "range_scan").
+ add_str("index", cur_key.name, strlen(cur_key.name)).
+ add("records", (*cur_scan)->records);
+
+ Opt_trace_array trace_range(trace_ctx, "ranges");
+ for (const SEL_ARG *current= (*cur_scan)->sel_arg;
+ current;
+ current= current->next)
+ {
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ for (const SEL_ARG *part= current;
+ part;
+ part= part->next_key_part)
+ {
+ const KEY_PART_INFO *cur_key_part= key_part + part->part;
+ append_range(&range_info, cur_key_part,
+ part->min_value, cur_key_part->length,
+ part->max_value, cur_key_part->length,
+ part->min_flag | part->max_flag);
+ }
+ trace_range.add_str(range_info.ptr(), range_info.length());
+ }
+ }
+ }
+#endif
};
@@ -2033,6 +2192,21 @@ public:
MEM_ROOT *parent_alloc);
TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
TABLE_READ_PLAN **last_ror; /* end of the above array */
+
+#ifdef OPTIMIZER_TRACE
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->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;
+ current++)
+ {
+ Opt_trace_object trp_info(param->thd->opt_trace);
+ (*current)->trace_basic_info(param, &trp_info);
+ }
+ }
+#endif
};
@@ -2051,6 +2225,21 @@ public:
MEM_ROOT *parent_alloc);
TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
TRP_RANGE **range_scans_end; /* end of the array */
+
+#ifdef OPTIMIZER_TRACE
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->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;
+ current++)
+ {
+ Opt_trace_object trp_info(param->thd->opt_trace);
+ (*current)->trace_basic_info(param, &trp_info);
+ }
+ }
+#endif
};
@@ -2061,23 +2250,71 @@ public:
class TRP_GROUP_MIN_MAX : public TABLE_READ_PLAN
{
private:
- bool have_min, have_max, have_agg_distinct;
- KEY_PART_INFO *min_max_arg_part;
- uint group_prefix_len;
- uint used_key_parts;
- uint group_key_parts;
- KEY *index_info;
- uint index;
- uint key_infix_len;
- uchar key_infix[MAX_KEY_LENGTH];
- SEL_TREE *range_tree; /* Represents all range predicates in the query. */
- SEL_ARG *index_tree; /* The SEL_ARG sub-tree corresponding to index_info. */
- uint param_idx; /* Index of used key in param->key. */
- bool is_index_scan; /* Use index_next() instead of random read */
+ bool have_min; ///< TRUE if there is a MIN function
+ bool have_max; ///< TRUE if there is a MAX function
+ /** TRUE if there is an aggregate distinct function, e.g.
+ "COUNT(DISTINCT x)"
+ */
+ bool have_agg_distinct;
+ /** The key_part of the only field used by all MIN/MAX functions.
+ Note that TRP_GROUP_MIN_MAX is not used if there are MIN/MAX
+ functions on more than one field.
+ */
+ KEY_PART_INFO *min_max_arg_part;
+ uint group_prefix_len; ///< Length of all key parts in the group prefix
+ uint used_key_parts; ///< Number of index key parts used for access
+ uint group_key_parts; ///< Number of index key parts in the group prefix
+ KEY *index_info; ///< The index chosen for data access
+ uint index; ///< The id of the chosen index
+ uchar key_infix[MAX_KEY_LENGTH]; ///< Constants from equality predicates
+ uint key_infix_len; ///< Length of key_infix
+ SEL_TREE *range_tree; ///< Represents all range predicates in the query
+ SEL_ARG *index_tree; ///< The SEL_ARG sub-tree corresponding to index_info
+ uint param_idx; ///< Index of used key in param->key
+ bool is_index_scan; ///< Use index_next() instead of random read
public:
- /* Number of records selected by the ranges in index_tree. */
+ /** Number of records selected by the ranges in index_tree. */
ha_rows quick_prefix_records;
public:
+
+#ifdef OPTIMIZER_TRACE
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add_str("type", "index_group").
+ add_str("index", index_info->name, strlen(index_info->name)).
+ add_str("group_attribute", min_max_arg_part->field->field_name,
+ strlen(min_max_arg_part->field->field_name)).
+ add("min_aggregate", have_min).
+ add("max_aggregate", have_max).
+ add("distinct_aggregate", have_agg_distinct).
+ add("key_parts_used_for_access", used_key_parts).
+ add("records", records).
+ add("cost", read_cost);
+
+ const KEY_PART_INFO *key_part= index_info->key_part;
+
+ Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
+ for (const SEL_ARG *current= index_tree;
+ current;
+ current= current->next)
+ {
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ for (const SEL_ARG *part= current;
+ part;
+ part= part->next_key_part)
+ {
+ const KEY_PART_INFO *cur_key_part= key_part + part->part;
+ append_range(&range_info, cur_key_part,
+ part->min_value, cur_key_part->length,
+ part->max_value, cur_key_part->length,
+ part->min_flag | part->max_flag);
+ }
+ trace_range.add_str(range_info.ptr(), range_info.length());
+ }
+ }
+#endif
+
TRP_GROUP_MIN_MAX(bool have_min_arg, bool have_max_arg,
bool have_agg_distinct_arg,
KEY_PART_INFO *min_max_arg_part_arg,
@@ -2246,12 +2483,10 @@ int SQL_SELECT::test_quick_select(THD *t
else if (read_time <= 2.0 && !force_quick_range)
DBUG_RETURN(0); /* No need for quick select */
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto(thd->opt_trace, "range_analysis");
+ Opt_trace_object trace_range(thd->opt_trace, "range_analysis");
Opt_trace_object(thd->opt_trace, "table_scan").
- add(head->file->stats.records, "records").
- add(read_time, "cost");
-#endif
+ add("records", head->file->stats.records).
+ add("cost", read_time);
keys_to_use.intersect(head->keys_in_use_for_query);
if (!keys_to_use.is_clear_all())
@@ -2302,9 +2537,9 @@ int SQL_SELECT::test_quick_select(THD *t
thd->mem_root= &alloc;
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto1(thd->opt_trace, "indices");
-#endif
+ Opt_trace_array trace_idx(thd->opt_trace,
+ "potential_range_indices",
+ Opt_trace_context::RANGE_OPTIMIZER);
/*
Make an array with description of all key parts of all table keys.
This is used in get_mm_parts function.
@@ -2312,17 +2547,28 @@ int SQL_SELECT::test_quick_select(THD *t
key_info= head->key_info;
for (idx=0 ; idx < head->s->keys ; idx++, key_info++)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto1.add(key_info->name, "index");
-#endif
+ Opt_trace_object trace_idx_details(thd->opt_trace);
+ trace_idx_details.add_str("index",
+ key_info->name, strlen(key_info->name));
KEY_PART_INFO *key_part_info;
if (!keys_to_use.is_set(idx))
+ {
+ trace_idx_details.add("usable", false).
+ add_str("cause", "not_usable_by_conditions");
continue;
+ }
if (key_info->flags & HA_FULLTEXT)
+ {
+ trace_idx_details.add("usable", false).
+ add_str("cause", "fulltext");
continue; // ToDo: ft-keys in non-ft ranges, if possible SerG
+ }
+
+ trace_idx_details.add("usable", true);
param.key[param.keys]=key_parts;
key_part_info= key_info->key_part;
+ Opt_trace_array trace_keypart(thd->opt_trace, "key_parts");
for (uint part=0 ; part < key_info->key_parts ;
part++, key_parts++, key_part_info++)
{
@@ -2336,6 +2582,8 @@ int SQL_SELECT::test_quick_select(THD *t
(key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
/* Only HA_PART_KEY_SEG is used */
key_parts->flag= (uint8) key_part_info->key_part_flag;
+ trace_keypart.add_str(key_parts->field->field_name,
+ strlen(key_parts->field->field_name));
}
param.real_keynr[param.keys++]=idx;
}
@@ -2359,12 +2607,15 @@ int SQL_SELECT::test_quick_select(THD *t
chosen= true;
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object(thd->opt_trace, "best_covering_scan").
- add(head->key_info[key_for_use].name, "index").
- add(key_read_time, "cost").
- add(chosen, "chosen");
-#endif
+ Opt_trace_object trace_cov(thd->opt_trace,
+ "best_covering_index_scan",
+ Opt_trace_context::RANGE_OPTIMIZER);
+ trace_cov.add_str("index", head->key_info[key_for_use].name,
+ strlen(head->key_info[key_for_use].name)).
+ add("cost", key_read_time).
+ add("chosen", chosen);
+ if (!chosen)
+ trace_cov.add_str("cause", "cost");
}
TABLE_READ_PLAN *best_trp= NULL;
@@ -2377,6 +2628,7 @@ int SQL_SELECT::test_quick_select(THD *t
{
if (tree->type == SEL_TREE::IMPOSSIBLE)
{
+ trace_range.add("impossible_range", true);
records=0L; /* Return -1 from this function. */
read_time= (double) HA_POS_ERROR;
goto free_mem;
@@ -2386,7 +2638,14 @@ int SQL_SELECT::test_quick_select(THD *t
can construct a group-min-max quick select
*/
if (tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER)
+ {
+ DBUG_ASSERT(tree->type == SEL_TREE::ALWAYS); //anything else possible?
+ trace_range.add("range_scan_possible", false);
+ if (tree->type == SEL_TREE::ALWAYS)
+ trace_range.add_str("cause", "condition_always_true");
+
tree= NULL;
+ }
}
}
@@ -2399,11 +2658,20 @@ int SQL_SELECT::test_quick_select(THD *t
{
param.table->quick_condition_rows= min(group_trp->records,
head->file->stats.records);
+ Opt_trace_object grp_summary(thd->opt_trace,
+ "best_group_range_summary",
+ Opt_trace_context::RANGE_OPTIMIZER);
+#ifdef OPTIMIZER_TRACE
+ group_trp->trace_basic_info(¶m, &grp_summary);
+#endif
if (group_trp->read_cost < best_read_time)
{
+ grp_summary.add("chosen", true);
best_trp= group_trp;
best_read_time= best_trp->read_cost;
}
+ else
+ grp_summary.add("chosen", false).add_str("cause", "cost");
}
if (tree)
@@ -2414,6 +2682,9 @@ int SQL_SELECT::test_quick_select(THD *t
*/
if (tree->merges.is_empty())
{
+ Opt_trace_object trace_range(thd->opt_trace,
+ "analyzing_range_alternatives",
+ Opt_trace_context::RANGE_OPTIMIZER);
TRP_RANGE *range_trp;
TRP_ROR_INTERSECT *rori_trp;
bool can_build_covering= FALSE;
@@ -2450,7 +2721,10 @@ int SQL_SELECT::test_quick_select(THD *t
if (!rori_trp->is_covering && can_build_covering &&
(rori_trp= get_best_covering_ror_intersect(¶m, tree,
best_read_time)))
+ {
+ trace_range.add("made_roworder_intersect_covering", true);
best_trp= rori_trp;
+ }
}
}
}
@@ -2465,18 +2739,27 @@ 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);
- 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))
- best_conj_trp= new_conj_trp;
+ Opt_trace_array trace_idx_merge(thd->opt_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))
+ {
+ best_conj_trp= new_conj_trp;
+ }
+ }
+ if (best_conj_trp)
+ best_trp= best_conj_trp;
}
- if (best_conj_trp)
- best_trp= best_conj_trp;
}
}
}
@@ -2494,18 +2777,26 @@ int SQL_SELECT::test_quick_select(THD *t
}
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto.add(param.table->quick_condition_rows, "quick_condition_rows");
-#endif
- free_mem:
+free_mem:
+ if (quick)
+ {
+ Opt_trace_object trace_range_summary(thd->opt_trace,
+ "chosen_range_access_summary");
+ {
+ Opt_trace_object trace_range_plan(thd->opt_trace,
+ "range_access_plan");
+ best_trp->trace_basic_info(¶m, &trace_range_plan);
+
+ }
+ trace_range_summary.add("records_in_plan", quick->records).
+ add("cost_of_plan", quick->read_time).
+ add("chosen", true);
+ }
+
free_root(&alloc,MYF(0)); // Return memory & allocator
thd->mem_root= param.old_root;
thd->no_errors=0;
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto.add(records, "records");
- OPT_TRACE2(thd->opt_trace, print_quick(quick, &needed_reg));
-#endif
/*
Assume that if the user is using 'limit' we will only need to scan
@@ -3837,49 +4128,63 @@ TABLE_READ_PLAN *get_best_disjunct_quick
DBUG_ENTER("get_best_disjunct_quick");
DBUG_PRINT("info", ("Full table scan cost: %g", read_time));
+ Opt_trace_context * const trace= param->thd->opt_trace;
+ Opt_trace_object trace_best_disjunct(trace);
if (!(range_scans= (TRP_RANGE**)alloc_root(param->mem_root,
sizeof(TRP_RANGE*)*
n_child_scans)))
DBUG_RETURN(NULL);
- /*
- Collect best 'range' scan for each of disjuncts, and, while doing so,
- analyze possibility of ROR scans. Also calculate some values needed by
- other parts of the code.
- */
- for (ptree= imerge->trees, cur_child= range_scans;
- ptree != imerge->trees_next;
- ptree++, cur_child++)
- {
- DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
- "tree in SEL_IMERGE"););
- if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
- {
- /*
- One of index scans in this index_merge is more expensive than entire
- table read for another available option. The entire index_merge (and
- any possible ROR-union) will be more expensive then, too. We continue
- here only to update SQL_SELECT members.
- */
- imerge_too_expensive= TRUE;
- }
- if (imerge_too_expensive)
- continue;
+ {
+ /*
+ Collect best 'range' scan for each of disjuncts, and, while doing so,
+ analyze possibility of ROR scans. Also calculate some values needed by
+ other parts of the code.
+ */
+ Opt_trace_array ota(trace,"indices_to_merge");
+ for (ptree= imerge->trees, cur_child= range_scans;
+ ptree != imerge->trees_next;
+ ptree++, cur_child++)
+ {
+ DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
+ "tree in SEL_IMERGE"););
+ Opt_trace_object trace_idx(trace);
+ if (!(*cur_child=
+ get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
+ {
+ /*
+ One of index scans in this index_merge is more expensive than entire
+ table read for another available option. The entire index_merge (and
+ any possible ROR-union) will be more expensive then, too. We continue
+ here only to update SQL_SELECT members.
+ */
+ imerge_too_expensive= TRUE;
+ }
+ if (imerge_too_expensive)
+ {
+ trace_idx.add("chosen", false).add_str("cause", "cost");
+ continue;
+ }
- imerge_cost += (*cur_child)->read_cost;
- all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
- all_scans_rors &= (*cur_child)->is_ror;
- if (pk_is_clustered &&
- param->real_keynr[(*cur_child)->key_idx] ==
- param->table->s->primary_key)
- {
- cpk_scan= cur_child;
- cpk_scan_records= (*cur_child)->records;
+ uint keynr_in_table= param->real_keynr[(*cur_child)->key_idx];
+ imerge_cost += (*cur_child)->read_cost;
+ all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
+ all_scans_rors &= (*cur_child)->is_ror;
+ if (pk_is_clustered &&
+ keynr_in_table == param->table->s->primary_key)
+ {
+ cpk_scan= cur_child;
+ cpk_scan_records= (*cur_child)->records;
+ }
+ else
+ non_cpk_scan_records += (*cur_child)->records;
+
+ trace_idx.
+ add_str("index_to_merge", param->table->key_info[keynr_in_table].name,
+ strlen(param->table->key_info[keynr_in_table].name)).
+ add("cumulated_cost", imerge_cost);
}
- else
- non_cpk_scan_records += (*cur_child)->records;
}
-
- DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost));
+ trace_best_disjunct.add("cost_of_reading_ranges", imerge_cost);
if (imerge_too_expensive || (imerge_cost > read_time) ||
((non_cpk_scan_records+cpk_scan_records >= param->table->file->stats.records) &&
read_time != DBL_MAX))
@@ -3890,6 +4195,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
*/
DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than "
"full table scan, bailing out"));
+ trace_best_disjunct.add("chosen", false).
+ add_str("cause", "cost");
DBUG_RETURN(NULL);
}
@@ -3902,6 +4209,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_UNION))
{
roru_read_plans= (TABLE_READ_PLAN**)range_scans;
+ trace_best_disjunct.add("use_roworder_union", true).
+ add_str("cause", "always_cheaper_than_not_roworder_retrieval");
goto skip_to_ror_scan;
}
@@ -3911,7 +4220,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)
*/
+
imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+ trace_best_disjunct.add("cost_of_mapping_rowid_in_non_cpk_scan",
+ non_cpk_scan_records / TIME_FOR_COMPARE_ROWID);
}
/* Calculate cost(rowid_to_row_scan) */
@@ -3922,12 +4234,16 @@ TABLE_READ_PLAN *get_best_disjunct_quick
get_sweep_read_cost(param->table, non_cpk_scan_records, is_interrupted,
&sweep_cost);
imerge_cost += sweep_cost.total_cost();
+ trace_best_disjunct.add("cost_sort_rowid_and_read_disk",
+ sweep_cost.total_cost());
}
DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g",
imerge_cost));
if (imerge_cost > read_time ||
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
{
+ trace_best_disjunct.add("use_roworder_index_merge", true).
+ add_str("cause", "cost");
goto build_ror_index_merge;
}
@@ -3944,12 +4260,19 @@ TABLE_READ_PLAN *get_best_disjunct_quick
param->imerge_cost_buff_size= unique_calc_buff_size;
}
- imerge_cost +=
- Unique::get_use_cost(param->imerge_cost_buff, (uint)non_cpk_scan_records,
- param->table->file->ref_length,
- param->thd->variables.sortbuff_size);
- DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)",
- imerge_cost, read_time));
+ {
+ double dup_removal_cost=
+ Unique::get_use_cost(param->imerge_cost_buff,
+ (uint)non_cpk_scan_records,
+ param->table->file->ref_length,
+ param->thd->variables.sortbuff_size);
+
+ trace_best_disjunct.add("cost_duplicate_removal", dup_removal_cost);
+ imerge_cost += dup_removal_cost;
+ trace_best_disjunct.add("total_cost", imerge_cost);
+ DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)",
+ imerge_cost, read_time));
+ }
if (imerge_cost < read_time)
{
if ((imerge_trp= new (param->mem_root)TRP_INDEX_MERGE))
@@ -3983,44 +4306,52 @@ skip_to_ror_scan:
cur_roru_plan= roru_read_plans;
/* 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++)
+
{
- /*
- 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)
+ 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++)
{
- /* 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;
+ 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
- DBUG_RETURN(imerge_trp);
- roru_index_costs += (*cur_roru_plan)->read_cost;
+ 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;
+ }
+ 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;
}
- 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;
}
/*
@@ -4052,13 +4383,14 @@ skip_to_ror_scan:
sweep_cost.total_cost();
}
- DBUG_PRINT("info", ("ROR-union: cost %g, %d members", roru_total_cost,
- n_child_scans));
+ trace_best_disjunct.add("index_roworder_union_cost", roru_total_cost).
+ add("members", n_child_scans);
TRP_ROR_UNION* roru;
if (roru_total_cost < read_time)
{
if ((roru= new (param->mem_root) TRP_ROR_UNION))
{
+ trace_best_disjunct.add("chosen", true);
roru->first_ror= roru_read_plans;
roru->last_ror= roru_read_plans + n_child_scans;
roru->read_cost= roru_total_cost;
@@ -4066,34 +4398,12 @@ skip_to_ror_scan:
DBUG_RETURN(roru);
}
}
+ trace_best_disjunct.add("chosen", false);
+
DBUG_RETURN(imerge_trp);
}
-typedef struct st_ror_scan_info
-{
- uint idx; /* # of used key in param->keys */
- uint keynr; /* # of used key in table */
- ha_rows records; /* estimate of # records this scan will return */
-
- /* Set of intervals over key fields that will be used for row retrieval. */
- SEL_ARG *sel_arg;
-
- /* Fields used in the query and covered by this ROR scan. */
- MY_BITMAP covered_fields;
- uint used_fields_covered; /* # of set bits in covered_fields */
- int key_rec_length; /* length of key record (including rowid) */
-
- /*
- Cost of reading all index records with values in sel_arg intervals set
- (assuming there is no need to access full table records)
- */
- double index_read_cost;
- uint first_uncovered_field; /* first unused bit in covered_fields */
- uint key_components; /* # of parts in the key */
-} ROR_SCAN_INFO;
-
-
/*
Create ROR_SCAN_INFO* structure with a single ROR scan on index idx using
sel_arg set of intervals.
@@ -4614,9 +4924,19 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
double min_cost= DBL_MAX;
DBUG_ENTER("get_best_ror_intersect");
+ Opt_trace_object trace_ror(param->thd->opt_trace,
+ "analyzing_roworder_intersect");
+
if ((tree->n_ror_scans < 2) || !param->table->file->stats.records ||
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
+ {
+ if (tree->n_ror_scans < 2)
+ trace_ror.add("usable", false).
+ add_str("cause", "too_few_roworder_scans");
+ else
+ trace_ror.add("usable", false).add("done_tracing", false);
DBUG_RETURN(NULL);
+ }
/*
Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of
@@ -4683,28 +5003,42 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
ROR_SCAN_INFO **intersect_scans_best;
cur_ror_scan= tree->ror_scans;
intersect_scans_best= intersect_scans;
- while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
{
- /* S= S + first(R); R= R - first(R); */
- if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
+ Opt_trace_array ota(param->thd->opt_trace, "intersecting_indices");
+ while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
{
- cur_ror_scan++;
- continue;
- }
+ 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("used_in_intersect", false).
+ add_str("cause", "does_not_reduce_cost_of_intersect");
+ cur_ror_scan++;
+ continue;
+ }
- *(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("used_in_intersect", 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;
+ }
}
}
if (intersect_scans_best == intersect_scans)
{
+ trace_ror.add("increases_selectivity", false).add("chosen", false);
DBUG_PRINT("info", ("None of scans increase selectivity"));
DBUG_RETURN(NULL);
}
@@ -4728,9 +5062,22 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
if (ror_intersect_add(intersect, cpk_scan, TRUE) &&
(intersect->total_cost < min_cost))
{
+ Opt_trace_object (param->thd->opt_trace, "clustered_pk").
+ add("cpk_scan_added_to_intersect", true).
+ add("cumulated_cost", intersect->total_cost);
cpk_scan_used= TRUE;
intersect_best= intersect; //just set pointer here
}
+ else
+ Opt_trace_object (param->thd->opt_trace, "clustered_pk").
+ add("cpk_added_to_intersect", false).add_str("cause", "cost");
+ }
+ else
+ {
+ Opt_trace_object trace_cpk(param->thd->opt_trace, "clustered_pk");
+ trace_cpk.add("cpk_added_to_intersect", false);
+ cpk_scan ? trace_cpk.add_str("cause", "ror_is_covering")
+ : trace_cpk.add_str("cause", "no_clustered_pk_index");
}
/* Ok, return ROR-intersect plan if we have found one */
@@ -4755,10 +5102,18 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
trp->records= best_rows;
trp->index_scan_costs= intersect_best->index_scan_costs;
trp->cpk_scan= cpk_scan_used? cpk_scan: NULL;
+
+ trace_ror.add("records", trp->records).
+ add("cost", trp->read_cost).
+ add("covering", trp->is_covering).
+ add("chosen", true);
+
DBUG_PRINT("info", ("Returning non-covering ROR-intersect plan:"
"cost %g, records %lu",
trp->read_cost, (ulong) trp->records));
}
+ else
+ trace_ror.add("chosen", false);
DBUG_RETURN(trp);
}
@@ -4805,6 +5160,12 @@ TRP_ROR_INTERSECT *get_best_covering_ror
ROR_SCAN_INFO **ror_scans_end= tree->ror_scans_end;
DBUG_ENTER("get_best_covering_ror_intersect");
+ // None of our tests enter this function
+ Opt_trace_object (param->thd->opt_trace).
+ add("get_best_covering_roworder_intersect", true).
+ add("untested_code", true).
+ add("need_tracing",true);
+
if (!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
DBUG_RETURN(NULL);
@@ -4964,9 +5325,7 @@ static TRP_RANGE *get_key_scans_params(P
*/
DBUG_EXECUTE("info", print_sel_tree(param, tree, &tree->keys_map,
"tree scans"););
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto1(param->thd->opt_trace, "index_scans_parameters");
-#endif
+ Opt_trace_array ota(param->thd->opt_trace, "range_scan_alternatives");
tree->ror_scans_map.clear_all();
tree->n_ror_scans= 0;
@@ -4986,42 +5345,39 @@ static TRP_RANGE *get_key_scans_params(P
bool read_index_only= index_read_must_be_used ? TRUE :
(bool) param->table->covering_keys.is_set(keynr);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto2(param->thd->opt_trace, "range_scan");
- oto2.add(param->table->key_info[keynr].name, "index");
-#endif
+ Opt_trace_object trace_idx(param->thd->opt_trace);
+ trace_idx.add_str("index", param->table->key_info[keynr].name,
+ strlen(param->table->key_info[keynr].name));
+
found_records= check_quick_select(param, idx, read_index_only, *key,
update_tbl_stats, &mrr_flags,
&buf_size, &cost);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto2.add(read_index_only, "index_only").add(found_records, "records");
-#endif
+ trace_idx.add("index_only", read_index_only).
+ add("records", found_records).
+ add("cost", cost.total_cost());
if ((found_records != HA_POS_ERROR) && param->is_ror_scan)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto2.add(true, "rowid_ordered");
-#endif
+ trace_idx.add("rowid_ordered", true);
tree->n_ror_scans++;
tree->ror_scans_map.set_bit(idx);
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
else
- oto2.add(false, "rowid_ordered");
-#endif
+ trace_idx.add("rowid_ordered", false);
if (found_records != HA_POS_ERROR &&
read_time > (found_read_time= cost.total_cost()))
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto2.add(found_read_time, "cost").add(true, "chosen");
-#endif
+ trace_idx.add("chosen", true);
read_time= found_read_time;
best_records= found_records;
key_to_read= key;
best_mrr_flags= mrr_flags;
best_buf_size= buf_size;
}
+ else
+ trace_idx.add("chosen", false).add_str("cause", "cost");
+
}
}
@@ -5037,10 +5393,6 @@ static TRP_RANGE *get_key_scans_params(P
read_plan->is_ror= tree->ror_scans_map.is_set(idx);
read_plan->read_cost= read_time;
read_plan->mrr_buf_size= best_buf_size;
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto1.add(param->table->key_info[param->real_keynr[idx]].name,
- "index_for_best_range_access");
-#endif
//DBUG_PRINT("info",
// ("Returning range plan for key %s, cost %g, records %lu",
// param->table->key_info[param->real_keynr[idx]].name,
@@ -5100,10 +5452,12 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
"creating ROR-intersect",
first_scan, last_scan););
alloc= parent_alloc? parent_alloc: &quick_intrsect->alloc;
- for (; first_scan != last_scan;++first_scan)
+ for (st_ror_scan_info **current= first_scan;
+ current != last_scan;
+ current++)
{
- if (!(quick= get_quick_select(param, (*first_scan)->idx,
- (*first_scan)->sel_arg,
+ if (!(quick= get_quick_select(param, (*current)->idx,
+ (*current)->sel_arg,
HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED,
0, alloc)) ||
quick_intrsect->push_quick_back(quick))
@@ -6010,6 +6364,12 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
value->result_type() == item_cmp_type(field->result_type(),
value->result_type()))
{
+ Opt_trace_object (param->thd->opt_trace, "impossible_condition",
+ Opt_trace_context::RANGE_OPTIMIZER).
+ add_str("table", *field->table_name, strlen(*field->table_name)).
+ add_str("field", field->field_name, strlen(field->field_name)).
+ add_str("cause", "incomparable_types");
+
tree= new (alloc) SEL_ARG(field, 0, 0);
tree->type= SEL_ARG::IMPOSSIBLE;
field->table->in_use->variables.sql_mode= orig_sql_mode;
@@ -6072,6 +6432,12 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
}
else if (err < 0)
{
+ Opt_trace_object (param->thd->opt_trace, "impossible_condition",
+ Opt_trace_context::RANGE_OPTIMIZER).
+ add_str("table", *field->table_name, strlen(*field->table_name)).
+ add_str("field", field->field_name, strlen(field->field_name)).
+ add_str("cause", "null_field_in_non_null_column");
+
field->table->in_use->variables.sql_mode= orig_sql_mode;
/* This happens when we try to insert a NULL field in a not null column */
tree= &null_element; // cmp with NULL is never TRUE
@@ -6085,6 +6451,11 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
*/
if (type != Item_func::EQUAL_FUNC && field->is_real_null())
{
+ Opt_trace_object (param->thd->opt_trace, "impossible_condition",
+ Opt_trace_context::RANGE_OPTIMIZER).
+ add_str("table", *field->table_name, strlen(*field->table_name)).
+ add_str("field", field->field_name, strlen(field->field_name)).
+ add_str("cause", "comparison_with_null_always_false");
tree= &null_element;
goto end;
}
@@ -6121,6 +6492,11 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
{
if (type == Item_func::LT_FUNC || type == Item_func::LE_FUNC)
{
+ Opt_trace_object (param->thd->opt_trace, "impossible_condition",
+ Opt_trace_context::RANGE_OPTIMIZER).
+ add_str("table", *field->table_name, strlen(*field->table_name)).
+ add_str("field", field->field_name, strlen(field->field_name)).
+ add_str("cause", "unsigned_int_cannot_be_negative");
tree->type= SEL_ARG::IMPOSSIBLE;
goto end;
}
@@ -7632,8 +8008,26 @@ range_seq_t sel_arg_range_seq_init(void
}
-static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
+static void step_down_to(String *s, SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
{
+
+ if (arg->param->thd->opt_trace && 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.
+ */
+ 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,
+ key_tree->max_value, length,
+ key_tree->min_flag | key_tree->max_flag);
+ }
+
RANGE_SEQ_ENTRY *cur= &arg->stack[arg->i+1];
RANGE_SEQ_ENTRY *prev= &arg->stack[arg->i];
@@ -7691,6 +8085,9 @@ uint sel_arg_range_seq_next(range_seq_t
{
SEL_ARG *key_tree;
SEL_ARG_RANGE_SEQ *seq= (SEL_ARG_RANGE_SEQ*)rseq;
+ String key_range_trace;
+ key_range_trace.set_charset(system_charset_info);
+
if (seq->at_start)
{
key_tree= seq->start;
@@ -7706,7 +8103,7 @@ uint sel_arg_range_seq_next(range_seq_t
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->next);
+ step_down_to(&key_range_trace, seq, key_tree->next);
key_tree= key_tree->next;
seq->param->is_ror_scan= FALSE;
goto walk_right_n_up;
@@ -7727,7 +8124,7 @@ uint sel_arg_range_seq_next(range_seq_t
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->next);
+ step_down_to(&key_range_trace, seq, key_tree->next);
key_tree= key_tree->next;
seq->param->is_ror_scan= FALSE;
break;
@@ -7781,7 +8178,7 @@ walk_up_n_right:
/* Step up */
key_tree= key_tree->prev;
}
- step_down_to(seq, key_tree);
+ step_down_to(&key_range_trace, seq, key_tree);
}
/* Ok got a tuple */
@@ -7839,17 +8236,24 @@ walk_up_n_right:
seq->param->is_ror_scan= FALSE;
}
}
- OPT_TRACE2(seq->param->thd->opt_trace,
- print_range(seq->param->thd->opt_trace,
- seq->param->table->key_info[seq->real_keyno].key_part,
- range->start_key.key,
- range->start_key.length,
- range->end_key.key,
- range->end_key.length,
- cur->min_key_flag | cur->max_key_flag));
seq->param->range_count++;
seq->param->max_key_part=max(seq->param->max_key_part,key_tree->part);
+
+ if (key_range_trace.length())
+ {
+ DBUG_ASSERT(seq->param->thd->opt_trace);
+ DBUG_ASSERT(seq->param->thd->opt_trace->is_started());
+ /*
+ Should be Opt_trace_array, but don't cast because if it is
+ Opt_trace_object a cast will crash the server while just
+ add()'ing to Opt_trace_struct will cause a JSON syntax error
+ for the optimizer trace (less severe)
+ */
+ Opt_trace_struct *trace_range=
+ seq->param->thd->opt_trace->get_current_struct();
+ trace_range->add_str(key_range_trace.ptr(), key_range_trace.length());
+ }
return 0;
}
@@ -9722,14 +10126,30 @@ get_best_group_min_max(PARAM *param, SEL
DBUG_ENTER("get_best_group_min_max");
+ Opt_trace_object trace_group(thd->opt_trace, "group_index_range",
+ Opt_trace_context::RANGE_OPTIMIZER);
+
/* Perform few 'cheap' tests whether this access method is applicable. */
if (!join)
+ {
+ trace_group.add("chosen", false).add_str("cause", "no_join");
DBUG_RETURN(NULL); /* This is not a select statement. */
- if ((join->tables != 1) || /* The query must reference one table. */
- (join->select_lex->olap == ROLLUP_TYPE)) /* Check (B3) for ROLLUP */
+ }
+ if (join->tables != 1) /* The query must reference one table. */
+ {
+ trace_group.add("chosen", false).add_str("cause", "not_single_table");
DBUG_RETURN(NULL);
+ }
+ if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
+ {
+ trace_group.add("chosen", false).add_str("cause", "is_rollup");
+ DBUG_RETURN(NULL);
+ }
if (table->s->keys == 0) /* There are no indexes to use. */
+ {
+ trace_group.add("chosen", false).add_str("cause", "no_index");
DBUG_RETURN(NULL);
+ }
/* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/
if (join->make_sum_func_list(join->all_fields, join->fields_list, 1))
@@ -9741,7 +10161,10 @@ get_best_group_min_max(PARAM *param, SEL
if ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
(!join->select_distinct) &&
!is_agg_distinct)
+ {
+ trace_group.add("chosen", false).add_str("cause", "not_group_by_or_distinct");
DBUG_RETURN(NULL);
+ }
/* Analyze the query in more detail. */
if (join->sum_funcs[0])
@@ -9759,7 +10182,11 @@ get_best_group_min_max(PARAM *param, SEL
min_max_item->sum_func() == Item_sum::AVG_DISTINCT_FUNC)
continue;
else
+ {
+ trace_group.add("chosen", false).
+ add_str("cause", "not_applicable_aggregate_function");
DBUG_RETURN(NULL);
+ }
/* The argument of MIN/MAX. */
Item *expr= min_max_item->get_arg(0)->real_item();
@@ -9777,6 +10204,7 @@ get_best_group_min_max(PARAM *param, SEL
/* Check (SA5). */
if (join->select_distinct)
{
+ trace_group.add("is_distinct_query", true);
while ((item= select_items_it++))
{
if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -9788,7 +10216,11 @@ get_best_group_min_max(PARAM *param, SEL
for (tmp_group= join->group_list; tmp_group; tmp_group= tmp_group->next)
{
if ((*tmp_group->item)->real_item()->type() != Item::FIELD_ITEM)
+ {
+ trace_group.add("chosen", false).
+ add_str("cause", "group_field_is_expression");
DBUG_RETURN(NULL);
+ }
}
/*
@@ -9810,289 +10242,334 @@ get_best_group_min_max(PARAM *param, SEL
ha_rows cur_quick_prefix_records= 0;
uint cur_param_idx=MAX_KEY;
- for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
- cur_index_info++, cur_index++)
+
{
- KEY_PART_INFO *cur_part;
- KEY_PART_INFO *end_part; /* Last part for loops. */
- /* Last index part. */
- KEY_PART_INFO *last_part;
- KEY_PART_INFO *first_non_group_part;
- KEY_PART_INFO *first_non_infix_part;
- uint key_infix_parts;
- uint cur_group_key_parts= 0;
- uint cur_group_prefix_len= 0;
- double cur_read_cost;
- ha_rows cur_records;
- key_map used_key_parts_map;
- uint max_key_part= 0;
- uint cur_key_infix_len= 0;
- uchar cur_key_infix[MAX_KEY_LENGTH];
- uint cur_used_key_parts;
+ 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++)
+ {
+ Opt_trace_object trace_idx(thd->opt_trace);
+ trace_idx.add_str("index", cur_index_info->name,
+ strlen(cur_index_info->name));
+ KEY_PART_INFO *cur_part;
+ KEY_PART_INFO *end_part; /* Last part for loops. */
+ /* Last index part. */
+ KEY_PART_INFO *last_part;
+ KEY_PART_INFO *first_non_group_part;
+ KEY_PART_INFO *first_non_infix_part;
+ uint key_infix_parts;
+ uint cur_group_key_parts= 0;
+ uint cur_group_prefix_len= 0;
+ double cur_read_cost;
+ ha_rows cur_records;
+ key_map used_key_parts_map;
+ uint max_key_part= 0;
+ uint cur_key_infix_len= 0;
+ uchar cur_key_infix[MAX_KEY_LENGTH];
+ uint cur_used_key_parts;
- /* Check (B1) - if current index is covering. */
- if (!table->covering_keys.is_set(cur_index))
- goto next_index;
-
- /*
- If the current storage manager is such that it appends the primary key to
- each index, then the above condition is insufficient to check if the
- index is covering. In such cases it may happen that some fields are
- covered by the PK index, but not by the current index. Since we can't
- use the concatenation of both indexes for index lookup, such an index
- does not qualify as covering in our case. If this is the case, below
- we check that all query fields are indeed covered by 'cur_index'.
- */
- if (pk < MAX_KEY && cur_index != pk &&
- (table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX))
- {
- /* For each table field */
- for (uint i= 0; i < table->s->fields; i++)
+ /* Check (B1) - if current index is covering. */
+ if (!table->covering_keys.is_set(cur_index))
{
- Field *cur_field= table->field[i];
- /*
- If the field is used in the current query ensure that it's
- part of 'cur_index'
- */
- if (bitmap_is_set(table->read_set, cur_field->field_index) &&
- !cur_field->part_of_key_not_clustered.is_set(cur_index))
- goto next_index; // Field was not part of key
+ trace_idx.add("usable", false).add("covering", false);
+ goto next_index;
}
- }
- /*
- Check (GA1) for GROUP BY queries.
- */
- if (join->group_list)
- {
- cur_part= cur_index_info->key_part;
- end_part= cur_part + cur_index_info->key_parts;
- /* Iterate in parallel over the GROUP list and the index parts. */
- for (tmp_group= join->group_list; tmp_group && (cur_part != end_part);
- tmp_group= tmp_group->next, cur_part++)
+ /*
+ If the current storage manager is such that it appends the primary key to
+ each index, then the above condition is insufficient to check if the
+ index is covering. In such cases it may happen that some fields are
+ covered by the PK index, but not by the current index. Since we can't
+ use the concatenation of both indexes for index lookup, such an index
+ does not qualify as covering in our case. If this is the case, below
+ we check that all query fields are indeed covered by 'cur_index'.
+ */
+ if (pk < MAX_KEY && cur_index != pk &&
+ (table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX))
{
- /*
- TODO:
- tmp_group::item is an array of Item, is it OK to consider only the
- first Item? If so, then why? What is the array for?
- */
- /* Above we already checked that all group items are fields. */
- DBUG_ASSERT((*tmp_group->item)->real_item()->type() == Item::FIELD_ITEM);
- Item_field *group_field= (Item_field *) (*tmp_group->item)->real_item();
- if (group_field->field->eq(cur_part->field))
+ /* For each table field */
+ for (uint i= 0; i < table->s->fields; i++)
{
- cur_group_prefix_len+= cur_part->store_length;
- ++cur_group_key_parts;
- max_key_part= cur_part - cur_index_info->key_part + 1;
- used_key_parts_map.set_bit(max_key_part);
+ Field *cur_field= table->field[i];
+ /*
+ If the field is used in the current query ensure that it's
+ part of 'cur_index'
+ */
+ if (bitmap_is_set(table->read_set, cur_field->field_index) &&
+ !cur_field->part_of_key_not_clustered.is_set(cur_index))
+ {
+ trace_idx.add("usable", false).add("covering", false);
+ goto next_index; // Field was not part of key
+ }
}
- else
- goto next_index;
}
- }
- /*
- Check (GA2) if this is a DISTINCT query.
- If GA2, then Store a new ORDER object in group_fields_array at the
- position of the key part of item_field->field. Thus we get the ORDER
- objects for each field ordered as the corresponding key parts.
- Later group_fields_array of ORDER objects is used to convert the query
- to a GROUP query.
- */
- if ((!join->group_list && join->select_distinct) ||
- is_agg_distinct)
- {
- if (!is_agg_distinct)
+ trace_idx.add("covering", true);
+
+ /*
+ Check (GA1) for GROUP BY queries.
+ */
+ if (join->group_list)
{
- select_items_it.rewind();
+ cur_part= cur_index_info->key_part;
+ end_part= cur_part + cur_index_info->key_parts;
+ /* Iterate in parallel over the GROUP list and the index parts. */
+ for (tmp_group= join->group_list; tmp_group && (cur_part != end_part);
+ tmp_group= tmp_group->next, cur_part++)
+ {
+ /*
+ TODO:
+ tmp_group::item is an array of Item, is it OK to consider only the
+ first Item? If so, then why? What is the array for?
+ */
+ /* Above we already checked that all group items are fields. */
+ DBUG_ASSERT((*tmp_group->item)->real_item()->type() == Item::FIELD_ITEM);
+ Item_field *group_field= (Item_field *) (*tmp_group->item)->real_item();
+ if (group_field->field->eq(cur_part->field))
+ {
+ cur_group_prefix_len+= cur_part->store_length;
+ ++cur_group_key_parts;
+ max_key_part= cur_part - cur_index_info->key_part + 1;
+ used_key_parts_map.set_bit(max_key_part);
+ }
+ else
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "group_attribute_not_prefix_in_index");
+ goto next_index;
+ }
+ }
}
- List_iterator<Item_field> agg_distinct_flds_it (agg_distinct_flds);
- while (NULL != (item = (is_agg_distinct ?
- (Item *) agg_distinct_flds_it++ : select_items_it++)))
+ /*
+ Check (GA2) if this is a DISTINCT query.
+ If GA2, then Store a new ORDER object in group_fields_array at the
+ position of the key part of item_field->field. Thus we get the ORDER
+ objects for each field ordered as the corresponding key parts.
+ Later group_fields_array of ORDER objects is used to convert the query
+ to a GROUP query.
+ */
+ if ((!join->group_list && join->select_distinct) ||
+ is_agg_distinct)
{
- /* (SA5) already checked above. */
- item_field= (Item_field*) item->real_item();
- DBUG_ASSERT(item->real_item()->type() == Item::FIELD_ITEM);
+ if (!is_agg_distinct)
+ {
+ select_items_it.rewind();
+ }
- /* not doing loose index scan for derived tables */
- if (!item_field->field)
- goto next_index;
+ List_iterator<Item_field> agg_distinct_flds_it (agg_distinct_flds);
+ while (NULL != (item = (is_agg_distinct ?
+ (Item *) agg_distinct_flds_it++ : select_items_it++)))
+ {
+ /* (SA5) already checked above. */
+ item_field= (Item_field*) item->real_item();
+ DBUG_ASSERT(item->real_item()->type() == Item::FIELD_ITEM);
- /* Find the order of the key part in the index. */
- key_part_nr= get_field_keypart(cur_index_info, item_field->field);
+ /* not doing loose index scan for derived tables */
+ if (!item_field->field)
+ {
+ trace_idx.add("usable", false).add_str("cause", "derived_table");
+ goto next_index;
+ }
+
+ /* Find the order of the key part in the index. */
+ key_part_nr= get_field_keypart(cur_index_info, item_field->field);
+ /*
+ Check if this attribute was already present in the select list.
+ If it was present, then its corresponding key part was alredy used.
+ */
+ if (used_key_parts_map.is_set(key_part_nr))
+ continue;
+ if (key_part_nr < 1 ||
+ (!is_agg_distinct && key_part_nr > join->fields_list.elements))
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "select_attribute_not_prefix_in_index");
+ goto next_index;
+ }
+ cur_part= cur_index_info->key_part + key_part_nr - 1;
+ cur_group_prefix_len+= cur_part->store_length;
+ used_key_parts_map.set_bit(key_part_nr);
+ ++cur_group_key_parts;
+ max_key_part= max(max_key_part,key_part_nr);
+ }
/*
- Check if this attribute was already present in the select list.
- If it was present, then its corresponding key part was alredy used.
+ Check that used key parts forms a prefix of the index.
+ To check this we compare bits in all_parts and cur_parts.
+ all_parts have all bits set from 0 to (max_key_part-1).
+ cur_parts have bits set for only used keyparts.
*/
- if (used_key_parts_map.is_set(key_part_nr))
- continue;
- if (key_part_nr < 1 ||
- (!is_agg_distinct && key_part_nr > join->fields_list.elements))
+ ulonglong all_parts, cur_parts;
+ all_parts= (1<<max_key_part) - 1;
+ cur_parts= used_key_parts_map.to_ulonglong() >> 1;
+ if (all_parts != cur_parts)
goto next_index;
- cur_part= cur_index_info->key_part + key_part_nr - 1;
- cur_group_prefix_len+= cur_part->store_length;
- used_key_parts_map.set_bit(key_part_nr);
- ++cur_group_key_parts;
- max_key_part= max(max_key_part,key_part_nr);
}
- /*
- Check that used key parts forms a prefix of the index.
- To check this we compare bits in all_parts and cur_parts.
- all_parts have all bits set from 0 to (max_key_part-1).
- cur_parts have bits set for only used keyparts.
- */
- ulonglong all_parts, cur_parts;
- all_parts= (1<<max_key_part) - 1;
- cur_parts= used_key_parts_map.to_ulonglong() >> 1;
- if (all_parts != cur_parts)
- goto next_index;
- }
-
- /* Check (SA2). */
- if (min_max_arg_item)
- {
- key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field);
- if (key_part_nr <= cur_group_key_parts)
- goto next_index;
- min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
- }
- /*
- Check (NGA1, NGA2) and extract a sequence of constants to be used as part
- of all search keys.
- */
-
- /*
- If there is MIN/MAX, each keypart between the last group part and the
- MIN/MAX part must participate in one equality with constants, and all
- keyparts after the MIN/MAX part must not be referenced in the query.
-
- If there is no MIN/MAX, the keyparts after the last group part can be
- referenced only in equalities with constants, and the referenced keyparts
- must form a sequence without any gaps that starts immediately after the
- last group keypart.
- */
- last_part= cur_index_info->key_part + cur_index_info->key_parts;
- first_non_group_part= (cur_group_key_parts < cur_index_info->key_parts) ?
- cur_index_info->key_part + cur_group_key_parts :
- NULL;
- first_non_infix_part= min_max_arg_part ?
- (min_max_arg_part < last_part) ?
- min_max_arg_part :
- NULL :
- NULL;
- if (first_non_group_part &&
- (!min_max_arg_part || (min_max_arg_part - first_non_group_part > 0)))
- {
- if (tree)
+ /* Check (SA2). */
+ if (min_max_arg_item)
{
- uint dummy;
- SEL_ARG *index_range_tree= get_index_range_tree(cur_index, tree, param,
- &dummy);
- if (!get_constant_key_infix(cur_index_info, index_range_tree,
- first_non_group_part, min_max_arg_part,
- last_part, thd, cur_key_infix,
- &cur_key_infix_len,
- &first_non_infix_part))
+ key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field);
+ if (key_part_nr <= cur_group_key_parts)
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "aggregate_column_not_suffix_in_idx");
goto next_index;
+ }
+ min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
}
- else if (min_max_arg_part &&
- (min_max_arg_part - first_non_group_part > 0))
- {
- /*
- There is a gap but no range tree, thus no predicates at all for the
- non-group keyparts.
- */
- goto next_index;
- }
- else if (first_non_group_part && join->conds)
+
+ /*
+ Check (NGA1, NGA2) and extract a sequence of constants to be used as part
+ of all search keys.
+ */
+
+ /*
+ If there is MIN/MAX, each keypart between the last group part and the
+ MIN/MAX part must participate in one equality with constants, and all
+ keyparts after the MIN/MAX part must not be referenced in the query.
+
+ If there is no MIN/MAX, the keyparts after the last group part can be
+ referenced only in equalities with constants, and the referenced keyparts
+ must form a sequence without any gaps that starts immediately after the
+ last group keypart.
+ */
+ last_part= cur_index_info->key_part + cur_index_info->key_parts;
+ first_non_group_part= (cur_group_key_parts < cur_index_info->key_parts) ?
+ cur_index_info->key_part + cur_group_key_parts :
+ NULL;
+ first_non_infix_part= min_max_arg_part ?
+ (min_max_arg_part < last_part) ?
+ min_max_arg_part :
+ NULL :
+ NULL;
+ if (first_non_group_part &&
+ (!min_max_arg_part || (min_max_arg_part - first_non_group_part > 0)))
{
- /*
- If there is no MIN/MAX function in the query, but some index
- key part is referenced in the WHERE clause, then this index
- cannot be used because the WHERE condition over the keypart's
- field cannot be 'pushed' to the index (because there is no
- range 'tree'), and the WHERE clause must be evaluated before
- GROUP BY/DISTINCT.
- */
- /*
- Store the first and last keyparts that need to be analyzed
- into one array that can be passed as parameter.
- */
- KEY_PART_INFO *key_part_range[2];
- key_part_range[0]= first_non_group_part;
- key_part_range[1]= last_part;
-
- /* Check if cur_part is referenced in the WHERE clause. */
- if (join->conds->walk(&Item::find_item_in_field_list_processor, 0,
- (uchar*) key_part_range))
+ if (tree)
+ {
+ uint dummy;
+ SEL_ARG *index_range_tree= get_index_range_tree(cur_index, tree, param,
+ &dummy);
+ if (!get_constant_key_infix(cur_index_info, index_range_tree,
+ first_non_group_part, min_max_arg_part,
+ last_part, thd, cur_key_infix,
+ &cur_key_infix_len,
+ &first_non_infix_part))
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "nonconst_equality_gap_attribute");
+ goto next_index;
+ }
+ }
+ else if (min_max_arg_part &&
+ (min_max_arg_part - first_non_group_part > 0))
+ {
+ /*
+ There is a gap but no range tree, thus no predicates at all for the
+ non-group keyparts.
+ */
+ trace_idx.add("usable", false).
+ add_str("cause", "no_nongroup_keypart_predicate");
goto next_index;
+ }
+ else if (first_non_group_part && join->conds)
+ {
+ /*
+ If there is no MIN/MAX function in the query, but some index
+ key part is referenced in the WHERE clause, then this index
+ cannot be used because the WHERE condition over the keypart's
+ field cannot be 'pushed' to the index (because there is no
+ range 'tree'), and the WHERE clause must be evaluated before
+ GROUP BY/DISTINCT.
+ */
+ /*
+ Store the first and last keyparts that need to be analyzed
+ into one array that can be passed as parameter.
+ */
+ KEY_PART_INFO *key_part_range[2];
+ key_part_range[0]= first_non_group_part;
+ key_part_range[1]= last_part;
+
+ /* Check if cur_part is referenced in the WHERE clause. */
+ if (join->conds->walk(&Item::find_item_in_field_list_processor, 0,
+ (uchar*) key_part_range))
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "indexpart_reference_from_where_clause");
+ goto next_index;
+ }
+ }
}
- }
- /*
- Test (WA1) partially - that no other keypart after the last infix part is
- referenced in the query.
- */
- if (first_non_infix_part)
- {
- cur_part= first_non_infix_part +
- (min_max_arg_part && (min_max_arg_part < last_part));
- for (; cur_part != last_part; cur_part++)
+ /*
+ Test (WA1) partially - that no other keypart after the last infix part is
+ referenced in the query.
+ */
+ if (first_non_infix_part)
{
- if (bitmap_is_set(table->read_set, cur_part->field->field_index))
- goto next_index;
+ cur_part= first_non_infix_part +
+ (min_max_arg_part && (min_max_arg_part < last_part));
+ for (; cur_part != last_part; cur_part++)
+ {
+ if (bitmap_is_set(table->read_set, cur_part->field->field_index))
+ {
+ trace_idx.add("usable", false).
+ add_str("cause", "keypart_after_infix_in_query");
+ goto next_index;
+ }
+ }
}
- }
- /* If we got to this point, cur_index_info passes the test. */
- key_infix_parts= cur_key_infix_len ? (uint)
- (first_non_infix_part - first_non_group_part) : 0;
- cur_used_key_parts= cur_group_key_parts + key_infix_parts;
+ /* If we got to this point, cur_index_info passes the test. */
+ key_infix_parts= cur_key_infix_len ? (uint)
+ (first_non_infix_part - first_non_group_part) : 0;
+ cur_used_key_parts= cur_group_key_parts + key_infix_parts;
- /* Compute the cost of using this index. */
- if (tree)
- {
- /* Find the SEL_ARG sub-tree that corresponds to the chosen index. */
- cur_index_tree= get_index_range_tree(cur_index, tree, param,
- &cur_param_idx);
- /* Check if this range tree can be used for prefix retrieval. */
- COST_VECT dummy_cost;
- uint mrr_flags= HA_MRR_USE_DEFAULT_IMPL;
- uint mrr_bufsize=0;
- cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
- FALSE /*don't care*/,
- cur_index_tree, TRUE,
- &mrr_flags, &mrr_bufsize,
- &dummy_cost);
- }
- cost_group_min_max(table, cur_index_info, cur_used_key_parts,
- cur_group_key_parts, tree, cur_index_tree,
- cur_quick_prefix_records, have_min, have_max,
- &cur_read_cost, &cur_records);
- /*
- If cur_read_cost is lower than best_read_cost use cur_index.
- Do not compare doubles directly because they may have different
- representations (64 vs. 80 bits).
- */
- if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
- {
- index_info= cur_index_info;
- index= cur_index;
- best_read_cost= cur_read_cost;
- best_records= cur_records;
- best_index_tree= cur_index_tree;
- best_quick_prefix_records= cur_quick_prefix_records;
- best_param_idx= cur_param_idx;
- group_key_parts= cur_group_key_parts;
- group_prefix_len= cur_group_prefix_len;
- key_infix_len= cur_key_infix_len;
- if (key_infix_len)
- memcpy (key_infix, cur_key_infix, sizeof (key_infix));
- used_key_parts= cur_used_key_parts;
- }
+ /* Compute the cost of using this index. */
+ if (tree)
+ {
+ /* Find the SEL_ARG sub-tree that corresponds to the chosen index. */
+ cur_index_tree= get_index_range_tree(cur_index, tree, param,
+ &cur_param_idx);
+ /* Check if this range tree can be used for prefix retrieval. */
+ COST_VECT dummy_cost;
+ uint mrr_flags= HA_MRR_USE_DEFAULT_IMPL;
+ uint mrr_bufsize=0;
+ cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
+ FALSE /*don't care*/,
+ cur_index_tree, TRUE,
+ &mrr_flags, &mrr_bufsize,
+ &dummy_cost);
+ }
+ cost_group_min_max(table, cur_index_info, cur_used_key_parts,
+ cur_group_key_parts, tree, cur_index_tree,
+ cur_quick_prefix_records, have_min, have_max,
+ &cur_read_cost, &cur_records);
+ /*
+ If cur_read_cost is lower than best_read_cost use cur_index.
+ Do not compare doubles directly because they may have different
+ representations (64 vs. 80 bits).
+ */
+ trace_idx.add("records", cur_records).add("cost", cur_read_cost);
+ if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
+ {
+ index_info= cur_index_info;
+ index= cur_index;
+ best_read_cost= cur_read_cost;
+ best_records= cur_records;
+ best_index_tree= cur_index_tree;
+ best_quick_prefix_records= cur_quick_prefix_records;
+ best_param_idx= cur_param_idx;
+ group_key_parts= cur_group_key_parts;
+ group_prefix_len= cur_group_prefix_len;
+ key_infix_len= cur_key_infix_len;
+ if (key_infix_len)
+ memcpy (key_infix, cur_key_infix, sizeof (key_infix));
+ used_key_parts= cur_used_key_parts;
+ }
next_index:;
+ }
}
if (!index_info) /* No usable index found. */
DBUG_RETURN(NULL);
@@ -10102,8 +10579,11 @@ get_best_group_min_max(PARAM *param, SEL
!check_group_min_max_predicates(join->conds, min_max_arg_item,
(index_info->flags & HA_SPATIAL) ?
Field::itMBR : Field::itRAW))
+ {
+ trace_group.add("usable",false).
+ add_str("cause", "unsupported_predicate_on_agg_attribute");
DBUG_RETURN(NULL);
-
+ }
/* The query passes all tests, so construct a new TRP object. */
read_plan= new (param->mem_root)
TRP_GROUP_MIN_MAX(have_min, have_max, is_agg_distinct,
@@ -10123,6 +10603,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);
read_plan->read_cost= 0;
read_plan->use_index_scan();
}
@@ -11782,7 +12263,6 @@ static void print_sel_tree(PARAM *param,
tmp.append(STRING_WITH_LEN("(empty)"));
DBUG_PRINT("info", ("SEL_TREE: %p (%s) scans: %s", tree, msg, tmp.ptr()));
- fprintf(DBUG_FILE,"SEL_TREE: %p (%s) scans: %s", tree, msg, tmp.ptr());
DBUG_VOID_RETURN;
}
@@ -11822,87 +12302,48 @@ static void print_ror_scans_arr(TABLE *t
#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
-/*
- Print a key into a stream
-
- SYNOPSIS
- key_part Index components description
- key Key tuple
- used_length Key tuple length
-*/
+/**
+ Append range info to a string
-static void
-print_key2(String *out, KEY_PART_INFO *key_part, const uchar *key,
- uint used_length)
+ @param[in,out] out String the range info is appended to
+ @param[in] key_parts Index components description
+ @param[in] min_key Key tuple describing lower bound of range
+ @param[in] min_length Length of min_key
+ @param[in] max_key Key tuple describing upper bound of range
+ @param[in] max_length Length of max_key
+ @param[in] flag Key range flags defining what min_key
+ and max_key represent @see my_base.h
+ */
+static void append_range(String *out,
+ const KEY_PART_INFO *key_parts,
+ const uchar *min_key, const uint16 min_length,
+ const uchar *max_key, const uint16 max_length,
+ const uint flag)
{
- const uchar *key_end= key+used_length;
- String tmp;
- uint store_length;
- TABLE *table= key_part->field->table;
- my_bitmap_map *old_sets[2];
-
- dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
- for (; key < key_end; key+=store_length, key_part++)
- {
- Field *field= key_part->field;
- store_length= key_part->store_length;
-
- if (field->real_maybe_null())
- {
- if (*key)
- {
- out->append("NULL", 4);
- continue;
- }
- key++; // Skip null byte
- store_length--;
- }
- field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void) field->val_int_as_str(&tmp, 1);
- else
- field->val_str(&tmp);
- out->append(tmp.ptr(), tmp.length());
- if (key+store_length < key_end)
- out->append("'/",1);
- }
- dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
-}
-
+ if (out->length() > 0)
+ out->append(STRING_WITH_LEN(" : "));
-static void print_range(Opt_trace_context *out, KEY_PART_INFO *key_parts,
- const uchar *min_key, uint16 min_length,
- const uchar *max_key, uint16 max_length,
- uint flag)
-{
- String tmp;
if (!(flag & NO_MIN_RANGE))
{
- print_key2(&tmp, key_parts, min_key, min_length);
+ print_key2(out, key_parts, min_key, min_length);
if (flag & NEAR_MIN)
- tmp.append(" < ");
+ out->append(STRING_WITH_LEN(" < "));
else
- tmp.append(" <= ");
+ out->append(STRING_WITH_LEN(" <= "));
}
- tmp.append("X");
+ out->append(key_parts->field->field_name);
if (!(flag & NO_MAX_RANGE))
{
if (flag & NEAR_MAX)
- tmp.append(" < ");
+ out->append(STRING_WITH_LEN(" < "));
else
- tmp.append(" <= ");
- print_key2(&tmp, key_parts, max_key, max_length);
+ out->append(STRING_WITH_LEN(" <= "));
+ print_key2(out, key_parts, max_key, max_length);
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- OPT_TRACE(out, add(tmp.c_ptr()));
-#endif
}
-
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg)
{
char buf[MAX_KEY/8+1];
@@ -11927,20 +12368,18 @@ static void print_quick(QUICK_SELECT_I *
DBUG_UNLOCK_FILE;
DBUG_VOID_RETURN;
}
-#endif
-
void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose)
{
Opt_trace_context *out= current_thd->opt_trace;
+ Opt_trace_object oto(out, "range_scan");
+ oto.add_str("index", head->key_info[index].name,
+ strlen(head->key_info[index].name)).
+ add("key_length", max_used_key_length).
+ add("used_keyparts", used_key_parts);
+
+ Opt_trace_array trace_range(out, "ranges");
/* purecov: begin inspected */
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto(out, "range_select");
- oto.add(head->key_info[index].name, "index").
- add(static_cast<ulonglong>(max_used_key_length), "key_length");
-
- Opt_trace_array ota(out, "ranges");
-#endif
if (TRUE)
{
QUICK_RANGE *range;
@@ -11948,72 +12387,67 @@ void QUICK_RANGE_SELECT::dbug_dump(int i
QUICK_RANGE **end_range= pr + ranges.elements;
for (; pr != end_range; ++pr)
{
+ String range_info;
+ range_info.set_charset(system_charset_info);
range= *pr;
- print_range(out, head->key_info[index].key_part,
- range->min_key, range->min_length,
- range->max_key, range->max_length,
- range->flag);
+ append_range(&range_info, head->key_info[index].key_part,
+ range->min_key, range->min_length,
+ range->max_key, range->max_length,
+ range->flag);
+ trace_range.add_str(range_info.ptr(), range_info.length());
}
}
/* purecov: end */
}
-
void QUICK_INDEX_MERGE_SELECT::dbug_dump(int indent, bool verbose)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_context *out= current_thd->opt_trace;
-#endif
List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
QUICK_RANGE_SELECT *quick;
/* purecov: begin inspected */
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto(out, "index_merge_sort_union");
-#endif
+ Opt_trace_array ota(out, "index_merge");
while ((quick= it++))
+ {
+ Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
+ }
if (pk_quick_select)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_object oto2(out, "clustered_pk_scan");
-#endif
pk_quick_select->dbug_dump(indent+2, verbose);
}
}
void QUICK_ROR_INTERSECT_SELECT::dbug_dump(int indent, bool verbose)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_context *out= current_thd->opt_trace;
-#endif
List_iterator_fast<QUICK_RANGE_SELECT> it(quick_selects);
QUICK_RANGE_SELECT *quick;
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto(out, "index_merge_intersect");
-#endif
+ Opt_trace_array ota(out, "index_roworder_intersect");
while ((quick= it++))
+ {
+ Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
+ }
if (cpk_quick)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_object oto2(out, "clustered_pk_scan");
-#endif
cpk_quick->dbug_dump(indent+2, verbose);
}
}
void QUICK_ROR_UNION_SELECT::dbug_dump(int indent, bool verbose)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_context *out= current_thd->opt_trace;
-#endif
List_iterator_fast<QUICK_SELECT_I> it(quick_selects);
QUICK_SELECT_I *quick;
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto(out, "index_merge_sort_union_intersect");
-#endif
+ Opt_trace_array ota(out, "index_roworder_union");
while ((quick= it++))
+ {
+ Opt_trace_object wrapper(out);
quick->dbug_dump(indent+2, verbose);
+ }
}
@@ -12039,6 +12473,11 @@ void QUICK_ROR_UNION_SELECT::dbug_dump(i
void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
{
+ Opt_trace_context *out= current_thd->opt_trace;
+ Opt_trace_object oto(out, "index_group");
+ oto.add_str("index", index_info->name, strlen(index_info->name)).
+ add("key_length", max_used_key_length);
+
fprintf(DBUG_FILE,
"%*squick_group_min_max_select: index %s (%d), length: %d\n",
indent, "", index_info->name, index, max_used_key_length);
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2010-08-05 13:51:44 +0000
+++ b/sql/opt_range.h 2010-10-22 08:31:43 +0000
@@ -33,6 +33,7 @@
*/
#include "sql_class.h" // set_var.h: THD
#include "set_var.h" /* Item */
+#include "opt_trace.h"
class JOIN;
class Item_sum;
@@ -863,6 +864,12 @@ class SQL_SELECT :public Sql_alloc {
bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
{
key_map tmp(key_map::ALL_BITS);
+
+ /* Entrypoint for insert/update/deletes with condition.
+ test_quick_select adds tracing with keys, and we are currently
+ in a trace array that does not accept keys.
+ */
+ Opt_trace_object wrapper(thd->opt_trace);
return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE) < 0;
}
inline bool skip_record(THD *thd, bool *skip_record)
=== modified file 'sql/opt_trace.cc'
--- a/sql/opt_trace.cc 2010-10-21 13:03:30 +0000
+++ b/sql/opt_trace.cc 2010-10-22 08:31:43 +0000
@@ -377,12 +377,13 @@ const char *Opt_trace_context::flag_name
const char *Opt_trace_context::feature_names[]=
{
- "misc", "greedy_search", "default", NullS
+ "misc", "greedy_search", "range_optimizer", "default", NullS
};
const Opt_trace_context::feature_value Opt_trace_context::FEATURES_DEFAULT=
Opt_trace_context::feature_value(Opt_trace_context::MISC |
- Opt_trace_context::GREEDY_SEARCH);
+ Opt_trace_context::GREEDY_SEARCH |
+ Opt_trace_context::RANGE_OPTIMIZER);
Opt_trace_context::Opt_trace_context(void):
oldest_stmt_to_show(NULL), newest_stmt_to_show(NULL), stmt_to_del(NULL),
=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h 2010-10-21 20:24:51 +0000
+++ b/sql/opt_trace.h 2010-10-22 08:31:43 +0000
@@ -370,6 +370,8 @@ public:
@c sys_vars.cc:
@li "misc" = anything unclassified
@li "greedy_search" = the greedy search for a plan
+ @li "range_optimizer" = the cost analysis of accessing data through
+ ranges in indices
@li "default".
*/
static const char *feature_names[];
@@ -380,7 +382,8 @@ public:
from parent", disabling MISC makes an empty trace).
*/
MISC= 1,
- GREEDY_SEARCH= 2 ///@todo range opt, join cache, semijoin...
+ GREEDY_SEARCH= 2,
+ RANGE_OPTIMIZER= 4 ///@todo join cache, semijoin...
/* if you add here, update FEATURES_DEFAULT! */
};
static const feature_value FEATURES_DEFAULT;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-10-21 13:03:30 +0000
+++ b/sql/sql_select.cc 2010-10-22 08:31:43 +0000
@@ -259,6 +259,10 @@ void select_describe(JOIN *join, bool ne
bool distinct, const char *message=NullS);
static Item *remove_additional_cond(Item* conds);
static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static void trace_indices_added_group_distinct(Opt_trace_context *trace,
+ const JOIN_TAB *join_tab,
+ const key_map new_keys,
+ const char* cause);
static bool replace_subcondition(JOIN *join, Item **tree,
Item *old_cond, Item *new_cond,
bool do_fix_fields);
@@ -4362,7 +4366,9 @@ int pull_out_semijoin_tables(JOIN *join)
if (tbl->table->map & join->const_table_map)
{
pulled_tables |= tbl->table->map;
- Opt_trace_object(trace).add_str("table", tbl->table->alias).
+ Opt_trace_object(trace).
+ add_str("database", tbl->db, strlen(tbl->db)).
+ add_str("table", tbl->alias, strlen(tbl->alias)).
add("constant", true);
}
}
@@ -4388,7 +4394,9 @@ int pull_out_semijoin_tables(JOIN *join)
{
pulled_a_table= TRUE;
pulled_tables |= tbl->table->map;
- Opt_trace_object(trace).add_str("table", tbl->table->alias).
+ Opt_trace_object(trace).
+ add_str("database", tbl->db, strlen(tbl->db)).
+ add_str("table", tbl->alias, strlen(tbl->alias)).
add("functionally_dependent", true);
/*
Pulling a table out of uncorrelated subquery in general makes
@@ -4947,14 +4955,22 @@ make_join_statistics(JOIN *join, TABLE_L
for (s= stat ; s < stat_end ; s++)
{
+ Opt_trace_object trace_table(join->thd->opt_trace);
+ trace_table.add_str("database",s->join->tables_list->db,
+ strlen(s->join->tables_list->db)).
+ add_str("table", s->table->alias, strlen(s->table->alias));
if (s->type == JT_SYSTEM || s->type == JT_CONST)
{
+ trace_table.add("records", 1).
+ add("cost", 1);
+
+ trace_table.add_str("cause", (s->type == JT_SYSTEM) ?
+ "system_table": "const_table");
+
/* Only one matching row */
s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
continue;
}
- Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("table", s->table->alias);
/* Approximate found rows and time to read them */
s->found_records= s->records= s->table->file->stats.records;
s->read_time= (ha_rows) s->table->file->scan_time();
@@ -4985,10 +5001,6 @@ make_join_statistics(JOIN *join, TABLE_L
(s->table->pos_in_table_list->embedding && // (3)
s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
{
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
- add_str("TODO?", "yes!");
-#endif
ha_rows records;
SQL_SELECT *select;
select= make_select(s->table, found_const_table_map,
@@ -5017,9 +5029,16 @@ make_join_statistics(JOIN *join, TABLE_L
{
/* Generate empty row */
s->info= "Impossible ON condition";
+ trace_table.add("returning_empty_null_row", true).
+ add_str("cause", "impossible_on_condition");
found_const_table_map|= s->table->map;
s->type= JT_CONST;
mark_as_null_row(s->table); // All fields are NULL
+ }
+ else
+ {
+ trace_table.add("records", 0).
+ add_str("cause", "impossible_where_condition");
}
}
if (records != HA_POS_ERROR)
@@ -6498,11 +6517,14 @@ add_group_and_distinct_keys(JOIN *join,
Item_field *cur_item;
key_map possible_keys;
+ const char* cause;
+
if (join->group_list)
{ /* Collect all query fields referenced in the GROUP clause. */
for (cur_group= join->group_list; cur_group; cur_group= cur_group->next)
(*cur_group->item)->walk(&Item::collect_item_field_processor, 0,
(uchar*) &indexed_fields);
+ cause= (char*)"group_by";
}
else if (join->select_distinct)
{ /* Collect all query fields referenced in the SELECT clause. */
@@ -6512,10 +6534,12 @@ add_group_and_distinct_keys(JOIN *join,
while ((item= select_items_it++))
item->walk(&Item::collect_item_field_processor, 0,
(uchar*) &indexed_fields);
+ cause= (char*)"distinct";
}
else if (is_indexed_agg_distinct(join, &indexed_fields))
{
join->sort_and_group= 1;
+ cause= (char*) "indexed_distinct_aggregate";
}
else
return;
@@ -6531,8 +6555,49 @@ add_group_and_distinct_keys(JOIN *join,
possible_keys.intersect(cur_item->field->part_of_key);
}
- if (!possible_keys.is_clear_all())
+ if (!possible_keys.is_clear_all() &&
+ !(possible_keys == join_tab->const_keys))
+ {
+ Opt_trace_context *trace= join->thd->opt_trace;
+ if (trace && trace->is_started())
+ {
+ trace_indices_added_group_distinct(trace, join_tab,
+ possible_keys, cause);
+ }
join_tab->const_keys.merge(possible_keys);
+ }
+}
+
+/**
+ Print keys that were appended to join_tab->const_keys because they
+ can be used for GROUP BY or DISTINCT to the optimizer trace.
+
+ @param thd Thread for the connection that submitted the query
+ @param join_tab The table the indices cover
+ @param new_keys The keys that are considered useful because they can
+ be used for GROUP BY or DISTINCT
+ @param cause Zero-terminated string with reason for adding indices
+ to const_keys
+
+ @see add_group_and_distinct_keys()
+ */
+static void trace_indices_added_group_distinct(Opt_trace_context *trace,
+ const JOIN_TAB *join_tab,
+ const key_map new_keys,
+ const char* cause)
+{
+ KEY *key_info= join_tab->table->key_info;
+ key_map existing_keys= join_tab->const_keys;
+ uint nbrkeys= join_tab->table->s->keys;
+
+ Opt_trace_object trace_summary(trace, "const_keys_added");
+ {
+ Opt_trace_array trace_key(trace,"keys");
+ for (uint j= 0 ; j < nbrkeys ; j++)
+ if (new_keys.is_set(j) && !existing_keys.is_set(j))
+ trace_key.add_str(key_info[j].name, strlen(key_info[j].name));
+ }
+ trace_summary.add_str("cause", cause);
}
@@ -6974,9 +7039,9 @@ best_access_path(JOIN *join,
loose_scan_opt.next_ref_key();
DBUG_PRINT("info", ("Considering ref access on key %s",
keyuse->table->key_info[keyuse->key].name));
- Opt_trace_object trace_index_path(trace);
- trace_index_path.add_str("access_type", "index").
- add_str("index", keyinfo->name);
+ Opt_trace_object trace_access_idx(thd->opt_trace);
+ trace_access_idx.add_str("access_type", "index").
+ add_str("index", keyinfo->name, strlen(keyinfo->name));
/*
True if we find some keys from the range optimizer that match more
@@ -7032,7 +7097,7 @@ best_access_path(JOIN *join,
*/
if (!found_part && !ft_key && !loose_scan_opt.have_a_case())
{
- trace_index_path.add("usable", false);
+ trace_access_idx.add("usable", false);
goto done_with_index; // Nothing usable found
}
@@ -7311,7 +7376,7 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
- trace_index_path.add("cost", tmp).add("records", records);
+ trace_access_idx.add("records", records).add("cost", tmp);
/** @todo trace quick_matches_more_parts etc? */
if (tmp < best_time - records/(double) TIME_FOR_COMPARE ||
(quick_matches_more_parts &&
@@ -7326,13 +7391,12 @@ best_access_path(JOIN *join,
best_ref_depends_map= found_ref;
}
done_with_index:
- trace_index_path.add("chosen", best_key == start_key);
+ trace_access_idx.add("chosen", best_key == start_key);
} /* for each key */
records= best_records;
}
- Opt_trace_object trace_scan_path(trace);
- trace_scan_path.add_str("access_type", "table scan");
+ Opt_trace_object trace_access_scan(thd->opt_trace);
/*
Don't test table scan if it can't be better.
Prefer key lookup if we would use the same key for scanning.
@@ -7363,28 +7427,38 @@ best_access_path(JOIN *join,
*/
if (!(records >= s->found_records || best > s->read_time)) // (1)
{
- trace_scan_path.add("cost", s->read_time).
- add("records", s->found_records);
+ trace_access_scan.add_str("access_type", s->quick ? "range" : "scan");
+ trace_access_scan.add("cost", s->read_time).
+ add("records", s->found_records).
+ add("chosen", false).
+ add_str("cause", "index_cheaper");
+
goto skip_table_scan;
}
if ((s->quick && best_key && s->quick->index == best_key->key && // (2)
best_max_key_part >= s->table->quick_key_parts[best_key->key])) // (2)
{
- trace_scan_path.add("heuristic_index_must_be_cheaper", true);
+ trace_access_scan.add_str("access_type", "range").
+ add("chosen", false).
+ add_str("cause", "heuristic_index_must_be_cheaper");
goto skip_table_scan;
}
if (((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
! s->table->covering_keys.is_clear_all() && best_key && !s->quick))// (3)
{
- trace_scan_path.add("index_is_covering", true);
+ trace_access_scan.add_str("access_type", s->quick ? "range" : "scan");
+ trace_access_scan.add("chosen", false).
+ add_str("cause", "covering_index_better_than_full_scan");
goto skip_table_scan;
}
if ((s->table->force_index && best_key && !s->quick)) // (4)
{
- trace_scan_path.add("FORCE_INDEX_used", true);
+ trace_access_scan.add_str("access_type", "scan").
+ add("chosen", false).
+ add_str("cause", "force_index");
goto skip_table_scan;
}
@@ -7417,6 +7491,7 @@ best_access_path(JOIN *join,
if (s->quick)
{
+ trace_access_scan.add_str("access_type", "range");
/*
For each record we:
- read record range through 'quick'
@@ -7434,6 +7509,7 @@ best_access_path(JOIN *join,
}
else
{
+ trace_access_scan.add_str("access_type", "scan");
/* Estimate cost of reading table. */
if (s->table->force_index && !best_key)
tmp= s->table->file->read_time(s->ref.key, 1, s->records);
@@ -7453,6 +7529,7 @@ best_access_path(JOIN *join,
}
else
{
+ trace_access_scan.add("using_join_cache", true);
/*
We read the table as many times as join buffer becomes full.
It would be more exact to round the result of the division with
@@ -7472,7 +7549,8 @@ best_access_path(JOIN *join,
}
}
- trace_scan_path.add("cost", tmp).add("records", rows2double(rnd_records));
+ trace_access_scan.add("records", rows2double(rnd_records)).
+ add("cost", tmp);
/*
We estimate the cost of evaluating WHERE clause for found records
as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
@@ -7495,11 +7573,10 @@ best_access_path(JOIN *join,
join->outer_join)));
}
}
+ trace_access_scan.add("chosen", best_key == NULL);
skip_table_scan:
- trace_scan_path.add("chosen", best_key == NULL);
-
/* Update the cost information for the current partial plan */
pos->records_read= records;
pos->read_time= best;
@@ -7515,7 +7592,10 @@ skip_table_scan:
idx == join->const_tables &&
s->table == join->sort_by_table &&
join->unit->select_limit_cnt >= records)
+ {
+ trace_access_scan.add("use_temp_table", true);
join->sort_by_table= (TABLE*) 1; // Must use temporary table
+ }
DBUG_VOID_RETURN;
}
@@ -8262,7 +8342,10 @@ best_extension_by_limited_search(JOIN
double current_record_count, current_read_time;
POSITION *position= join->positions + idx;
Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("table", s->table->alias);
+ trace_one_table.
+ add_str("database", s->join->tables_list->db,
+ strlen(s->join->tables_list->db)).
+ add_str("table", s->table->alias, strlen(s->table->alias));
/* Find the best access method from 's' to the current partial plan */
POSITION loose_scan_pos;
@@ -8684,7 +8767,10 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
oto1.add_str("strategy", "MaterializationScan").
- add_str("table", join->best_positions[i].table->table->alias);
+ add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db)).
+ add_str("table", join->best_positions[i].table->table->alias,
+ strlen(join->best_positions[i].table->table->alias));
best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
prefix_rec_count, join->best_positions + i, &dummy);
prefix_rec_count *= join->best_positions[i].records_read;
@@ -8721,7 +8807,10 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
oto1.add_str("strategy", "FirstMatch").
- add_str("table", join->best_positions[idx].table->table->alias);
+ add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db)).
+ add_str("table", join->best_positions[idx].table->table->alias,
+ strlen(join->best_positions[idx].table->table->alias));
best_access_path(join, join->best_positions[idx].table,
rem_tables, idx, TRUE /* no jbuf */,
record_count, join->best_positions + idx, &dummy);
@@ -8755,7 +8844,10 @@ static bool fix_semijoin_strategies_for_
Opt_trace_object oto1(trace,
"reconsidering_access_paths_for_semijoin");
oto1.add_str("strategy", "LooseScan").
- add("table", join->best_positions[idx].table->table->alias);
+ add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db)).
+ add_str("table", join->best_positions[idx].table->table->alias,
+ strlen(join->best_positions[idx].table->table->alias));
best_access_path(join, join->best_positions[idx].table,
rem_tables, idx, TRUE /* no jbuf */,
record_count, join->best_positions + idx,
@@ -9629,7 +9721,9 @@ static bool make_join_select(JOIN *join,
{
tab= join->join_tab+i;
Opt_trace_object trace_one_table(thd->opt_trace);
- trace_one_table.add_str("table", tab->table->alias);
+ trace_one_table.add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db)).
+ add_str("table", tab->table->alias, strlen(tab->table->alias));
/*
first_inner is the X in queries like:
SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X
@@ -13786,7 +13880,10 @@ void optimize_wo_join_buffering(JOIN *jo
{
/* Find the best access method that would not use join buffering */
Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("table", rs->table->alias);
+ trace_one_table.add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db));
+ trace_one_table.add_str("table", rs->table->alias,
+ strlen(rs->table->alias));
best_access_path(join, rs, reopt_remaining_tables, i,
i < no_jbuf_before, inner_fanout * outer_fanout,
&pos, &loose_scan_pos);
@@ -14227,8 +14324,10 @@ void advance_sj_state(JOIN *join, table_
for (i= first_tab + table_count; i <= idx; i++)
{
Opt_trace_object trace_one_table(trace);
- trace_one_table.add_str("table",
- join->positions[i].table->table->alias);
+ trace_one_table.add_str("database", join->tables_list->db,
+ strlen(join->tables_list->db)).
+ add_str("table", join->positions[i].table->table->alias,
+ strlen(join->positions[i].table->table->alias));
best_access_path(join, join->positions[i].table, rem_tables, i, FALSE,
prefix_rec_count, &curpos, &dummy);
prefix_rec_count *= curpos.records_read;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101022083143-0a6nyb3mpamtl33r.bundle
