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


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110523084027-e11h0lajj1lksf46.bundle
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