List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:October 16 2010 4:12pm
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)
WL#5594
View as plain text  
Hello,

I don't see big problems with your approach.
Comments below, prefixed with "GB" (by the way I discovered
control-<downarrow> in emacs today, it's great! when pressed, it leaps
over any contiguous block of lines starting with ">", so reaches my
next comment).

I had two choices: either take a few more days to figure out
everything by myself, or send now with quite a few questions for you,
which I have done.

Regarding the produced trace (as seen in the result file), I find it
understandable enough (after reading it a few times); I guess we don't
need to aim at perfection; if someone later finds a better way to
express this or that key/value pair, she/he can always change it.

Jorgen Loland a écrit, Le 07.10.2010 11:02:
 > #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

GB Ah! now I see... this patch is about adding tracepoints to the range
optimizer!

 > === 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;
 > +#################

GB Maybe ##### could be changed to
# test of range optimizer tracing
I believe that tracing of modules should be moved to separate
"optimizer_trace*" tests; optimizer_trace_no_prot becomes big.
That applies to pieces which I added to this test too (subquery etc);
in fact, to all pieces which are not pretexts for demonstrating
certain features of @@optimizer_trace* variables. I can take care of
moving my parts if you agree.

 > +
 > +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;

GB so far there was a ban on explicit setting of the engine in a generic
test; for the case where an engine would not be enabled in a certain
binary. Now that InnoDB is default, maybe enabled all the time, I
don't know if this is still a problem.
Note: if a test doesn't have "require_innodb.inc", then the default
engine used is myisam.

 > === 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",

GB Should we print the database's name when we print the table's name
(something which I didn't do myself, but now I start thinking :-)?
Otherwise there is an ambiguity if the query contains db1.C and db2.C.

 > +                "found_records": 1,
 > +                "records": 1,

GB how is "found_records" different from "records"? What is our policy:
have names which an advanced user could understand, or, stick to names
used in code (assuming the reader reads code at the same time)? Maybe
the former is impossible...

 > +                "cost": 1,
 > +                "worst_seeks": 1,

GB nobody can know what "worst_seeks" is about, without consulting
code (and there is no comment near its declaration in
sql_select.h :-( ).

 > +                "exit_find_best_accessplan": true,

GB I don't think this is needed; we have printed the cost/records, and
also print below that this is a constant table, so it's clear that we
don't have more to do in this function.

 > +                "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,

GB Here we don't see the ranges which led the range optimizer to conclude
that it was impossible to find records. Maybe that would be
interesting, if possible (if not, don't bother).

 > +                  "stop_range_analysis": true,
 > +                  "chosen_range_access_summary": {
 > +                    "chosen": false
 > +                  },
 > +                  "records": 0
 > +                },
 > +                "records": 0,
 > +                "cause": "impossible_where_condition"

GB we have "impossible_select", and a few lines below,
"impossible_where_condition": can we avoid having both? If not, can we
choose one stadard name?
It takes all this:
                   "impossible_select": true,
                   "stop_range_analysis": true,
                   "chosen_range_access_summary": {
                     "chosen": false
                   },
                   "records": 0
                 },
                 "records": 0,
                 "cause": "impossible_where_condition"
to say that we have found something impossible; is it possible to
shorten it easily? Maybe not, just asking.

 >                }
 >              ]
 >            }
 > @@ -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,

GB I wonder how a range access can be used when we are doing a "scan"
(which means table scan)? I think range access is only when there is
an index...?

 > +                      "using_join_cache": true,

GB A note unrelated to your patch: this means that best_access_path()
computes costs under the assumption that join cache will be used, but
it may not be, the decision is taken much later.

 > +                      "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

GB this probably means that we abandon any range method and will go
with full index scan or full table scan...?

 > +                  } /* 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,

GB interesting: it's a DELETE, so it needs no columns, but index_only is
false (I would expect that from the index, we can get the row's
position, which is all we need). This is just my curiosity, it's not
about your patch.

 > +              "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"

GB how can a range access with 3 <= d <= 3 (i.e. an index lookup?) be
more expensive (than index scan?)?

 > +                      }
 > +                    ] /* 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

GB This 662 says that 662 bytes are missing because the trace is bigger
than @@optimizer_trace_max_mem_size (it has grown due to range opt
trace); I suggest raising the variable for this query.

 >  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

GB trace has a missing piece here too

 >  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

GB a piece is missing here too

 >  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

GB a piece is missing here too

 > +0		84	0

GB and thus this query could not be traced.

 >  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";

GB I find it's easier for me human to parse the trace if end_marker is
on. Any strong reason to keep it to off?
If I need to pass it to a json parser, I first do a replacement of
/*...*/ by "" (with "sed" for example).

 > +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"

GB Looks like not_in_keys_to_use is related to keys which the WHERE
clause does not 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,

GB Here I don't understand what "index_only" means. The SELECT wants all
columns, and index i1 doesn't contain all columns...?

 > +                              "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

GB ok, so "new_cost" is a cumulated cost. Maybe we should name it
"cumulated_cost"? or a better name?

 > +                        }
 > +                      ],
 > +                      "scan_cost": 11.935,

GB "scan_cost": what type of scan? this seems to be the cost of the range
accesses above.

 > +                      "total_cost": 58.363

GB Maybe we should also print the difference between scan_cost and
total_cost, which seems to be some Unique::get_use_cost?

 > +                    }
 > +                  ],
 > +                  "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))"

GB interesting; as we chose index_merge, I imagine that we're doing index
range accesses, so it should not be needed to have attached conditions
like this (those are conditions which are tested after fetching
records). This is unrelated to your patch.

 > +                }
 > +              ]
 > +            }
 > +          }
 > +        ]
 > +      }
 > +    }
 > +  ]
 > +}
 > +
 > +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": {

GB Here again, understanding "const_key" requires knowing code.

 > +                  "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

GB not chosen because more expensive than table scan?

 > +                  },
 > +                  "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
 > +                      }
 > +                    ]

GB Was group_index_range chosen or not?
I could not find a place where this group_index_range is marked
"chosen".

 > +                  },
 > +                  "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,

GB what's the diff between "total_records" and "records"?

 > +                    "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

GB do you know why we don't also union with i4?

 > +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"
 > +                      ]

GB here we don't have "usable":true. We need to solve the problem of
boolean attributes which are sometimes present sometimes absent: when
absent, what is their implicit value? Or should we explicitely print
"true" for them?
For what it's worth, in Python the None value evaluate to false.

 > +                    },
 > +                    {
 > +                      "index": "i2_2",
 > +                      "key_parts": [
 > +                        "key2",
 > +                        "key2_1"
 > +                      ]
 > +                    }
 > +                  ],
 > +                  "range_scan_possible": false,

GB does the trace above tell why range scan is not possible? I could not find.

 > +                  "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"

GB higher than table scan?

 > +                            }
 > +                          ],
 > +                          "chosen": false,
 > +                          "cause": "expensive"

GB could we use the same cause ("higher_cost"), or do "expensive" and
"higher_cost" carry different meanings?

 > +                        },
 > +                        {
 > +                          "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"

GB "higher_cost", "expensive",
"more_expensive_than_table_scan"... we should standardize.
QA had the idea of parsing the optimizer trace, it's better if we
don't have too many possible words.

 > +                    }
 > +                  ],
 > +                  "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

GB why "_now" ?

 > +                        },
 > +                        {
 > +                          "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,

GB it looks like "cpk_scan" is superfluous info, it is true if and only
if "cpk_added_to_intersect" is true.

 > +                      "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

GB What do you think about my comment under #ifndef? If you find that
there's no big problem, or no choice, you can delete it.

 >    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.

GB ahah, one less mystery! thanks!
GB How about having a "RANGE_OPTIMIZER" flag, used in the constructor of
a few top-level objects/arrays of the range opt code, so that a user
can request hiding of the trace pieces generated by the range
optimizer?

 >      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

GB My position was that as I didn't understand this range optimizer
tracing (done by SergeyP), I disabled it, until we later get back to
it.
Now that we plan to push it in a main tree, we need to understand all
of SergeyP's enabled code. So you will see questions below like
"what's this?".

 >  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;

GB "trace" makes me think of the trace context (like THD::opt_trace which
is Opt_trace_context). How about trace_object?
No space after = .
If trace_basic_info() is only for the optimizer trace, I suggest to
put it in
#ifdef OPTIMIZER_TRACE
otherwise, if optimizer trace is not compiled in, it grows the binary
for no reason.

 > +
 >  };
 >
 > -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)
 > +{

GB key_part could be const.

 > +  const uchar *key_end= key+used_length;

GB space around +

 > +  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++)

GB the coding style asks for fixing some places (space after "+=" above,
too many spaces at the line below...).

 > +  {
 > +    Field *field=      key_part->field;
 > +    store_length= key_part->store_length;
 > +
 > +    if (field->real_maybe_null())
 > +    {
 > +      if (*key)

GB Could you please add your explanation given on IRC in a small comment
here?

 > +      {
 > +        out->append("NULL", 4);

GB or
out->append(STRING_WITH_LEN("NULL"));

 > +        continue;
 > +      }
 > +      key++;                                    // Skip null byte
 > +      store_length--;
 > +    }
 > +    field->set_key_image(key, key_part->length);

GB do you know what set_key_image() does? I imagine it leaves data in
some member of the field; so the content of that member is different
depending on whether trace is enabled or not; we need to be sure that
this difference is innocuous.

 > +    if (field->type() == MYSQL_TYPE_BIT)
 > +      (void) field->val_int_as_str(&tmp, 1);
 > +    else
 > +      field->val_str(&tmp);

GB do we need to have such special case for MYSQL_TYPE_BIT?

 > +    out->append(tmp.ptr(), tmp.length());
 > +    if (key+store_length < key_end)

GB space around +

 > +      out->append("'",1);

GB out->append('\'') should do. And, what does this quote server for? Is
it that for a multi-column key we want to print
part1'part2'part3?
If this has to be, we see that we compute key+store_length above,
compare it to key_end, then we go to the next iteration, where we
compute key+store_length again (in the incrementation in the for(...)
line) and compare it with key_end again (in the for(...)), it's a bit
inefficient.
Also, we don't append a quote after NULL (as we just "continue"), this
sounds strange. Will a multi-column key (NULL,3,4) be printed as
NULL3'4 ? That should be tested.
I suggest to test every branch of this function (the BIT one, the
quote one, etc) in some .test file.

 > +  }
 > +  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).

GB is usage of real_keynr here inspired from existing places of code?
I need to ask, I don't know when real_keynr is good and when not.
About the latest patch: keynr_in_table could be const (maybe the
compiler can do more optimizations with this information).

 > +      add("records", records);

GB should we print also "read_cost" (like we do for ror_intersect)? And
the is_ror member?

 > +
 > +    Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
 > +    KEY_PART_INFO *key_part=

GB could be const

 > +      param->table->key_info[param->real_keynr[key_idx]].key_part;

GB you may want to cache the address of
param->table->key_info[param->real_keynr[key_idx]] to avoid computing
twice this long expression.

 > +    for (SEL_ARG *current= key; current; current= current->next)

GB "current" could be const

 > +    {
 > +      String multikey;
 > +      for (SEL_ARG *part= current; part; part= part->next_key_part)

GB "part" could be const. In all logic, in those functions which only do
tracing, all pointers to the optimizer's structures should be const,
to emphasize that this only updates the trace.
Reading the description of next/next_key_part pointers at start of
opt_range.cc, I interpret that this trace_basic_info() could print
a list, which has ranges as elements; but in
optimizer_trace_no_prot.result, when I grep for
"table_read_plan_type", I always see that a "ranges" list has only one
element; could you please add a test which shows a two-element list?


 > +      {
 > +        int length= (key_part+part->part)->length;

GB space around +

 > +        append_range(&multikey, key_part+part->part,
 > +                     part->min_value, length,
 > +                     part->max_value, length,
 > +                     part->min_flag|part->max_flag);

GB space around |

 > +      }
 > +      trace_range.add(multikey.c_ptr());
 > +    }
 > +  }
 >  };
 >
 > +typedef struct st_ror_scan_info
 > +{
 > +  uint      idx;      ///< # of used key in param->keys

GB thanks for the doxygenized comments!

 > +  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).

GB for bools so far I didn't use "is_"; I think "covering":true/false
would be clear enough; like "chosen" or "usable".

 > +      add("cpk_scan", cpk_scan ? true : false);

GB Coding style asks that we use TRUE/FALSE. I have violated this in the
tree many times, and continue to do so, but I know I'll have to abide
if my proposal to abandon this rule isn't approved by the time I
push...
Or this could be used:
add("cpk_scan", cpk_scan != NULL);

 > +
 > +    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++)

GB ok, print_ror_scans_arr() seems to use the same for().

 > +    {
 > +      KEY key=
param->table->key_info[param->real_keynr[(*cur_scan)->keynr]];

GB print_ror_scans_arr gets a KEY through
   param->table->key_info[(*cur_scan)->keynr]
so I am clueless about whether param->real_keynr is good/bad/useless
here. The description of real_keynr says "Only makes sense if
using_real_indexes==TRUE"...? Is
param->table->key_info[(*cur_scan)->keynr] sometimes unreliable?

 > +      Opt_trace_object trace_isect_idx(param->thd->opt_trace);
 > +      trace_isect_idx.add("table_read_plan_type", "range_scan").

GB ok, looks like inside the for() we print ranges as in the
TRP_RANGE case.

 > +        add("index", key.name).
 > +        add("records", (*cur_scan)->records);
 > +      {

GB I'm not sure we need to open a new scope here. Destructors are called
in the reverse order of constructors, so trace_range will be closed
before trace_isect_idx, as is desirable.

 > +        Opt_trace_array trace_range(param->thd->opt_trace,"ranges");

GB Maybe param->thd->opt_trace should be cached outside of all loops. I'm
not sure that a compiler can figure out that param and thd and
opt_trace don't change accross the loops and thus
param->thd->opt_trace is a constant value.

 > +        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);

GB I suggest to make the code similar to the TRP_RANGE case (or the
contrary); for example, in both do cache key_part (as above), and do
cache "length" (as in TRP_RANGE).

GB Is this 3-loop function covered by tests?

 > +          }
 > +          trace_range.add(range.c_ptr());

GB In the latest tree, there is add(char*,size_t), so a suggestion is to
change all calls like above to:
add(range.ptr(),range.length());
Because the value (range.ptr()) may contain non-ASCII characters, it
should be in UTF8 (JSON parses only UTF8); the add(char*, size_t) will
additionally do escaping of strange characters found in the value
(like \0, \n) so that a JSON parser can parse it.
Let's study how to achieve this.
append_range() uses print_key2() which uses Field::val_str():
   inline String *val_str(String *str) { return val_str(str, str); }
If we look at Field_blob::val_str():
String *Field_blob::val_str(String *val_buffer __attribute__((unused)),
			    String *val_ptr)
{
   ASSERT_COLUMN_MARKED_FOR_READ;
   char *blob;
   memcpy(&blob, ptr+packlength, sizeof(char*));
   if (!blob)
     val_ptr->set("",0,charset());	// A bit safer than ->length(0)
   else
     val_ptr->set((const char*) blob,get_length(ptr),charset());
   return val_ptr;
}
this val_ptr->set() changes the String's charset to be the field's
charset.
So it is possible that in the first iteration of the loops of
print_key2() or of trace_basic_info(), "range"'s charset is set to X,
"range" receives characters in this charset, then in a second
iteration of the loop, "range"'s charset becomes Y and "range"
receives characters in this charset. In the end, we don't know what
charset "range" is in, and it contains a mix of charsets. Possibly not
in UTF8 at all, and we cannot make it UTF8 (it's too late, we have a
mix).
I suggest to change code to be like this:
- in print_key2(), change this line:
     out->append(tmp.ptr(), tmp.length());
to
     out->append(tmp.ptr(), tmp.length(), tmp.charset());
if all goes well this should convert from tmp.charset() to "out"'s
charset before storing in "out"
- we need "out" charset to be declared as UTF8 for the previous point
to work; thus, add after each declaration of "out" a mention of the
charset, for example in
void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose),
change
       String range_info;
       range= *pr;
to
       String range_info;
       range_info.set_charset(system_charset_info);
       range= *pr;

GB Could you also, please, add a test for this:
- a test of escaping, by storing \n in some range bound of your query,
which shows up in the trace via print_key2(), so that we can check
that displays well (escaped) and that it's valid JSON (you can use
WL4800_validate_json.py on the result file to see)
- a test of charset correctness: you can add queries/indices to
optimizer_trace_charset.test (test present in the latest tree).

 > +        }
 > +      }
 > +    }
 > +  }
 >  };
 >
 >
 > @@ -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";}

GB Sometimes in the patch, words are separated by "_", sometimes they are
glued, like index_roworder_union: is there an idea behind the
differences?

 > +  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);

GB space after ',' . I hope you can later do a pass to spot and fix all
little coding style issues.

 > +    }
 > +  }
 >  };
 >
 >
 > @@ -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);

GB space after ,

 > +    }
 > +  }
 >  };
 >
 >
 > @@ -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()).

GB Looks like get_name() is always used in trace_basic_info(), so I
wonder whether we really need get_name(): it could be hard-coded into
trace_basic_info().

 > +      add("index", get_idx_name()).
 > +      add("records", records).
 > +      add("cost", read_cost);

GB This is scarce info compared to other trace_basic_info(), don't we
need to dump something here (range_tree? index_tree? no clue).

 > +  }
 >    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").

GB In some other place of the patch further down, "table scan" is
replaced by "scan". We should decide and be consistent. What do you
prefer?
"scan" is vague; it could well be an "index scan", which is a way of
scanning. But "table scan" is vague too: scanning the index is a way
to scan the table, so a table scan could be an index scan. What "table
scan" really means in MySQL is "scan not using an index", "non-index
scan". The doc talks about "table scan" and defines it as "full table
scan" (see the description of "ALL" in
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html ).
A google search for "oracle table scan" brings up "full 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").

GB So here we have "full_index_scan". For non-index scan, we used
"table_scan", or "scan". We should normalize on one term.

 > +        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);

GB Maybe "stop_range_analysis" is not needed, I imagine that someone
reading "impossible_select" will guess...? Especially as it's then
followed by
                   "chosen_range_access_summary": {
                     "chosen": false
                   },


 >            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(&param, &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(&param, 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(&param, 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(&param, 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
 > +      {
 > +      }

GB can be removed

 > +    }
 > +
 > +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).

GB you mean that in a QUICK_SELECT object, we don't have all info which
trace_basic_info() uses?
Assuming we go with trace_basic_info(), do you think that what it
prints may be misleading compared to what is in QUICK_SELECT?

 > +            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"

GB field1 is better than X, for sure.

 > +                      ]
 > +                    }
 > +                  ],
 > +
 > +            2) will print
 > +                 "ranges": [
 > +                   "3 <= X < 3'10"            <- must print X, not field
name
 > +                 ]
 > +      */
 > +      // 1
 > +      best_trp->trace_basic_info(&param, &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).

GB Isn't this info (records/cost) already printed by the
trace_basic_info() call above?

 > +        add("chosen", true);
 > +    }
 > +    else
 > +    {
 > +      trace_range_summary.add("chosen", false);
 > +      print_quick(quick, &needed_reg);

GB You want to use print_quick() in this branch?

 >      }
 >
 > -#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);

GB param->thd->opt_trace is used many times, could be cached; something
like
Opt_trace_context * const trace= 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");

GB I would move those add() into the if(imerge_too_expensive) block
below; for the case where we don't set imerge_too_expensive in this
iteration but it was set from before (and thus we will "continue" so
should print that "chosen" is false because it's 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));

GB This DBUG_PRINT could be removed, it is preceded by an opt trace call
which prints to DBUG too.

 >    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).

GB Sometimes the patches uses "roworder" sometimes "ror", in
keys/values. We should pick one.

 > +      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");

GB That means, if there is only one, there is nothing to intersect?

 > +    else
 > +      trace_ror.add("usable", false).add("done_tracing", false);

GB What does "done_tracing" mean here? looks like something is missing?

 >      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");

GB what does cost_not_reduced mean?

 > +        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);

GB why "_now"?

 > +
 > +      *(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);

GB Sometimes we have "cost", "cost_now", "new_cost"; we need to
standardize.

 >        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);

GB by "our tests" do you mean the entire mtr testsuite?

 > +
 >    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");

GB Does this mean, cost of range methods is bigger than cost of full
index scan? How could that be? I think a range method scans only a
piece of the index, so how can it be more costly than a full index
scan?

GB Maybe we could standardize between "higher_cost",
"increased_cost", "cheaper"; maybe if this is the value for a "cause"
key, "cost" is clear enough (if "chosen" is false, "cost" means "cost
higher than something else"; if "chosen" is true it means "cost lower
than something else")...?

 > +
 >      }
 >    }
 >
 > @@ -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++)

GB It sounds much better indeed to not increment the first_scan
member. I guess no other code depended on this incrementation?

 >      {
 > -      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

GB comment should have /* alone on its line

 > +       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,

GB can we compress this to less lines?

 > +                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();

GB Do we have a way to pass this array down from the callers, for more
safety? Maybe by adding it as a member of "SEL_ARG_RANGE_SEQ seq" (I'm
looking for a way which doesn't affect the storage engine API).
If impossible, it would be better to not cast, and declare trace_range
of type Opt_trace_struct*. Indeed, with casting, if the struct happens
to be Opt_trace_object, a wrong cast may give a crash including in
release binaries. Without casting, the worse which can happen is a
JSON syntax error, which asserts in debug builds but is not dangerous
in release binaries (see Opt_trace_context::syntax_error()).
I have the goal to make get_current_struct() private soon if
possible. So, nobody could access "the current struct"; it could just
add objects/arrays (which implicitely adds them as children of the
current struct), or pass down an object/array if a function wants to
add to it.

 > +    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);

GB cur_index_info.name should do

 >      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);

GB As we have added "index" earlier, "covering" would be shorter
("_index" is redundant here). By the way, "covering" is used a few
lines below.

 >        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);

GB I'd add a cause mentioning something about "not doing loose index scan
for derived tables" (item_field->field means that this is a derived
table? puzzling...).

 >            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);

GB can bundle both like
trace_idx.add("records", cur_records).add("cost", cur_read_cost);

 >      if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
 >      {
 >        index_info= cur_index_info;
 > @@ -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());

GB this fprintf looks redundant with the DBUG_PRINT just before it.
I suggest to kill one of the two.

 >    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(" : ");

GB for literals like this,
    out->append(" : ", 3);
should avoid strlen(), or alternatively
    out->append(STRING_WITH_LEN(" : "));
Makes me notice that I need to apply the same principal to many calls
in opt_trace.cc (now done).
I wonder: we have lots of calls like
struct.add("somekey","somevalue");
where both parameters are literals (constant strings, fixed). One
example is
         oto1.add("cause", "field types");
in sql_select.cc.
The current add() code will do two calls to
String::append(char*), one to append "somekey", one to append
"somevalue"; String::append(char*) uses strlen() internally.
So we're calling strlen() on what are, in fact, constant
strings. That's not optimal. I should think about a better way without
degrading the API.
Is this addition of ":" covered in tests?

 > -  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);

GB This isn't covered in optimizer_trace_no_prot; do we need to print to
the trace in this dbug_dump()? My experience is that such dbug_dump()
is called from several contexts, including some where a trace would
not be desirable.
It looks like it's only print_quick() which calls dbug_dump()
functions, and print_quick() is called only once, around line 2757, in
an "else" branch, whereas the alternate branch prints to the optimizer
trace; I suggest not making print_quick() print to the opt trace, same
for dbug_dump() functions.

GB Generally, printing to trace in low-level functions gives trouble
as they are called from many places which produces unexpected
traces. I understand that sometimes we don't have a choice when the
info is present only in the low-level function. Or, we do have a
choice, we could let the low-level function copy info to a temporary
object, pass it upwards, and let the higher-level function decide to
build a real trace object out of this temporary data, and print
it. Complicated, maybe.

 >
 > -  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;

GB QUICK_INDEX_MERGE_SELECT has a thd member, you can use it instead of
current_thd.

 > -#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;

GB this class has a thd member already

 > -#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;

GB this class has a thd member already

 > -#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.
 > +    */

GB Do you plan to implement this todo now, or later (before or after it
reaches the main tree)?

 > +    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);

GB what does this if() do? I mean, what is the "index" about?

 > +          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");

GB ok, or
trace_table.add("cause", (s->type == JT_SYSTEM) ?
                          "system_table" : "const_table");

 > +
 >            /* Only one matching row */
 >            s->found_records= s->records= s->read_time=1; s->worst_seeks=
1.0;
 >            continue;
 >          }
 > -        Opt_trace_object 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");

GB or impossible_ON_condition? just an idea to make sure the "on" is
not taken as a real part of the sentence but as a SQL word.

 >                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");

GB 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;

GB given that we assign string literals to it, this should be const char*.

 >    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";

GB is \0 needed?

 >    }
 >    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";

GB same here

 >    }
 >    else if (is_indexed_agg_distinct(join, &indexed_fields))

GB Do you know what this is?

 >    {
 >      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,

GB The only member of thd which this function uses is thd->opt_trace, I
suggest passing that as argument.
There is probably matter for arguing here. Some would say that thd is
just a context which should be passed all around; I rather believe
that a function should be passed only what's needed. I imagine both
have pros and cons.

 > +                                               JOIN_TAB *join_tab,
 > +                                               const key_map new_keys,
 > +                                               char* cause)

GB "cause" -> const char*
join_tab -> const JOIN_TAB*.

 > +{
 > +  // 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;

GB nokeys makes me think of "no keys". I guess it means "number of 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++)

GB j= 0

 > +      if (new_keys.is_set(j) && !existing_keys.is_set(j))
 > +        trace_key.add(key_info[j].name);
 > +  }
 > +  trace_summary.add("cause", cause);

GB Is it normal that "cause" is an attribute of trace_summary? Shouldn't
it be of the upper object instead?

 > @@ -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);

GB There was a line, which this patch deletes, which made sure to output
'"chosen":false' at label skip_table_scan, instead of once per if()
block. Why change this?

 >      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);

GB or move this out of the if()/else, just before if(s->quick), like
this:
trace_access_scan.add("using_range_access", s->quick != NULL);


 >        /* 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);

GB This "cost" (tmp) does not include the cost of WHERE; according to the
code a few lines down, that would be
tmp + record_count * rnd_records / TIME_FOR_COMPARE.
So I wonder about the name "with_where_cond_processing".

 >      /*
 >        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);

GB the logic to decide whether a temp table is needed, is scattered
accross many places; I could not figure it out when fixing BUG#54481;
you can leave this add() above, but I think we will have to deal with
sorting/ordering/temp_tables in a separate WL.

 >      join->sort_by_table= (TABLE*) 1;  // Must use temporary table
 > +  }
 >
 >    DBUG_VOID_RETURN;
 >  }

GB Regarding the multiple crashes due to "json syntax error", I have
on my short-term todo to study this.

-- 
Mr. Guilhem Bichot <guilhem.bichot@stripped>
Oracle / MySQL / Optimizer team, Lead Software Engineer
Bordeaux, France
www.oracle.com / www.mysql.com

Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219) WL#5594Jorgen Loland7 Oct
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Guilhem Bichot16 Oct
    • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Jorgen Loland22 Oct
      • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Guilhem Bichot10 Nov
        • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Jorgen Loland12 Nov
          • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Guilhem Bichot15 Nov
      • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Guilhem Bichot7 Feb
        • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3219)WL#5594Jorgen Loland8 Feb