List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:May 27 2011 8:57am
Subject:Re: bzr commit into mysql-trunk branch (jorgen.loland:3313) WL#4800
View as plain text  
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
>

Thread
bzr commit into mysql-trunk branch (jorgen.loland:3313) WL#4800Jorgen Loland23 May
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3313) WL#4800Tor Didriksen27 May