LGTM
On Mon, May 23, 2011 at 10:40 AM, Jorgen Loland <jorgen.loland@stripped>wrote:
> #At
> file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/
> based on revid:guilhem.bichot@stripped
>
> 3313 Jorgen Loland 2011-05-23
> WL#4800:
>
> * fix todo item "c3) The optimizer may have second
> thoughts about which access method to use for a table" by
> tracing the decision.
> * cost for ref access now includes "compare" cost
>
> Reviewer note: Also take a look at Bug 12580768
>
> modified:
> mysql-test/r/group_min_max.result
> mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc
> mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result
> mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result
>
> mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result
>
> mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result
> sql/sql_select.cc
> === modified file 'mysql-test/r/group_min_max.result'
> --- a/mysql-test/r/group_min_max.result 2011-05-06 12:58:06 +0000
> +++ b/mysql-test/r/group_min_max.result 2011-05-23 08:40:27 +0000
> @@ -2444,7 +2444,7 @@ a b
> 3 13
> explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0
> group by a;
> id select_type table type possible_keys key key_len ref
> rows filtered Extra
> -1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 3
> 100.00 Using where; Using index for group-by; Using temporary
> +1 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15
> 100.00 Using index; Using temporary
> Warnings:
> Note 1003 /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS
> `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where
> (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
> drop table t1;
>
> === modified file
> 'mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc'
> --- a/mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc
> 2011-05-20 13:15:22 +0000
> +++ b/mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc
> 2011-05-23 08:40:27 +0000
> @@ -286,3 +286,16 @@ SELECT * FROM information_schema.OPTIMIZ
>
> --echo
> DROP TABLE t1;
> +
> +# Optimizer first decides to use ref, then changes mind to use range
> instead
> +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b))
> ENGINE=InnoDB;
> +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
> +
> +--echo
> +--echo # Test trace for "access_type_changed 'ref' to 'range'"
> +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
> +--echo
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +
> +--echo
> +drop table t1;
>
> === modified file
> 'mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result'
> --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result
> 2011-05-20 13:15:22 +0000
> +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot.result
> 2011-05-23 08:40:27 +0000
> @@ -1369,7 +1369,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -1401,7 +1401,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -1698,7 +1698,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -4484,7 +4484,7 @@ trace
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -4612,7 +4612,7 @@ trace
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -6369,7 +6369,7 @@ select * from t6 where d in (select f1()
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 3,
> + "cost": 3.2,
> "chosen": true
> },
> {
> @@ -6405,7 +6405,7 @@ select * from t6 where d in (select f1()
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -7008,7 +7008,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 3,
> + "cost": 3.2,
> "chosen": true
> },
> {
> @@ -7044,7 +7044,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -8466,7 +8466,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
>
> === modified file
> 'mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result'
> --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result
> 2011-05-20 13:15:22 +0000
> +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot.result
> 2011-05-23 08:40:27 +0000
> @@ -1353,7 +1353,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -1385,7 +1385,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -1682,7 +1682,7 @@ explain SELECT c FROM t5 where c+1 in (s
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -4468,7 +4468,7 @@ trace
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -4596,7 +4596,7 @@ trace
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -6349,7 +6349,7 @@ select * from t6 where d in (select f1()
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 3,
> + "cost": 3.2,
> "chosen": true
> },
> {
> @@ -6385,7 +6385,7 @@ select * from t6 where d in (select f1()
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -6978,7 +6978,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 3,
> + "cost": 3.2,
> "chosen": true
> },
> {
> @@ -7014,7 +7014,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -8450,7 +8450,7 @@ select d into res from t6 where d in (se
> "access_type": "ref",
> "index": "d",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
>
> === modified file
> 'mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result'
> ---
> a/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result
> 2011-05-20 13:15:22 +0000
> +++
> b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result
> 2011-05-23 08:40:27 +0000
> @@ -1444,14 +1444,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
> "access_type": "ref",
> "index": "i2_1",
> "records": 10,
> - "cost": 10,
> + "cost": 12,
> "chosen": true
> },
> {
> "access_type": "ref",
> "index": "i2_2",
> "records": 10,
> - "cost": 10,
> + "cost": 12,
> "chosen": false
> },
> {
> @@ -2086,7 +2086,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 10,
> - "cost": 10.24,
> + "cost": 12.24,
> "chosen": true
> },
> {
> @@ -2954,7 +2954,7 @@ WHERE t1.key1=t2.key1a AND t1.key2 > 102
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 10,
> - "cost": 430.08,
> + "cost": 432.08,
> "chosen": true
> },
> {
> @@ -3216,14 +3216,14 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
> "access_type": "ref",
> "index": "cola",
> "records": 533,
> - "cost": 533,
> + "cost": 639.6,
> "chosen": true
> },
> {
> "access_type": "ref",
> "index": "colb",
> "records": 533,
> - "cost": 533,
> + "cost": 639.6,
> "chosen": false
> },
> {
> @@ -3406,7 +3406,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
> "access_type": "ref",
> "index": "cola",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -5440,3 +5440,236 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
> } 0 0
>
> DROP TABLE t1;
> +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b))
> ENGINE=InnoDB;
> +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
> +
> +# Test trace for "access_type_changed 'ref' to 'range'"
> +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1
> Using where; Using index for group-by
> +
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE
> INSUFFICIENT_PRIVILEGES
> +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a {
> + "steps": [
> + {
> + "join_preparation": {
> + "select#": 1,
> + "steps": [
> + {
> + "expanded_query": "/* select#1 */ select max(`test`.`t1`.`b`)
> AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b`
> < 2) and (`test`.`t1`.`a` = 1)) group by `test`.`t1`.`a`"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "join_optimization": {
> + "select#": 1,
> + "steps": [
> + {
> + "condition_processing": {
> + "condition": "WHERE",
> + "original_condition": "((`test`.`t1`.`b` < 2) and
> (`test`.`t1`.`a` = 1))",
> + "steps": [
> + {
> + "transformation": "equality_propagation",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + },
> + {
> + "transformation": "constant_propagation",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + },
> + {
> + "transformation": "trivial_condition_removal",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + }
> + ] /* steps */
> + } /* condition_processing */
> + },
> + {
> + "ref_optimizer_key_uses": [
> + {
> + "database": "test",
> + "table": "t1",
> + "field": "a",
> + "equals": "1",
> + "null_rejecting": false
> + }
> + ] /* ref_optimizer_key_uses */
> + },
> + {
> + "records_estimation": [
> + {
> + "database": "test",
> + "table": "t1",
> + "range_analysis": {
> + "table_scan": {
> + "records": 4,
> + "cost": 3.9
> + } /* table_scan */,
> + "potential_range_indices": [
> + {
> + "index": "PRIMARY",
> + "usable": true,
> + "key_parts": [
> + "a",
> + "b"
> + ] /* key_parts */
> + },
> + {
> + "index": "b",
> + "usable": true,
> + "key_parts": [
> + "b"
> + ] /* key_parts */
> + }
> + ] /* potential_range_indices */,
> + "best_covering_index_scan": {
> + "index": "b",
> + "cost": 1.8044,
> + "chosen": true
> + } /* best_covering_index_scan */,
> + "setup_range_conditions": [
> + ] /* setup_range_conditions */,
> + "group_index_range": {
> + "potential_group_range_indices": [
> + {
> + "index": "PRIMARY",
> + "covering": true,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "records": 1,
> + "cost": 1.2
> + },
> + {
> + "index": "b",
> + "usable": false,
> + "cause": "not_covering"
> + }
> + ] /* potential_group_range_indices */
> + } /* group_index_range */,
> + "best_group_range_summary": {
> + "type": "index_group",
> + "index": "PRIMARY",
> + "group_attribute": "b",
> + "min_aggregate": false,
> + "max_aggregate": true,
> + "distinct_aggregate": false,
> + "records": 1,
> + "cost": 1.2,
> + "key_parts_used_for_access": [
> + "a"
> + ] /* key_parts_used_for_access */,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "chosen": true
> + } /* best_group_range_summary */,
> + "analyzing_range_alternatives": {
> + "range_scan_alternatives": [
> + {
> + "index": "PRIMARY",
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "index_only": true,
> + "records": 1,
> + "cost": 1.21,
> + "rowid_ordered": true,
> + "chosen": false,
> + "cause": "cost"
> + },
> + {
> + "index": "b",
> + "ranges": [
> + "b < 2"
> + ] /* ranges */,
> + "index_only": true,
> + "records": 2,
> + "cost": 3.41,
> + "rowid_ordered": false,
> + "chosen": false,
> + "cause": "cost"
> + }
> + ] /* range_scan_alternatives */,
> + "analyzing_roworder_intersect": {
> + "usable": false,
> + "cause": "too_few_roworder_scans"
> + } /* analyzing_roworder_intersect */
> + } /* analyzing_range_alternatives */,
> + "chosen_range_access_summary": {
> + "range_access_plan": {
> + "type": "index_group",
> + "index": "PRIMARY",
> + "group_attribute": "b",
> + "min_aggregate": false,
> + "max_aggregate": true,
> + "distinct_aggregate": false,
> + "records": 1,
> + "cost": 1.2,
> + "key_parts_used_for_access": [
> + "a"
> + ] /* key_parts_used_for_access */,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */
> + } /* range_access_plan */,
> + "records_for_plan": 1,
> + "cost_for_plan": 1.2,
> + "chosen": true
> + } /* chosen_range_access_summary */
> + } /* range_analysis */
> + }
> + ] /* records_estimation */
> + },
> + {
> + "considered_execution_plans": "..."
> + },
> + {
> + "attaching_conditions_to_tables": {
> + "original_condition": "((`test`.`t1`.`a` = 1) and
> (`test`.`t1`.`b` < 2))",
> + "attached_conditions_computation": [
> + {
> + "access_type_changed": {
> + "database": "test",
> + "table": "t1",
> + "index": "PRIMARY",
> + "old_type": "ref",
> + "new_type": "range",
> + "cause": "uses_more_keyparts"
> + } /* access_type_changed */
> + }
> + ] /* attached_conditions_computation */,
> + "attached_conditions_summary": [
> + {
> + "database": "test",
> + "table": "t1",
> + "attached": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b`
> < 2))"
> + }
> + ] /* attached_conditions_summary */
> + } /* attaching_conditions_to_tables */
> + },
> + {
> + "refine_plan": [
> + {
> + "database": "test",
> + "table": "t1",
> + "scan_type": "table"
> + }
> + ] /* refine_plan */
> + }
> + ] /* steps */
> + } /* join_optimization */
> + },
> + {
> + "join_execution": {
> + "select#": 1,
> + "steps": [
> + ] /* steps */
> + } /* join_execution */
> + }
> + ] /* steps */
> +} 0 0
> +
> +drop table t1;
>
> === modified file
> 'mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result'
> ---
> a/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result
> 2011-05-20 13:15:22 +0000
> +++
> b/mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result
> 2011-05-23 08:40:27 +0000
> @@ -1444,14 +1444,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1
> "access_type": "ref",
> "index": "i2_1",
> "records": 10,
> - "cost": 10,
> + "cost": 12,
> "chosen": true
> },
> {
> "access_type": "ref",
> "index": "i2_2",
> "records": 10,
> - "cost": 10,
> + "cost": 12,
> "chosen": false
> },
> {
> @@ -2086,7 +2086,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 10,
> - "cost": 10.24,
> + "cost": 12.24,
> "chosen": true
> },
> {
> @@ -2954,7 +2954,7 @@ WHERE t1.key1=t2.key1a AND t1.key2 > 102
> "access_type": "ref",
> "index": "PRIMARY",
> "records": 10,
> - "cost": 430.08,
> + "cost": 432.08,
> "chosen": true
> },
> {
> @@ -3216,14 +3216,14 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
> "access_type": "ref",
> "index": "cola",
> "records": 533,
> - "cost": 533,
> + "cost": 639.6,
> "chosen": true
> },
> {
> "access_type": "ref",
> "index": "colb",
> "records": 533,
> - "cost": 533,
> + "cost": 639.6,
> "chosen": false
> },
> {
> @@ -3406,7 +3406,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
> "access_type": "ref",
> "index": "cola",
> "records": 1,
> - "cost": 1,
> + "cost": 1.2,
> "chosen": true
> },
> {
> @@ -5432,3 +5432,236 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
> } 0 0
>
> DROP TABLE t1;
> +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b))
> ENGINE=InnoDB;
> +INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
> +
> +# Test trace for "access_type_changed 'ref' to 'range'"
> +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
> +id select_type table type possible_keys key key_len ref
> rows Extra
> +1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1
> Using where; Using index for group-by
> +
> +SELECT * FROM information_schema.OPTIMIZER_TRACE;
> +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE
> INSUFFICIENT_PRIVILEGES
> +EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a {
> + "steps": [
> + {
> + "join_preparation": {
> + "select#": 1,
> + "steps": [
> + {
> + "expanded_query": "/* select#1 */ select max(`test`.`t1`.`b`)
> AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b`
> < 2) and (`test`.`t1`.`a` = 1)) group by `test`.`t1`.`a`"
> + }
> + ] /* steps */
> + } /* join_preparation */
> + },
> + {
> + "join_optimization": {
> + "select#": 1,
> + "steps": [
> + {
> + "condition_processing": {
> + "condition": "WHERE",
> + "original_condition": "((`test`.`t1`.`b` < 2) and
> (`test`.`t1`.`a` = 1))",
> + "steps": [
> + {
> + "transformation": "equality_propagation",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + },
> + {
> + "transformation": "constant_propagation",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + },
> + {
> + "transformation": "trivial_condition_removal",
> + "resulting_condition": "((`test`.`t1`.`b` < 2) and
> multiple equal(1, `test`.`t1`.`a`))"
> + }
> + ] /* steps */
> + } /* condition_processing */
> + },
> + {
> + "ref_optimizer_key_uses": [
> + {
> + "database": "test",
> + "table": "t1",
> + "field": "a",
> + "equals": "1",
> + "null_rejecting": false
> + }
> + ] /* ref_optimizer_key_uses */
> + },
> + {
> + "records_estimation": [
> + {
> + "database": "test",
> + "table": "t1",
> + "range_analysis": {
> + "table_scan": {
> + "records": 4,
> + "cost": 3.9
> + } /* table_scan */,
> + "potential_range_indices": [
> + {
> + "index": "PRIMARY",
> + "usable": true,
> + "key_parts": [
> + "a",
> + "b"
> + ] /* key_parts */
> + },
> + {
> + "index": "b",
> + "usable": true,
> + "key_parts": [
> + "b"
> + ] /* key_parts */
> + }
> + ] /* potential_range_indices */,
> + "best_covering_index_scan": {
> + "index": "b",
> + "cost": 1.8044,
> + "chosen": true
> + } /* best_covering_index_scan */,
> + "setup_range_conditions": [
> + ] /* setup_range_conditions */,
> + "group_index_range": {
> + "potential_group_range_indices": [
> + {
> + "index": "PRIMARY",
> + "covering": true,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "records": 1,
> + "cost": 1.2
> + },
> + {
> + "index": "b",
> + "usable": false,
> + "cause": "not_covering"
> + }
> + ] /* potential_group_range_indices */
> + } /* group_index_range */,
> + "best_group_range_summary": {
> + "type": "index_group",
> + "index": "PRIMARY",
> + "group_attribute": "b",
> + "min_aggregate": false,
> + "max_aggregate": true,
> + "distinct_aggregate": false,
> + "records": 1,
> + "cost": 1.2,
> + "key_parts_used_for_access": [
> + "a"
> + ] /* key_parts_used_for_access */,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "chosen": true
> + } /* best_group_range_summary */,
> + "analyzing_range_alternatives": {
> + "range_scan_alternatives": [
> + {
> + "index": "PRIMARY",
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */,
> + "index_only": true,
> + "records": 1,
> + "cost": 1.21,
> + "rowid_ordered": true,
> + "chosen": false,
> + "cause": "cost"
> + },
> + {
> + "index": "b",
> + "ranges": [
> + "b < 2"
> + ] /* ranges */,
> + "index_only": true,
> + "records": 2,
> + "cost": 3.41,
> + "rowid_ordered": false,
> + "chosen": false,
> + "cause": "cost"
> + }
> + ] /* range_scan_alternatives */,
> + "analyzing_roworder_intersect": {
> + "usable": false,
> + "cause": "too_few_roworder_scans"
> + } /* analyzing_roworder_intersect */
> + } /* analyzing_range_alternatives */,
> + "chosen_range_access_summary": {
> + "range_access_plan": {
> + "type": "index_group",
> + "index": "PRIMARY",
> + "group_attribute": "b",
> + "min_aggregate": false,
> + "max_aggregate": true,
> + "distinct_aggregate": false,
> + "records": 1,
> + "cost": 1.2,
> + "key_parts_used_for_access": [
> + "a"
> + ] /* key_parts_used_for_access */,
> + "ranges": [
> + "1 <= a <= 1 AND b < 2"
> + ] /* ranges */
> + } /* range_access_plan */,
> + "records_for_plan": 1,
> + "cost_for_plan": 1.2,
> + "chosen": true
> + } /* chosen_range_access_summary */
> + } /* range_analysis */
> + }
> + ] /* records_estimation */
> + },
> + {
> + "considered_execution_plans": "..."
> + },
> + {
> + "attaching_conditions_to_tables": {
> + "original_condition": "((`test`.`t1`.`a` = 1) and
> (`test`.`t1`.`b` < 2))",
> + "attached_conditions_computation": [
> + {
> + "access_type_changed": {
> + "database": "test",
> + "table": "t1",
> + "index": "PRIMARY",
> + "old_type": "ref",
> + "new_type": "range",
> + "cause": "uses_more_keyparts"
> + } /* access_type_changed */
> + }
> + ] /* attached_conditions_computation */,
> + "attached_conditions_summary": [
> + {
> + "database": "test",
> + "table": "t1",
> + "attached": "((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b`
> < 2))"
> + }
> + ] /* attached_conditions_summary */
> + } /* attaching_conditions_to_tables */
> + },
> + {
> + "refine_plan": [
> + {
> + "database": "test",
> + "table": "t1",
> + "scan_type": "table"
> + }
> + ] /* refine_plan */
> + }
> + ] /* steps */
> + } /* join_optimization */
> + },
> + {
> + "join_execution": {
> + "select#": 1,
> + "steps": [
> + ] /* steps */
> + } /* join_execution */
> + }
> + ] /* steps */
> +} 0 0
> +
> +drop table t1;
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-05-20 12:38:41 +0000
> +++ b/sql/sql_select.cc 2011-05-23 08:40:27 +0000
> @@ -7636,18 +7636,21 @@ best_access_path(JOIN *join,
> loose_scan_opt.check_ref_access_part2(key, start_key, records,
> tmp);
>
> } /* not ft_key */
> - trace_access_idx.add("records", records).add("cost", tmp);
> - if (tmp < best_time - records * ROW_EVALUATE_COST ||
> - (quick_matches_more_parts &&
> - quick_records < best_quick_records))
> - {
> - best_quick_records = quick_records;
> - best_time= tmp + records * ROW_EVALUATE_COST;
> - best= tmp;
> - best_records= records;
> - best_key= start_key;
> - best_max_key_part= max_key_part;
> - best_ref_depends_map= found_ref;
> +
> + {
> + double idx_time= tmp + records * ROW_EVALUATE_COST;
> + trace_access_idx.add("records", records).add("cost", idx_time);
> + if (idx_time < best_time ||
> + (quick_matches_more_parts && quick_records <
> best_quick_records))
> + {
> + best_quick_records = quick_records;
> + best_time= idx_time;
> + best= tmp;
> + best_records= records;
> + best_key= start_key;
> + best_max_key_part= max_key_part;
> + best_ref_depends_map= found_ref;
> + }
> }
> done_with_index:
> trace_access_idx.add("chosen", best_key == start_key);
> @@ -10096,7 +10099,20 @@ static bool make_join_select(JOIN *join,
> (uint) tab->ref.key == tab->quick->index &&
> tab->ref.key_length < tab->quick->max_used_key_length)
> {
> - /* Range uses longer key; Use this instead of ref on key */
> + /*
> + Range uses longer key; Use this instead of ref on key
> +
> + Todo: This decision should rather be made in
> + best_access_path()
> + */
> + Opt_trace_object wrapper(trace);
> + Opt_trace_object (trace, "access_type_changed").
> + add_utf8_table(tab->table).
> + add_utf8("index", tab->table->key_info[tab->ref.key].name).
> + add_alnum("old_type", "ref").
> + add_alnum("new_type", "range").
> + add_alnum("cause", "uses_more_keyparts");
> +
> tab->type=JT_ALL;
> use_quick_range=1;
> tab->use_quick=QS_RANGE;
>
>
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:
> http://lists.mysql.com/commits?unsub=1
>
