List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:February 8 2012 9:26am
Subject:bzr push into mysql-trunk branch (ole.john.aske:3858 to 3859)
View as plain text  
 3859 Ole John Aske	2012-02-08
      Created branch mysql-trunk-wl5558_2 based on current mysql-trunk, revno: 3858, with the addition of WL5558
      patches.

    modified:
      mysql-test/r/group_by.result
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/select_found.result
      mysql-test/r/subquery_mat_none.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
      mysql-test/suite/opt_trace/r/range_no_prot.result
      sql/sql_executor.cc
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
      sql/sql_select.cc
 3858 Rohit Kalhans	2012-02-08 [merge]
      null merge from mysql-5.5 -> mysql-trunk

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2012-02-06 12:47:30 +0000
+++ b/mysql-test/r/group_by.result	2012-02-08 09:25:30 +0000
@@ -1340,10 +1340,10 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	144	
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 a
 1
@@ -2166,7 +2166,7 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5
 EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
 FROM t1 GROUP BY field1, field2;;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx	5	NULL	6	Using index for group-by; Using filesort
+1	SIMPLE	t1	range	NULL	idx	5	NULL	6	Using index for group-by
 SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
 FROM t1 GROUP BY field1, field2;;
 field1	field2

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-02-08 09:25:30 +0000
@@ -2233,7 +2233,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-02-08 09:25:30 +0000
@@ -2233,7 +2233,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-02-08 09:25:30 +0000
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-01-30 13:13:15 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-02-08 09:25:30 +0000
@@ -2216,7 +2216,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1,t2
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-02-01 09:59:13 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-02-08 09:25:30 +0000
@@ -2216,7 +2216,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1,t2
@@ -2234,7 +2234,7 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)

=== modified file 'mysql-test/r/select_found.result'
--- a/mysql-test/r/select_found.result	2011-06-27 07:17:26 +0000
+++ b/mysql-test/r/select_found.result	2012-02-08 09:25:30 +0000
@@ -84,19 +84,19 @@ UNIQUE KEY e_n (email,name)
 EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	kid	NULL	NULL	NULL	0	const row not found
-1	SIMPLE	t2	index	NULL	e_n	104	NULL	10	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	200	Using temporary
 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 email
 email1
+email2
+email3
+email4
+email5
+email6
+email7
+email8
+email9
 email10
-email100
-email101
-email102
-email103
-email104
-email105
-email106
-email107
 SELECT FOUND_ROWS();
 FOUND_ROWS()
 200

=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result	2012-01-31 11:19:25 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2012-02-08 09:25:30 +0000
@@ -137,7 +137,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	100.00	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	60.00	Using index
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or isnull(`test`.`t2i`.`b1`)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or isnull(max(`test`.`t2i`.`b2`))) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`)))))
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
@@ -148,11 +148,11 @@ prepare st1 from "explain select * from 
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	Using index
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2i	index	NULL	it2i3	18	NULL	5	Using index
 prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
 execute st2;
 a1	a2
@@ -764,7 +764,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
-2	DEPENDENT SUBQUERY	t1	index	NULL	a	8	NULL	1	Using filesort
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);
@@ -1277,7 +1277,7 @@ HAVING COUNT(*) > 0
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	table2	system	PRIMARY	NULL	NULL	NULL	1	
-2	DEPENDENT SUBQUERY	innr	index	NULL	col_int_key	5	NULL	2	Using where; Using filesort
+2	DEPENDENT SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
 SELECT table1.pk, table2.pk
 FROM t2 AS table1 LEFT JOIN t2 AS table2
 ON table2.pk = table1.pk AND

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-01-30 13:13:15 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-02-08 09:25:30 +0000
@@ -2280,14 +2280,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                             "table": "t1"
                           }
                         ] /* refine_plan */
-                      }
-                    ] /* steps */
-                  } /* join_optimization */
-                },
-                {
-                  "join_execution": {
-                    "select#": 2,
-                    "steps": [
+                      },
                       {
                         "reconsidering_access_paths_for_index_ordering": {
                           "index_order_summary": {
@@ -2301,6 +2294,13 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                         } /* reconsidering_access_paths_for_index_ordering */
                       }
                     ] /* steps */
+                  } /* join_optimization */
+                },
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
                   } /* join_execution */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result	2012-01-26 13:09:59 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result	2012-02-08 09:25:30 +0000
@@ -1781,14 +1781,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                             "table": "t1"
                           }
                         ] /* refine_plan */
-                      }
-                    ] /* steps */
-                  } /* join_optimization */
-                },
-                {
-                  "join_execution": {
-                    "select#": 2,
-                    "steps": [
+                      },
                       {
                         "reconsidering_access_paths_for_index_ordering": {
                           "index_order_summary": {
@@ -1802,6 +1795,13 @@ SELECT 1 FROM t1 WHERE 1 LIKE
                         } /* reconsidering_access_paths_for_index_ordering */
                       }
                     ] /* steps */
+                  } /* join_optimization */
+                },
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
                   } /* join_execution */
                 }
               ] /* steps */

=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result	2012-01-26 13:09:59 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result	2012-02-08 09:25:30 +0000
@@ -878,10 +878,6 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -1417,10 +1413,6 @@ GROUP BY key2	{
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -4643,14 +4635,7 @@ EXPLAIN SELECT * FROM t1 WHERE i1 > '2' 
                 "access_type": "range"
               }
             ] /* refine_plan */
-          }
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_explain": {
-        "select#": 1,
-        "steps": [
+          },
           {
             "reconsidering_access_paths_for_index_ordering": {
               "rows_estimation": {
@@ -4730,6 +4715,13 @@ EXPLAIN SELECT * FROM t1 WHERE i1 > '2' 
             } /* reconsidering_access_paths_for_index_ordering */
           }
         ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_explain": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
       } /* join_explain */
     }
   ] /* steps */
@@ -5075,18 +5067,6 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
       "join_explain": {
         "select#": 1,
         "steps": [
-          {
-            "reconsidering_access_paths_for_index_ordering": {
-              "index_order_summary": {
-                "database": "test",
-                "table": "t1",
-                "index": "k1",
-                "order_direction": "desc",
-                "plan_changed": true,
-                "access_type": "index_scan"
-              } /* index_order_summary */
-            } /* reconsidering_access_paths_for_index_ordering */
-          }
         ] /* steps */
       } /* join_explain */
     }
@@ -6235,14 +6215,7 @@ EXPLAIN SELECT * FROM t1 WHERE c1 = '1' 
                 "table_condition_attached": null
               }
             ] /* refine_plan */
-          }
-        ] /* steps */
-      } /* join_optimization */
-    },
-    {
-      "join_explain": {
-        "select#": 1,
-        "steps": [
+          },
           {
             "reconsidering_access_paths_for_index_ordering": {
               "index_order_summary": {
@@ -6257,6 +6230,13 @@ EXPLAIN SELECT * FROM t1 WHERE c1 = '1' 
             } /* reconsidering_access_paths_for_index_ordering */
           }
         ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_explain": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
       } /* join_explain */
     }
   ] /* steps */

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2012-02-07 14:50:31 +0000
+++ b/sql/sql_executor.cc	2012-02-08 09:25:30 +0000
@@ -117,11 +117,6 @@ static bool setup_copy_fields(THD *thd, 
   Execute select, executor entry point.
 
   @todo
-    Note, that create_sort_index calls test_if_skip_sort_order and may
-    finally replace sorting with index scan if there is a LIMIT clause in
-    the query.  It's never shown in EXPLAIN!
-
-  @todo
     When can we have here thd->net.report_error not zero?
 */
 
@@ -606,6 +601,9 @@ JOIN::execute(JOIN *parent)
           Note: here we call make_cond_for_table() a second time in order
           to get sort_table_cond. An alternative could be to use
           Item::copy_andor_structure() to make a copy of sort_table_cond.
+
+          TODO: This is now obsolete as test_if_skip_sort_order()
+                is not any longer called as part of JOIN::execute() !
         */
         if (curr_table->pre_idx_push_cond)
         {
@@ -664,9 +662,6 @@ JOIN::execute(JOIN *parent)
 	Here we sort rows for ORDER BY/GROUP BY clause, if the optimiser
 	chose FILESORT to be faster than INDEX SCAN or there is no 
 	suitable index present.
-	Note, that create_sort_index calls test_if_skip_sort_order and may
-	finally replace sorting with index scan if there is a LIMIT clause in
-	the query. XXX: it's never shown in EXPLAIN!
 	OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.
       */
       DBUG_PRINT("info",("Sorting for order by/group by"));
@@ -936,11 +931,9 @@ JOIN::optimize_distinct()
   /* Optimize "select distinct b from t1 order by key_part_1 limit #" */
   if (order && skip_sort_order)
   {
-    /* Should always succeed */
-    if (test_if_skip_sort_order(&join_tab[const_tables],
-                                order, unit->select_limit_cnt, false, 
-                                &join_tab[const_tables].table->
-                                keys_in_use_for_order_by))
+    /* Should already have been optimized away */
+    DBUG_ASSERT(ordered_index_usage == ordered_index_order_by);
+    if (ordered_index_usage == ordered_index_order_by)
       order= NULL;
   }
 }
@@ -3861,19 +3854,25 @@ create_sort_index(THD *thd, JOIN *join, 
   select= tab->select;
 
   /*
-    When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
-    and thus force sorting on disk unless a group min-max optimization
-    is going to be used as it is applied now only for one table queries
-    with covering indexes.
+    JOIN::optimize may have prepared an access patch which makes
+    either the GROUP BY or ORDER BY sorting obsolete by using an
+    ordered index for the access. If the requested 'order' match
+    the prepared 'ordered_index_usage', we don't have to build 
+    a temporary sort index now.
   */
-  if ((order != join->group_list || 
-       !(join->select_options & SELECT_BIG_RESULT) ||
-       (select && select->quick &&
-        select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) &&
-      test_if_skip_sort_order(tab,order,select_limit,0, 
-                              is_order_by ?  &table->keys_in_use_for_order_by :
-                              &table->keys_in_use_for_group_by))
-    DBUG_RETURN(0);
+  {
+    DBUG_ASSERT((is_order_by) == (order == join->order));  // Obsolete arg !
+    const bool is_skippable= (is_order_by) ?
+      ( join->simple_order &&
+        join->ordered_index_usage == JOIN::ordered_index_order_by )
+      :
+      ( join->simple_group &&
+        join->ordered_index_usage == JOIN::ordered_index_group_by );
+
+    if (is_skippable)
+      DBUG_RETURN(0);
+  }
+
   for (ORDER *ord= join->order; ord; ord= ord->next)
     length++;
   if (!(join->sortorder= 

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-02-07 20:32:47 +0000
+++ b/sql/sql_optimizer.cc	2012-02-08 09:25:30 +0000
@@ -591,15 +591,21 @@ JOIN::optimize()
     JOIN_TAB *tab= &join_tab[const_tables];
     bool all_order_fields_used;
     if (order)
-      skip_sort_order= test_if_skip_sort_order(tab, order, m_select_limit, 1, 
-        &tab->table->keys_in_use_for_order_by);
+    {
+      skip_sort_order=
+        test_if_skip_sort_order(tab, order, m_select_limit,
+                                true,           // no_changes
+                                &tab->table->keys_in_use_for_order_by);
+    }
     if ((group_list=create_distinct_group(thd, ref_ptrs,
                                           order, fields_list, all_fields,
 				          &all_order_fields_used)))
     {
-      bool skip_group= (skip_sort_order &&
-        test_if_skip_sort_order(tab, group_list, m_select_limit, 1, 
-                                &tab->table->keys_in_use_for_group_by) != 0);
+      const bool skip_group=
+        skip_sort_order &&
+        test_if_skip_sort_order(tab, group_list, m_select_limit,
+                                  true,         // no_changes
+                                  &tab->table->keys_in_use_for_group_by);
       count_field_types(select_lex, &tmp_table_param, all_fields, 0);
       if ((skip_group && all_order_fields_used) ||
 	  m_select_limit == HA_POS_ERROR ||
@@ -812,6 +818,26 @@ JOIN::optimize()
 
   if (const_tables != tables)
   {
+    JOIN_TAB *tab= &join_tab[const_tables];
+
+    if (order)
+    {
+      /*
+        Force using of tmp table if sorting by a SP or UDF function due to
+        their expensive and probably non-deterministic nature.
+      */
+      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      {
+        Item *item= *tmp_order->item;
+        if (item->is_expensive())
+        {
+          /* Force tmp table without sort */
+          need_tmp=1; simple_order=simple_group=0;
+          break;
+        }
+      }
+    }
+
     /*
       Because filesort always does a full table scan or a quick range scan
       we must add the removed reference to the select for the table.
@@ -819,54 +845,80 @@ JOIN::optimize()
       as in other cases the join is done before the sort.
     */
     if ((order || group_list) &&
-        join_tab[const_tables].type != JT_ALL &&
-        join_tab[const_tables].type != JT_FT &&
-        join_tab[const_tables].type != JT_REF_OR_NULL &&
+        tab->type != JT_ALL &&
+        tab->type != JT_FT &&
+        tab->type != JT_REF_OR_NULL &&
         ((order && simple_order) || (group_list && simple_group)))
     {
-      if (add_ref_to_table_cond(thd,&join_tab[const_tables])) {
+      if (add_ref_to_table_cond(thd,tab)) {
         DBUG_RETURN(1);
       }
     }
     
-    if (!(select_options & SELECT_BIG_RESULT) &&
-        ((group_list &&
-          (!simple_group ||
-           !test_if_skip_sort_order(&join_tab[const_tables], group_list,
-                                    unit->select_limit_cnt, 0, 
-                                    &join_tab[const_tables].table->
-                                    keys_in_use_for_group_by))) ||
-         select_distinct) &&
-        tmp_table_param.quick_group && !procedure)
-    {
-      need_tmp=1; simple_order=simple_group=0;	// Force tmp table without sort
-    }
-    if (order)
+    /*
+      Investigate whether we may use an ordered index as part of either
+      DISTINCT, GROUP BY or ORDER BY execution. An ordered index may be
+      used for only the first of any of these terms to be executed. This
+      is reflected in the order which we check for test_if_skip_sort_order()
+      below. However we do not check for DISTINCT here, as it would have
+      been transformed to a GROUP BY at this stage if it is a candidate for 
+      ordered index optimization.
+      If a decision was made to use an ordered index, the availability
+      if such an access path is stored in 'ordered_index_usage' for later
+      use by 'execute' or 'explain'
+    */
+    DBUG_ASSERT(ordered_index_usage == ordered_index_void);
+
+    if (group_list)   // GROUP BY honoured first
+                      // (DISTINCT was rewritten to GROUP BY if skippable)
     {
       /*
-        Do we need a temporary table due to the ORDER BY not being equal to
-        the GROUP BY? The call to test_if_skip_sort_order above tests for the
-        GROUP BY clause only and hence is not valid in this case. So the
-        estimated number of rows to be read from the first table is not valid.
-        We clear it here so that it doesn't show up in EXPLAIN.
-       */
-      if (need_tmp && (select_options & SELECT_DESCRIBE) != 0)
-        join_tab[const_tables].limit= 0;
-      /*
-        Force using of tmp table if sorting by a SP or UDF function due to
-        their expensive and probably non-deterministic nature.
+        When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
+        and thus force sorting on disk unless a group min-max optimization
+        is going to be used as it is applied now only for one table queries
+        with covering indexes.
       */
-      for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next)
+      if (!(select_options & SELECT_BIG_RESULT) ||
+            (tab->select &&
+             tab->select->quick &&
+             tab->select->quick->get_type() ==
+             QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX))
       {
-        Item *item= *tmp_order->item;
-        if (item->is_expensive())
+        if (simple_group &&              // GROUP BY is possibly skippable
+            !select_distinct)            // .. if not preceded by a DISTINCT
         {
-          /* Force tmp table without sort */
-          need_tmp=1; simple_order=simple_group=0;
-          break;
+          /*
+            Calculate a possible 'limit' of table rows for 'GROUP BY':
+            A specified 'LIMIT' is relative to the final resultset.
+            'need_tmp' implies that there will be more postprocessing 
+            so the specified 'limit' should not be enforced yet.
+           */
+          const ha_rows limit = need_tmp ? HA_POS_ERROR : m_select_limit;
+
+          if (test_if_skip_sort_order(tab, group_list, limit, false, 
+                                      &tab->table->keys_in_use_for_group_by))
+          {
+            ordered_index_usage= ordered_index_group_by;
+          }
+        }
+
+        if ((ordered_index_usage != ordered_index_group_by) &&
+            tmp_table_param.quick_group && !procedure)
+        {
+          need_tmp=1;
+          simple_order= simple_group= false; // Force tmp table without sort
         }
       }
     }
+    else if (order &&                      // ORDER BY wo/ preceeding GROUP BY
+             (simple_order || skip_sort_order)) // which is possibly skippable
+    {
+      if (test_if_skip_sort_order(tab, order, m_select_limit, false, 
+                                  &tab->table->keys_in_use_for_order_by))
+      {
+        ordered_index_usage= ordered_index_order_by;
+      }
+    }
   }
 
   tmp_having= having;

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2012-02-07 20:32:47 +0000
+++ b/sql/sql_optimizer.h	2012-02-08 09:25:30 +0000
@@ -181,6 +181,19 @@ public:
     GROUP/ORDER BY.
   */
   bool simple_order, simple_group;
+
+  /*
+    ordered_index_usage is set if an ordered index access
+    should be used instead of a filesort when computing 
+    ORDER/GROUP BY.
+  */
+  enum
+  {
+    ordered_index_void,       // No ordered index avail.
+    ordered_index_group_by,   // Use index for GROUP BY
+    ordered_index_order_by,   // Use index for ORDER BY
+  } ordered_index_usage;
+
   /**
     Is set only in case if we have a GROUP BY clause
     and no ORDER BY after constant elimination of 'order'.
@@ -317,6 +330,7 @@ public:
     no_order= 0;
     simple_order= 0;
     simple_group= 0;
+    ordered_index_usage= ordered_index_void;
     skip_sort_order= 0;
     need_tmp= 0;
     hidden_group_fields= 0; /*safety*/

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-02-01 09:59:13 +0000
+++ b/sql/sql_select.cc	2012-02-08 09:25:30 +0000
@@ -861,6 +861,7 @@ JOIN::explain()
     table to resolve ORDER BY: in that case, we only may need to do
     filesort for GROUP BY.
   */
+  bool is_order_by= true;
   if (!order && !no_order && (!skip_sort_order || !need_tmp))
   {
     /*
@@ -870,21 +871,32 @@ JOIN::explain()
     order= group_list;
     simple_order= simple_group;
     skip_sort_order= 0;
+    is_order_by= false;
   }
-  if (order && 
-      (order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
-      (const_tables == tables ||
-       ((simple_order || skip_sort_order) &&
-        test_if_skip_sort_order(&join_tab[const_tables], order,
-                                m_select_limit, 0, 
-                                &join_tab[const_tables].table->
-                                keys_in_use_for_query))))
-    order=0;
+
   having= tmp_having;
   if (tables)
+  {
+    /*
+      JOIN::optimize may have prepared an access patch which makes
+      either the GROUP BY or ORDER BY sorting obsolete by using an
+      ordered index for the access. If the required 'order' match
+      the available 'ordered_index_usage' we will use ordered index
+      access instead of doing a filesort.
+
+      NOTE: This code is intentional similar to 'is_skippable' code
+            in create_sort_index() which is the ::execute()
+            counterpart of what we 'explain' here.
+    */
+    const bool is_skippable= (is_order_by) ?
+      ( simple_order && ordered_index_usage == ordered_index_order_by )
+      :
+      ( simple_group && ordered_index_usage == ordered_index_group_by );
+
     explain_query_specification(thd, this, need_tmp,
-                                order != 0 && !skip_sort_order,
+                                (order != NULL) && !is_skippable,
                                 select_distinct);
+  }
   else
     explain_no_table(thd, this, "No tables used");
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (ole.john.aske:3858 to 3859) Ole John Aske8 Feb