#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:guilhem@stripped
3219 Jorgen Loland 2010-10-07
WL#5594 - Add optimizer traces to the range optimizer
Add optimizer trace points to the range optimizer.
@ mysql-test/include/optimizer_trace.inc
Added tests to cover range optimizer
@ mysql-test/r/optimizer_trace_no_prot.result
Added tests to cover range optimizer. Also adds range information to trace of existing queries
@ sql/handler.cc
Add tracepoints to range optimizer
@ sql/opt_range.cc
Add tracepoints to range optimizer
@ sql/opt_range.h
Add tracepoints to range optimizer
@ sql/sql_select.cc
Add tracepoints to range optimizer
modified:
mysql-test/include/optimizer_trace.inc
mysql-test/r/optimizer_trace_no_prot.result
sql/handler.cc
sql/opt_range.cc
sql/opt_range.h
sql/sql_select.cc
=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc 2010-10-05 10:29:43 +0000
+++ b/mysql-test/include/optimizer_trace.inc 2010-10-07 09:02:31 +0000
@@ -473,4 +473,138 @@ select 1 union select 2;
drop table t1,t2;
DROP TABLE C,D;
+
+set optimizer_trace=default;
+#################
+
+SET optimizer_trace="enabled=on,end_marker=off,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,
+ INDEX 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;
+
+# index merge
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+# group
+--echo
+EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+#intersect
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+# union
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+# range_scan_possible=false
+--echo
+EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+# more_expensive_than_table_scan
+--echo
+EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+--echo
+
+DROP TABLE t1,t2;
+
+####
+
+CREATE TABLE t1 (
+ cola char(3) not null,
+ colb char(3) not null,
+ filler char(200),
+ key(cola),
+ key(colb)
+) engine=innodb;
+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
+EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
+--echo
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+
+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-05 13:19:55 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result 2010-10-07 09:02:31 +0000
@@ -41,6 +41,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
],
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
]
},
{
@@ -80,9 +89,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"table": "D",
- "more_range_optimizer_trace": {
- "TODO?": "yes!"
- }
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ },
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "key_parts": [
+ "d"
+ ]
+ }
+ ],
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ },
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ },
+ "records": 0
+ },
+ "records": 0,
+ "cause": "impossible_where_condition"
}
]
}
@@ -97,18 +130,18 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
from information_schema.OPTIMIZER_TRACE;
(1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-44.4888
+49.1097
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_ID TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR
-0 {"top_query": "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": [{"ta
ble": "D","more_range_optimizer_trace": {"TODO?": "yes!"}}]}],"empty_result": {"cause": "no matching row in const table"}}}]} 0 0
+0 {"top_query": "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": [{"table": "C","found_records": 1,"records": 1,"cost": 1,"worst_seeks": 1,"exit_find_best_accessplan": true,"cause": "is_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": [{"table": "D","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","key_parts": ["d"]}],"best_full_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_select": true,"stop_range_analysis": true,"chosen_range_access_summary": {"chosen": false},"records": 0},"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
-7.1942
+6.5983
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
@@ -145,6 +178,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -184,9 +226,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -232,6 +298,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -271,9 +346,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -338,6 +437,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -377,9 +485,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -416,9 +548,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -459,9 +615,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10,
- "records": 0,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 0,
+ "cost": 10
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -589,6 +749,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -676,9 +845,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10,
- "records": 0,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 0,
+ "cost": 10
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -716,7 +889,7 @@ select (@trace:=TRACE)+NULL from informa
NULL
select length(@trace);
length(@trace)
-8729
+9244
set optimizer_trace_max_mem_size=8400;
select length(@trace) > @@optimizer_trace_max_mem_size;
length(@trace) > @@optimizer_trace_max_mem_size
@@ -725,13 +898,13 @@ 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)
-336
+852
select (@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@trace2:=TRACE)+NULL
NULL
select length(@trace2), (length(@trace2) + @missing_bytes) = length(@trace);
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace)
-8393 1
+8392 1
select length(@trace2) between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
length(@trace2) between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100)
1
@@ -808,10 +981,63 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
] /* constant_tables */,
"records_estimation": [
{
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_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": "higher_cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 2
+ } /* range_analysis */
}
] /* records_estimation */
},
@@ -831,12 +1057,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"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
@@ -881,12 +1107,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"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
@@ -1007,6 +1233,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"C"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "C",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -1054,9 +1289,53 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.4233,
+ "chosen": true
+ } /* best_full_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": "higher_cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 2
+ } /* range_analysis */
}
] /* records_estimation */
},
@@ -1069,12 +1348,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"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
@@ -1180,9 +1459,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1231,9 +1514,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1333,9 +1620,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1384,9 +1675,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1503,9 +1798,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1567,9 +1866,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1685,9 +1988,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1749,9 +2056,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1835,9 +2146,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0034,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0034
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1850,9 +2165,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0053,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0053
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1868,9 +2187,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -1883,9 +2206,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0036,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0036
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2118,9 +2445,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0293,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0293
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2182,9 +2513,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0293,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0293
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2316,9 +2651,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2331,9 +2670,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0054,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0054
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2349,9 +2692,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2417,9 +2764,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2508,9 +2859,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2572,9 +2927,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2630,9 +2989,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.05,
- "records": 1,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 1,
+ "cost": 10.05
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2769,9 +3132,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2836,9 +3203,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2887,9 +3258,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0051,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -2950,6 +3325,24 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"t2"
] /* constant_tables */,
"records_estimation": [
+ {
+ "table": "t1",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
+ "table": "t2",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ }
] /* records_estimation */
},
{
@@ -3066,6 +3459,33 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "table": "t3",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
+ "table": "t1",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
+ "table": "t2",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
"table": "t4",
"table_scan": {
"records": 2,
@@ -3099,12 +3519,12 @@ trace
{
"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
@@ -3171,6 +3591,33 @@ trace
] /* constant_tables */,
"records_estimation": [
{
+ "table": "t3",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
+ "table": "t1",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
+ "table": "t2",
+ "found_records": 1,
+ "records": 1,
+ "cost": 1,
+ "worst_seeks": 1,
+ "exit_find_best_accessplan": true,
+ "cause": "is_system_table"
+ },
+ {
"table": "t4",
"table_scan": {
"records": 2,
@@ -3188,12 +3635,12 @@ trace
{
"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
@@ -3282,9 +3729,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0034,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0034
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3297,9 +3748,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 4.0068,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 4.0068
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3422,9 +3876,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0044
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3494,9 +3952,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0044
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3595,10 +4057,15 @@ trace
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
- "records": 2,
- "chosen": true
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0044
+ } /* with_where_cond_processing */,
+ "chosen": true,
+ "use_temp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
@@ -3610,9 +4077,13 @@ trace
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0045,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0045
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3628,9 +4099,13 @@ trace
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0044,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0044
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -3715,6 +4190,57 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
0 {
"top_query": "update D set d=5 where D is NULL",
"steps": [
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5034
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "d",
+ "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_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "range_scan",
+ "index": "d",
+ "records": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */,
+ "total_records": 1,
+ "total_cost": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */,
+ "records": 1
+ } /* range_analysis */
+ }
] /* steps */
} 0 0
delete from D where d=5;
@@ -3725,6 +4251,53 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"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",
+ "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": {
+ "table_read_plan_type": "range_scan",
+ "index": "d",
+ "records": 1,
+ "ranges": [
+ "5 <= d <= 5"
+ ] /* ranges */,
+ "total_records": 1,
+ "total_cost": 2.21,
+ "chosen": true
+ } /* chosen_range_access_summary */,
+ "records": 1
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -3775,9 +4348,53 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.6465,
+ "chosen": true
+ } /* best_full_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": "higher_cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 3
+ } /* range_analysis */
}
] /* records_estimation */
},
@@ -3788,9 +4405,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.2051,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.2051
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4009,9 +4630,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4082,9 +4707,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4152,9 +4781,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4252,9 +4885,53 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_full_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": "higher_cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 4
+ } /* range_analysis */
},
{
"table": "t2",
@@ -4279,9 +4956,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0212
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4304,9 +4985,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0212
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4323,14 +5008,18 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"access_type": "index",
"index": "d",
- "cost": 3,
"records": 1,
+ "cost": 3,
"chosen": true
},
{
- "access_type": "table scan",
- "cost": 2.6076,
- "records": 1,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 1,
+ "cost": 2.6076
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4357,12 +5046,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"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
@@ -4379,9 +5068,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0213,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0213
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4399,9 +5092,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0213,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0213
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4441,35 +5138,18 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0212,
- "records": 3,
+ "access_type": "scan",
+ "using_range_access": false,
+ "with_where_cond_processing": {
+ "records": 3,
+ "cost": 2.0212
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */
} /* reconsidering_access_paths_for_semijoin */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
- "attached_conditions": [
- {
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
- },
- {
- "table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
+ } 662 0
0 {
"top_query": "insert into t1 values(\"z\",0)",
"steps": [
@@ -4480,6 +5160,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */,
+ "records": 3
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4520,35 +5209,8 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0154,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
- {
- "table": "t1",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
+ "access_type": "scan",
+ "using_range_access 1012 0
set optimizer_trace_offset=2, optimizer_trace_limit=1|
select * from D where d in (select f1() from t2 where s="c")|
d
@@ -4559,6 +5221,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */,
+ "records": 3
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -4614,9 +5285,33 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -4672,9 +5367,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4765,9 +5464,33 @@ select @trace;
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_full_index_scan */,
+ "impossible_select": true,
+ "stop_range_analysis": true,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 0
+ } /* range_analysis */,
+ "records": 0,
+ "cause": "impossible_where_condition"
}
] /* records_estimation */
}
@@ -4869,9 +5592,53 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"records_estimation": [
{
"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",
+ "key_parts": [
+ "d"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_full_index_scan": {
+ "index": "d",
+ "cost": 1.8698,
+ "chosen": true
+ } /* best_full_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": "higher_cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ } /* analyzing_ror_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "chosen": false
+ } /* chosen_range_access_summary */,
+ "records": 4
+ } /* range_analysis */
},
{
"table": "t2",
@@ -4896,9 +5663,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
- "records": 7,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 7,
+ "cost": 2.0496
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4923,12 +5694,12 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
{
"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
@@ -4945,9 +5716,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
- "records": 7,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 7,
+ "cost": 2.0496
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -4965,9 +5740,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
- "records": 7,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 7,
+ "cost": 2.0496
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5003,9 +5782,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
- "records": 7,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 7,
+ "cost": 2.0496
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5025,35 +5808,18 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0496,
- "records": 7,
+ "access_type": "scan",
+ "using_range_access": false,
+ "with_where_cond_processing": {
+ "records": 7,
+ "cost": 2.0496
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */
} /* reconsidering_access_paths_for_semijoin */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
- "attached_conditions": [
- {
- "table": "D",
- "attached": "(`test`.`D`.`d` = `f1`())"
- },
- {
- "table": "t2",
- "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
+ } 666 0
0 {
"top_query": "insert into t1 values(\"z\",0)",
"steps": [
@@ -5064,6 +5830,15 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"steps": [
{
"expanded_query": "/* select#1 */ select from dual where (`id` = 'z')"
+ },
+ {
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7154
+ } /* table_scan */,
+ "records": 3
+ } /* range_analysis */
}
] /* steps */
} 0 0
@@ -5104,40 +5879,11 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
- "chosen": true
- }
- ] /* considered_access_paths */
- } /* best_access_path */,
- "cost_for_plan": 2.0154,
- "records_for_plan": 2,
- "chosen": true
- }
- ] /* considered_execution_plans */
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": null,
- "attached_conditions": [
- {
- "table": "t1",
- "attached": null
- }
- ] /* attached_conditions */
- } /* attaching_conditions_to_tables */
- }
- ] /* steps */
- } /* join_optimization */
- }
- ] /* steps */
-} 0 0
-0 {
- "top_query": "SET optimizer_trace=\"enabled=off\"",
- "steps": [
- ] /* steps */
-} 0 0
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing 905 0
+0 84 0
select @@optimizer_trace|
@@optimizer_trace
enabled=off,end_marker=on,one_line=off
@@ -5210,9 +5956,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5305,9 +6055,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 2.0154,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 2.0154
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5376,9 +6130,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 10.1
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5462,9 +6220,13 @@ QUERY_ID TRACE MISSING_BYTES_BEYOND_MAX_
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 10.1
+ } /* with_where_cond_processing */,
"chosen": true
}
] /* considered_access_paths */
@@ -5562,9 +6324,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "table scan",
- "cost": 10.1,
- "records": 2,
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 10.1
+ },
"chosen": true
}
]
@@ -5593,3 +6359,1365 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}drop table t1,t2;
DROP TABLE C,D;
set optimizer_trace=default;
+SET optimizer_trace="enabled=on,end_marker=off,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,
+INDEX 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 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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "key_parts": [
+ "key1"
+ ]
+ },
+ {
+ "index": "i2",
+ "key_parts": [
+ "key2"
+ ]
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ }
+ ],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "key1 < 3"
+ ],
+ "index_only": true,
+ "records": 3,
+ "cost": 1.6526,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ],
+ "index_to_merge": "i1",
+ "new_cost": 1.6526
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "1020 < key2"
+ ],
+ "index_only": true,
+ "records": 42,
+ "cost": 10.282,
+ "rowid_ordered": false,
+ "chosen": true
+ }
+ ],
+ "index_to_merge": "i2",
+ "new_cost": 11.935
+ }
+ ],
+ "scan_cost": 11.935,
+ "total_cost": 58.363
+ }
+ ],
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "index_merge",
+ "index_merge_of": [
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i1",
+ "records": 3,
+ "ranges": [
+ "key1 < 3"
+ ]
+ },
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i2",
+ "records": 42,
+ "ranges": [
+ "1020 < key2"
+ ]
+ }
+ ],
+ "total_records": 45,
+ "total_cost": 58.363,
+ "chosen": true
+ },
+ "records": 45
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_range_access": true,
+ "with_where_cond_processing": {
+ "records": 45,
+ "cost": 58.363
+ },
+ "chosen": true
+ }
+ ]
+ },
+ "cost_for_plan": 58.363,
+ "records_for_plan": 45,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "i2_1",
+ "i2_2"
+ ],
+ "cause": "group_by"
+ },
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1a",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i2_1",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ },
+ {
+ "index": "i2_2",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ }
+ ],
+ "best_full_index_scan": {
+ "index": "i2_1",
+ "cost": 235.03,
+ "chosen": false
+ },
+ "group_index_range": {
+ "potential_group_range_indices": [
+ {
+ "index": "i1a",
+ "usable": false,
+ "covering_index": false
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "covering_index": false
+ },
+ {
+ "index": "i2_1",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ },
+ {
+ "index": "i2_2",
+ "covering": true,
+ "records": 103,
+ "cost": 50.6
+ }
+ ]
+ },
+ "best_group_range_summary": {
+ "table_read_plan_type": "index_group",
+ "index": "i2_1",
+ "records": 103,
+ "cost": 50.6
+ },
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "index_group",
+ "index": "i2_1",
+ "records": 103,
+ "cost": 50.6,
+ "total_records": 103,
+ "total_cost": 50.6,
+ "chosen": true
+ },
+ "records": 103
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_range_access": true,
+ "with_where_cond_processing": {
+ "records": 103,
+ "cost": 50.6
+ },
+ "chosen": true,
+ "use_temp_table": true
+ }
+ ]
+ },
+ "cost_for_plan": 50.6,
+ "records_for_plan": 103,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions": [
+ {
+ "table": "t2",
+ "attached": null
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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`))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t2.key2= 1",
+ "null_rejecting": false
+ },
+ {
+ "condition": "t2.key2= 1",
+ "null_rejecting": false
+ }
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1a",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i2_1",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ },
+ {
+ "index": "i2_2",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ }
+ ],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "i2_1",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ],
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i2_2",
+ "ranges": [
+ "1 <= key2 <= 1"
+ ],
+ "index_only": false,
+ "records": 10,
+ "cost": 13.01,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "higher_cost"
+ }
+ ],
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ }
+ },
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "range_scan",
+ "index": "i2_1",
+ "records": 10,
+ "ranges": [
+ "1 <= key2 <= 1"
+ ],
+ "total_records": 10,
+ "total_cost": 13.01,
+ "chosen": true
+ },
+ "records": 10
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "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": "scan",
+ "chosen": false,
+ "cause": "heuristic_index_cheaper"
+ }
+ ]
+ },
+ "cost_for_plan": 10,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))",
+ "attached_conditions": [
+ {
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i2",
+ "key_parts": [
+ "key2"
+ ]
+ },
+ {
+ "index": "i3",
+ "key_parts": [
+ "key3"
+ ]
+ },
+ {
+ "index": "i4",
+ "key_parts": [
+ "key4"
+ ]
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ }
+ ],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "10 <= key2 <= 10"
+ ],
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ],
+ "index_to_merge": "i2",
+ "new_cost": 2.21
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i3",
+ "ranges": [
+ "3 <= key3 <= 3"
+ ],
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ }
+ ],
+ "index_to_merge": "i3",
+ "new_cost": 4.42
+ }
+ ],
+ "scan_cost": 4.42,
+ "use_ror_union": true,
+ "cause": "always_cheaper_than_non_ror",
+ "analyzing_ror_scans": [
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ],
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ }
+ },
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ],
+ "analyzing_ror_intersect": {
+ "usable": false,
+ "cause": "too_few_ror_scans"
+ }
+ }
+ ],
+ "index_roworder_union_cost": 6.327,
+ "members": 2,
+ "chosen": true
+ }
+ ],
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "index_roworder_union",
+ "union_of": [
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i2",
+ "records": 1,
+ "ranges": [
+ "10 <= key2 <= 10"
+ ]
+ },
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "i3",
+ "records": 1,
+ "ranges": [
+ "3 <= key3 <= 3"
+ ]
+ }
+ ],
+ "total_records": 2,
+ "total_cost": 6.327,
+ "chosen": true
+ },
+ "records": 2
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_range_access": true,
+ "with_where_cond_processing": {
+ "records": 2,
+ "cost": 6.327
+ },
+ "chosen": true
+ }
+ ]
+ },
+ "cost_for_plan": 6.327,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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`))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1a",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i1b",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i2_1",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ },
+ {
+ "index": "i2_2",
+ "key_parts": [
+ "key2",
+ "key2_1"
+ ]
+ }
+ ],
+ "range_scan_possible": false,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "chosen_range_access_summary": {
+ "chosen": false
+ },
+ "records": 1024
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 1024,
+ "cost": 8.25
+ },
+ "chosen": true
+ }
+ ]
+ },
+ "cost_for_plan": 8.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))",
+ "attached_conditions": [
+ {
+ "table": "t2",
+ "attached": "((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+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 TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 217.15
+ },
+ "potential_range_indices": [
+ {
+ "index": "i1",
+ "key_parts": [
+ "key1"
+ ]
+ },
+ {
+ "index": "i2",
+ "key_parts": [
+ "key2"
+ ]
+ },
+ {
+ "index": "i3",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i4",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i5",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i6",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i7",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ },
+ {
+ "index": "i8",
+ "usable": false,
+ "cause": "not_in_keys_to_use"
+ }
+ ],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "analyzing_index_merge": [
+ {
+ "indices_to_merge": [
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i1",
+ "ranges": [
+ "1 < key1"
+ ],
+ "index_only": true,
+ "records": 1023,
+ "cost": 227.35,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "higher_cost"
+ }
+ ],
+ "chosen": false,
+ "cause": "expensive"
+ },
+ {
+ "range_scan_alternatives": [
+ {
+ "index": "i2",
+ "ranges": [
+ "2 < key2"
+ ],
+ "index_only": true,
+ "records": 1022,
+ "cost": 227.13,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "higher_cost"
+ }
+ ],
+ "chosen": false,
+ "cause": "expensive"
+ }
+ ],
+ "scan_cost": 0,
+ "chosen": false,
+ "cause": "more_expensive_than_table_scan"
+ }
+ ],
+ "chosen_range_access_summary": {
+ "chosen": false
+ },
+ "records": 1024
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "using_range_access": false,
+ "using_join_cache": true,
+ "with_where_cond_processing": {
+ "records": 1024,
+ "cost": 10.25
+ },
+ "chosen": true
+ }
+ ]
+ },
+ "cost_for_plan": 10.25,
+ "records_for_plan": 1024,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+
+DROP TABLE t1,t2;
+CREATE TABLE t1 (
+cola char(3) not null,
+colb char(3) not null,
+filler char(200),
+key(cola),
+key(colb)
+) engine=innodb;
+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 31 Using intersect(cola,colb); Using where
+
+SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
+TRACE
+{
+ "top_query": "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": [
+ ]
+ }
+ },
+ {
+ "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`))"
+ }
+ },
+ {
+ "ref-optimizer-key-uses": [
+ {
+ "condition": "t1.cola= 'foo'",
+ "null_rejecting": false
+ },
+ {
+ "condition": "t1.colb= 'bar'",
+ "null_rejecting": false
+ }
+ ]
+ },
+ {
+ "constant_tables": [
+ ],
+ "records_estimation": [
+ {
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 8225,
+ "cost": 1808.1
+ },
+ "potential_range_indices": [
+ {
+ "index": "cola",
+ "key_parts": [
+ "cola"
+ ]
+ },
+ {
+ "index": "colb",
+ "key_parts": [
+ "colb"
+ ]
+ }
+ ],
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ },
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "cola",
+ "ranges": [
+ "foo <= cola <= foo"
+ ],
+ "index_only": false,
+ "records": 512,
+ "cost": 615.41,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "colb",
+ "ranges": [
+ "bar <= colb <= bar"
+ ],
+ "index_only": false,
+ "records": 512,
+ "cost": 615.41,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "higher_cost"
+ }
+ ],
+ "analyzing_ror_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "cola",
+ "used_in_intersect": true,
+ "matching_records_now": 512,
+ "cost_now": 518.14,
+ "covering_now": false
+ },
+ {
+ "index": "colb",
+ "used_in_intersect": true,
+ "matching_records_now": 31.872,
+ "cost_now": 34.399,
+ "covering_now": false
+ }
+ ],
+ "clustered_pk": {
+ "cpk_added_to_intersect": false,
+ "cause": "no_clustered_pk_index"
+ },
+ "records": 31,
+ "cost": 34.399,
+ "is_covering": false,
+ "cpk_scan": false,
+ "chosen": true
+ }
+ },
+ "chosen_range_access_summary": {
+ "table_read_plan_type": "index_roworder_intersect",
+ "records": 31,
+ "cost": 34.399,
+ "is_covering": false,
+ "cpk_scan": false,
+ "intersect_of": [
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "cola",
+ "records": 512,
+ "ranges": [
+ "foo <= cola <= foo"
+ ]
+ },
+ {
+ "table_read_plan_type": "range_scan",
+ "index": "colb",
+ "records": 512,
+ "ranges": [
+ "bar <= colb <= bar"
+ ]
+ }
+ ],
+ "total_records": 31,
+ "total_cost": 34.399,
+ "chosen": true
+ },
+ "records": 31
+ }
+ }
+ ]
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "index",
+ "index": "cola",
+ "records": 512,
+ "cost": 483,
+ "chosen": true
+ },
+ {
+ "access_type": "index",
+ "index": "colb",
+ "records": 512,
+ "cost": 483,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "using_range_access": true,
+ "with_where_cond_processing": {
+ "records": 24,
+ "cost": 35.799
+ },
+ "chosen": true
+ }
+ ]
+ },
+ "cost_for_plan": 35.799,
+ "records_for_plan": 24,
+ "chosen": true
+ }
+ ]
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))",
+ "attached_conditions": [
+ {
+ "table": "t1",
+ "attached": "((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))"
+ }
+ ]
+ }
+ }
+ ]
+ }
+ }
+ ]
+}
+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-07 09:02:31 +0000
@@ -4381,14 +4381,12 @@ 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-07 09:02:31 +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,
+ KEY_PART_INFO *key_parts,
+ const uchar *min_key, uint16 min_length,
+ const uchar *max_key, uint16 max_length,
+ uint flag);
#endif
static SEL_TREE *tree_and(RANGE_OPT_PARAM *param,SEL_TREE *tree1,SEL_TREE *tree2);
@@ -1955,13 +1955,69 @@ public:
static void operator delete(void *ptr,size_t size) { TRASH(ptr, size); }
static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ }
virtual ~TABLE_READ_PLAN() {} /* Remove gcc warning */
+ /**
+ Return the name of the TABLE_READ_PLAN ("range_scan", "index_merge" etc)
+
+ @return The name of the plan, zero terminated
+ */
+ virtual const char *get_name() const = 0;
+ /**
+ Add basic info for this TABLE_READ_PLAN to the optimizer trace.
+ @param param Parameters for range analysis of this table
+ @param trace The optimizer trace object the info is appended to
+ */
+ virtual void trace_basic_info(const PARAM *param,
+ Opt_trace_object *trace) const = 0;
+
};
-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, 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())
+ {
+ 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);
+}
/*
Plan for a QUICK_RANGE_SELECT scan.
@@ -1996,8 +2052,54 @@ public:
}
DBUG_RETURN(quick);
}
+ const char *get_name() const {return "range_scan";}
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add("table_read_plan_type", get_name()).
+ add("index", param->table->key_info[param->real_keynr[key_idx]].name).
+ add("records", records);
+
+ Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
+ KEY_PART_INFO *key_part=
+ param->table->key_info[param->real_keynr[key_idx]].key_part;
+ for (SEL_ARG *current= key; current; current= current->next)
+ {
+ String multikey;
+ for (SEL_ARG *part= current; part; part= part->next_key_part)
+ {
+ int length= (key_part+part->part)->length;
+ append_range(&multikey, key_part+part->part,
+ part->min_value, length,
+ part->max_value, length,
+ part->min_flag|part->max_flag);
+ }
+ trace_range.add(multikey.c_ptr());
+ }
+ }
};
+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 +2117,48 @@ 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)) */
+ const char *get_name() const {return "index_roworder_intersect";}
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add("table_read_plan_type", get_name()).
+ add("records", records).
+ add("cost", read_cost).
+ add("is_covering", is_covering).
+ add("cpk_scan", cpk_scan ? true : false);
+
+ Opt_trace_array ota(param->thd->opt_trace,"intersect_of");
+ for (st_ror_scan_info **cur_scan= first_scan;
+ cur_scan != last_scan;
+ cur_scan++)
+ {
+ KEY key= param->table->key_info[param->real_keynr[(*cur_scan)->keynr]];
+
+ Opt_trace_object trace_isect_idx(param->thd->opt_trace);
+ trace_isect_idx.add("table_read_plan_type", "range_scan").
+ add("index", key.name).
+ add("records", (*cur_scan)->records);
+ {
+ Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
+ for (SEL_ARG *current= (*cur_scan)->sel_arg;
+ current;
+ current= current->next)
+ {
+ String range;
+ for (SEL_ARG *part= current;
+ part;
+ part= part->next_key_part)
+ {
+ KEY_PART_INFO *key_part= key.key_part+part->part;
+ append_range(&range, key_part,
+ part->min_value, key_part->length,
+ part->max_value, key_part->length,
+ part->min_flag|part->max_flag);
+ }
+ trace_range.add(range.c_ptr());
+ }
+ }
+ }
+ }
};
@@ -2033,6 +2177,19 @@ 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 */
+ const char *get_name() const {return "index_roworder_union";}
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add("table_read_plan_type", get_name());
+ 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);
+ }
+ }
};
@@ -2051,6 +2208,19 @@ 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 */
+ const char *get_name() const {return "index_merge";}
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add("table_read_plan_type", get_name());
+ 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);
+ }
+ }
};
@@ -2078,6 +2248,15 @@ public:
/* Number of records selected by the ranges in index_tree. */
ha_rows quick_prefix_records;
public:
+ const char *get_idx_name() const {return index_info->name;}
+ const char *get_name() const {return "index_group";}
+ void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+ {
+ trace->add("table_read_plan_type", get_name()).
+ add("index", get_idx_name()).
+ add("records", records).
+ add("cost", read_cost);
+ }
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 +2425,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 +2479,7 @@ 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");
/*
Make an array with description of all key parts of all table keys.
This is used in get_mm_parts function.
@@ -2312,17 +2487,25 @@ 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("index", key_info->name);
KEY_PART_INFO *key_part_info;
if (!keys_to_use.is_set(idx))
+ {
+ trace_idx_details.add("usable", false).
+ add("cause", "not_in_keys_to_use");
continue;
+ }
if (key_info->flags & HA_FULLTEXT)
+ {
+ trace_idx_details.add("usable", false).
+ add("cause", "fulltext");
continue; // ToDo: ft-keys in non-ft ranges, if possible SerG
+ }
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 +2519,7 @@ 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(key_parts->field->field_name);
}
param.real_keynr[param.keys++]=idx;
}
@@ -2359,12 +2543,10 @@ 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(thd->opt_trace, "best_full_index_scan").
+ add("index", head->key_info[key_for_use].name).
+ add("cost", key_read_time).
+ add("chosen", chosen);
}
TABLE_READ_PLAN *best_trp= NULL;
@@ -2377,6 +2559,8 @@ int SQL_SELECT::test_quick_select(THD *t
{
if (tree->type == SEL_TREE::IMPOSSIBLE)
{
+ trace_range.add("impossible_select", true).
+ add("stop_range_analysis", true);
records=0L; /* Return -1 from this function. */
read_time= (double) HA_POS_ERROR;
goto free_mem;
@@ -2386,7 +2570,10 @@ 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)
+ {
+ trace_range.add("range_scan_possible", false);
tree= NULL;
+ }
}
}
@@ -2399,6 +2586,9 @@ 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");
+ group_trp->trace_basic_info(¶m, &grp_summary);
+
if (group_trp->read_cost < best_read_time)
{
best_trp= group_trp;
@@ -2414,6 +2604,8 @@ int SQL_SELECT::test_quick_select(THD *t
*/
if (tree->merges.is_empty())
{
+ Opt_trace_object trace_range(thd->opt_trace,
+ "analyzing_range_alternatives");
TRP_RANGE *range_trp;
TRP_ROR_INTERSECT *rori_trp;
bool can_build_covering= FALSE;
@@ -2450,7 +2642,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_ror_intersect_covering", true);
best_trp= rori_trp;
+ }
}
}
}
@@ -2465,18 +2660,26 @@ 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");
+ 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;
}
}
}
@@ -2492,20 +2695,68 @@ int SQL_SELECT::test_quick_select(THD *t
delete quick;
quick= NULL;
}
+ else
+ {
+ }
+ }
+
+free_mem:
+ Opt_trace_object trace_range_summary(thd->opt_trace,
+ "chosen_range_access_summary");
+ if (quick)
+ {
+ /* Guilhem question: I've worked with two alternative ways of
+ printing the chosen range access.
+
+ 1) Use trace_basic_info() of the TABLE_READ_PLAN object. This
+ can print multi-key-part ranges separately (I prefer this)
+
+ 2) Use dbug_dump() of the QUICK_SELECT object. Maybe
+ conceptually more correct since it is what will be
+ executed, but I cannot make it print multi-key-part ranges
+ in an understandable way (and I tried a lot). This is
+ Sergey's way of doing it.
+
+ Consider
+ SELECT * FROM t2 WHERE key1a = 3 and key1b < 10 and key2=0
+
+ 1) will print
+ "ranges": [
+ {
+ "3 <= field1 <= 3" : "field2 < 10"
+ ]
+ }
+ ],
+
+ 2) will print
+ "ranges": [
+ "3 <= X < 3'10" <- must print X, not field name
+ ]
+ */
+ // 1
+ best_trp->trace_basic_info(¶m, &trace_range_summary);
+
+ // 2 (note: currently prints field name of first field in index,
+ // but that will be changed if we go for this option)
+ /*
+ print_quick(quick, &needed_reg);
+ */
+
+ trace_range_summary.add("total_records", quick->records).
+ add("total_cost", quick->read_time).
+ add("chosen", true);
+ }
+ else
+ {
+ trace_range_summary.add("chosen", false);
+ print_quick(quick, &needed_reg);
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto.add(param.table->quick_condition_rows, "quick_condition_rows");
-#endif
- free_mem:
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
+ trace_range.add("records", records);
/*
Assume that if the user is using 'limit' we will only need to scan
@@ -3837,48 +4088,59 @@ 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_object trace_best_disjunct(param->thd->opt_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(param->thd->opt_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(param->thd->opt_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.
+ */
+ trace_idx.add("chosen", false).add("cause", "expensive");
+ imerge_too_expensive= TRUE;
+ }
+ if (imerge_too_expensive)
+ 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 real_key= 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 &&
+ real_key == 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("index_to_merge", param->table->key_info[real_key].name).
+ add("new_cost", imerge_cost);
}
- else
- non_cpk_scan_records += (*cur_child)->records;
}
-
+ trace_best_disjunct.add("scan_cost", imerge_cost);
DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost));
if (imerge_too_expensive || (imerge_cost > read_time) ||
((non_cpk_scan_records+cpk_scan_records >= param->table->file->stats.records) &&
@@ -3890,6 +4152,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("cause", "more_expensive_than_table_scan");
DBUG_RETURN(NULL);
}
@@ -3902,6 +4166,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_ror_union", true).
+ add("cause", "always_cheaper_than_non_ror");
goto skip_to_ror_scan;
}
@@ -3928,6 +4194,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
if (imerge_cost > read_time ||
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
{
+ trace_best_disjunct.add("use_ror_index_merge", true).
+ add("cause", "index_merge_cheaper");
goto build_ror_index_merge;
}
@@ -3948,6 +4216,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
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("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)
@@ -3983,44 +4252,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(param->thd->opt_trace, "analyzing_ror_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(param->thd->opt_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 +4329,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 +4344,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 +4870,17 @@ 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_ror_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("cause", "too_few_ror_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 +4947,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);
+ trace_idx.add("index",
+ param->table->key_info[(*cur_ror_scan)->keynr].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("cause", "cost_not_reduced");
+ 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("cost_now", intersect->total_cost).
+ add("covering_now", 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 +5006,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("new_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("cause", "increased_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("cause", "ror_is_covering")
+ : trace_cpk.add("cause", "no_clustered_pk_index");
}
/* Ok, return ROR-intersect plan if we have found one */
@@ -4755,10 +5046,19 @@ 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("is_covering", trp->is_covering).
+ add("cpk_scan", cpk_scan_used).
+ 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 +5105,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_ror_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 +5270,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 +5290,38 @@ 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("index", 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("cause", "higher_cost");
+
}
}
@@ -5037,10 +5337,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 +5396,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))
@@ -7632,8 +7930,28 @@ 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 +8009,8 @@ 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;
+
if (seq->at_start)
{
key_tree= seq->start;
@@ -7706,7 +8026,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 +8047,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 +8101,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 +8159,18 @@ 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());
+ Opt_trace_array *trace_range=
+ (Opt_trace_array*)seq->param->thd->opt_trace->get_current_struct();
+ trace_range->add(key_range_trace.c_ptr());
+ }
return 0;
}
@@ -9722,14 +10043,29 @@ 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");
+
/* Perform few 'cheap' tests whether this access method is applicable. */
if (!join)
+ {
+ trace_group.add("chosen", false).add("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("cause", "not_single_table");
DBUG_RETURN(NULL);
+ }
+ if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
+ {
+ trace_group.add("chosen", false).add("cause", "is_rollup");
+ DBUG_RETURN(NULL);
+ }
if (table->s->keys == 0) /* There are no indexes to use. */
+ {
+ trace_group.add("chosen", false).add("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 +10077,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("cause", "not_group_by_or_distinct");
DBUG_RETURN(NULL);
+ }
/* Analyze the query in more detail. */
if (join->sum_funcs[0])
@@ -9759,7 +10098,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("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 +10120,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 +10132,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("cause", "group_field_is_expression");
DBUG_RETURN(NULL);
+ }
}
/*
@@ -9810,9 +10158,12 @@ get_best_group_min_max(PARAM *param, SEL
ha_rows cur_quick_prefix_records= 0;
uint cur_param_idx=MAX_KEY;
+ Opt_trace_array ota(thd->opt_trace, "potential_group_range_indices");
for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
cur_index_info++, cur_index++)
{
+ Opt_trace_object trace_idx(thd->opt_trace);
+ trace_idx.add("index", table->key_info[cur_index].name);
KEY_PART_INFO *cur_part;
KEY_PART_INFO *end_part; /* Last part for loops. */
/* Last index part. */
@@ -9832,7 +10183,10 @@ get_best_group_min_max(PARAM *param, SEL
/* Check (B1) - if current index is covering. */
if (!table->covering_keys.is_set(cur_index))
+ {
+ trace_idx.add("usable", false).add("covering_index", false);
goto next_index;
+ }
/*
If the current storage manager is such that it appends the primary key to
@@ -9856,9 +10210,13 @@ get_best_group_min_max(PARAM *param, SEL
*/
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_index", false);
goto next_index; // Field was not part of key
+ }
}
}
+ trace_idx.add("covering", true);
/*
Check (GA1) for GROUP BY queries.
@@ -9887,9 +10245,14 @@ get_best_group_min_max(PARAM *param, SEL
used_key_parts_map.set_bit(max_key_part);
}
else
+ {
+ trace_idx.add("usable", false).
+ add("cause", "group_attribute_not_prefix_in_index");
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
@@ -9916,7 +10279,10 @@ get_best_group_min_max(PARAM *param, SEL
/* not doing loose index scan for derived tables */
if (!item_field->field)
+ {
+ trace_idx.add("usable", false);
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);
@@ -9928,7 +10294,11 @@ get_best_group_min_max(PARAM *param, SEL
continue;
if (key_part_nr < 1 ||
(!is_agg_distinct && key_part_nr > join->fields_list.elements))
+ {
+ trace_idx.add("usable", false).
+ add("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);
@@ -9953,7 +10323,11 @@ get_best_group_min_max(PARAM *param, SEL
{
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("cause", "aggregate_column_not_suffix_in_idx");
goto next_index;
+ }
min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
}
@@ -9994,7 +10368,10 @@ get_best_group_min_max(PARAM *param, SEL
last_part, thd, cur_key_infix,
&cur_key_infix_len,
&first_non_infix_part))
+ {
+ trace_idx.add("usable", false).add("cause", "nonconst_equality_gap_attribute");
goto next_index;
+ }
}
else if (min_max_arg_part &&
(min_max_arg_part - first_non_group_part > 0))
@@ -10003,6 +10380,7 @@ get_best_group_min_max(PARAM *param, SEL
There is a gap but no range tree, thus no predicates at all for the
non-group keyparts.
*/
+ trace_idx.add("usable", false).add("cause", "no_nongroup_keypart_predicate");
goto next_index;
}
else if (first_non_group_part && join->conds)
@@ -10026,7 +10404,11 @@ get_best_group_min_max(PARAM *param, SEL
/* 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("cause", "indexpart_reference_from_where_clause");
goto next_index;
+ }
}
}
@@ -10041,7 +10423,11 @@ get_best_group_min_max(PARAM *param, SEL
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("cause", "keypart_after_infix_in_query");
goto next_index;
+ }
}
}
@@ -10075,6 +10461,8 @@ get_best_group_min_max(PARAM *param, SEL
Do not compare doubles directly because they may have different
representations (64 vs. 80 bits).
*/
+ trace_idx.add("records", cur_records);
+ trace_idx.add("cost", cur_read_cost);
if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
{
index_info= cur_index_info;
@@ -10102,8 +10490,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("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 +10514,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 +12174,7 @@ 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());
+ fprintf(DBUG_FILE,"SEL_TREE: %p (%s) scans: %s\n", tree, msg, tmp.ptr());
DBUG_VOID_RETURN;
}
@@ -11822,87 +12214,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,
+ KEY_PART_INFO *key_parts,
+ const uchar *min_key, uint16 min_length,
+ const uchar *max_key, uint16 max_length,
+ 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];
+ if (out->length() > 0)
+ out->append(" : ");
- 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);
-}
-
-
-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(" < ");
else
- tmp.append(" <= ");
+ out->append(" <= ");
}
- tmp.append("X");
+ out->append(key_parts->field->field_name);
if (!(flag & NO_MAX_RANGE))
{
if (flag & NEAR_MAX)
- tmp.append(" < ");
+ out->append(" < ");
else
- tmp.append(" <= ");
- print_key2(&tmp, key_parts, max_key, max_length);
+ out->append(" <= ");
+ 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 +12280,17 @@ 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;
/* 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_object oto(out, "range_scan");
+ oto.add("index", head->key_info[index].name).
+ add("key_length", max_used_key_length).
+ add("used_keyparts", used_key_parts);
- Opt_trace_array ota(out, "ranges");
-#endif
+ Opt_trace_array trace_range(out, "ranges");
if (TRUE)
{
QUICK_RANGE *range;
@@ -11948,72 +12298,66 @@ void QUICK_RANGE_SELECT::dbug_dump(int i
QUICK_RANGE **end_range= pr + ranges.elements;
for (; pr != end_range; ++pr)
{
+ String range_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(range_info.c_ptr());
}
}
/* 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 +12383,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("index", 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-07 09:02:31 +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,14 @@ 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 inser/update/deletes with condition.
+ test_quick_select adds tracing with keys, and we are currently
+ in a trace array that does not accept keys. Todo: move this
+ wrapper trace object to sql_insert/update/delete where it is
+ more meaningful.
+ */
+ 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/sql_select.cc'
--- a/sql/sql_select.cc 2010-10-05 13:19:55 +0000
+++ b/sql/sql_select.cc 2010-10-07 09:02:31 +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(THD *thd,
+ JOIN_TAB *join_tab,
+ const key_map new_keys,
+ char* cause);
static bool replace_subcondition(JOIN *join, Item **tree,
Item *old_cond, Item *new_cond,
bool do_fix_fields);
@@ -4943,14 +4947,28 @@ 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("table", s->table->alias);
if (s->type == JT_SYSTEM || s->type == JT_CONST)
{
+ trace_table.add("found_records", 1).
+ add("records", 1).
+ add("cost", 1).
+ add("worst_seeks", 1.0);
+
+ if (s->ref.key_parts)
+ trace_table.add("index", (s->table->key_info+ s->ref.key)->name);
+
+ trace_table.add("exit_find_best_accessplan", true);
+ if (s->type == JT_SYSTEM)
+ trace_table.add("cause", "is_system_table");
+ else
+ trace_table.add("cause", "is_const_table");
+
/* Only one matching row */
s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
continue;
}
- Opt_trace_object oto1(join->thd->opt_trace);
- oto1.add("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();
@@ -4981,10 +4999,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("TODO?", "yes!");
-#endif
ha_rows records;
SQL_SELECT *select;
select= make_select(s->table, found_const_table_map,
@@ -5013,9 +5027,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("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("cause", "impossible_where_condition");
}
}
if (records != HA_POS_ERROR)
@@ -6494,11 +6515,14 @@ add_group_and_distinct_keys(JOIN *join,
Item_field *cur_item;
key_map possible_keys;
+ 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\0";
}
else if (join->select_distinct)
{ /* Collect all query fields referenced in the SELECT clause. */
@@ -6508,10 +6532,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\0";
}
else if (is_indexed_agg_distinct(join, &indexed_fields))
{
join->sort_and_group= 1;
+ cause= (char*) "indexed_distinct_aggregate";
}
else
return;
@@ -6527,8 +6553,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))
+ {
+ trace_indices_added_group_distinct(join->thd, 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(THD *thd,
+ JOIN_TAB *join_tab,
+ const key_map new_keys,
+ char* cause)
+{
+ // Do nothing if optimizer trace is not enabled
+ if (!thd->opt_trace || !thd->opt_trace->is_started())
+ return;
+
+ KEY *key_info= join_tab->table->key_info;
+ key_map existing_keys= join_tab->const_keys;
+ uint nokeys= join_tab->table->s->keys;
+
+ Opt_trace_object trace_summary(thd->opt_trace, "const_keys_added");
+ {
+ Opt_trace_array trace_key(thd->opt_trace,"keys");
+ for (uint j=0 ; j < nokeys ; j++)
+ if (new_keys.is_set(j) && !existing_keys.is_set(j))
+ trace_key.add(key_info[j].name);
+ }
+ trace_summary.add("cause", cause);
}
@@ -6970,8 +7037,8 @@ 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 oto1(thd->opt_trace);
- oto1.add("access_type", "index").add("index", keyinfo->name);
+ Opt_trace_object trace_access_idx(thd->opt_trace);
+ trace_access_idx.add("access_type", "index").add("index", keyinfo->name);
/*
True if we find some keys from the range optimizer that match more
@@ -7027,7 +7094,7 @@ best_access_path(JOIN *join,
*/
if (!found_part && !ft_key && !loose_scan_opt.have_a_case())
{
- oto1.add("usable", false);
+ trace_access_idx.add("usable", false);
goto done_with_index; // Nothing usable found
}
@@ -7306,7 +7373,7 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
- oto1.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 &&
@@ -7321,13 +7388,13 @@ best_access_path(JOIN *join,
best_ref_depends_map= found_ref;
}
done_with_index:
- oto1.add("chosen", best_key == start_key);
+ trace_access_idx.add("chosen", best_key == start_key);
} /* for each key */
records= best_records;
}
- Opt_trace_object oto1(thd->opt_trace);
- oto1.add("access_type", "table scan");
+ Opt_trace_object trace_access_scan(thd->opt_trace);
+ trace_access_scan.add("access_type", "scan");
/*
Don't test table scan if it can't be better.
Prefer key lookup if we would use the same key for scanning.
@@ -7358,27 +7425,31 @@ best_access_path(JOIN *join,
*/
if (!(records >= s->found_records || best > s->read_time)) // (1)
{
- oto1.add("cost", s->read_time).add("records", s->found_records);
+ trace_access_scan.add("cost", s->read_time).
+ add("records", s->found_records).
+ add("chosen", false);
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)
{
- oto1.add("heuristic_index_must_be_cheaper", true);
+ trace_access_scan.add("chosen", false).
+ add("cause", "heuristic_index_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)
{
- oto1.add("index_is_covering", true);
+ trace_access_scan.add("chosen", false).
+ add("cause", "covering_index_better_than_full_scan");
goto skip_table_scan;
}
if ((s->table->force_index && best_key && !s->quick)) // (4)
{
- oto1.add("FORCE_INDEX_used", true);
+ trace_access_scan.add("chosen", false).add("cause", "force_index");
goto skip_table_scan;
}
@@ -7411,6 +7482,7 @@ best_access_path(JOIN *join,
if (s->quick)
{
+ trace_access_scan.add("using_range_access", true);
/*
For each record we:
- read record range through 'quick'
@@ -7428,6 +7500,7 @@ best_access_path(JOIN *join,
}
else
{
+ trace_access_scan.add("using_range_access", false);
/* Estimate cost of reading table. */
if (s->table->force_index && !best_key)
tmp= s->table->file->read_time(s->ref.key, 1, s->records);
@@ -7447,6 +7520,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
@@ -7466,7 +7540,8 @@ best_access_path(JOIN *join,
}
}
- oto1.add("cost", tmp).add("records", rows2double(rnd_records));
+ Opt_trace_object (thd->opt_trace, "with_where_cond_processing").
+ 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
@@ -7489,11 +7564,10 @@ best_access_path(JOIN *join,
join->outer_join)));
}
}
+ trace_access_scan.add("chosen", best_key == NULL);
skip_table_scan:
- oto1.add("chosen", best_key == NULL);
-
/* Update the cost information for the current partial plan */
pos->records_read= records;
pos->read_time= best;
@@ -7509,7 +7583,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;
}
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101007090231-gc6kgk1c7ppd1rgo.bundle
