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 Aske | 8 Feb |
