3705 Ole John Aske 2012-01-05
Establish new baseline branch for WL#5940 review: mysqltrunk-wl5949_2:
- There has been lots of refactoring in the optimizer since the original WL5940
branch was created. We need to refactor the WL5940 patches to due to this.
- One of the inital comments from Evgeny suggested that WL5940 would be cleaner
if the Abstract Query Plan didn't have to call test_if_skip_sort_order(). Which
had the sideffect that the query plan could be changed. This required us
to implement WL5558 first, and then base a simpler wl5940 on this.
This commit add proposed implementation for WL5558 to mysql-trunk and
the bugfixes it depends on - Look in wl5558 for a description of these.
modified:
mysql-test/include/icp_tests.inc
mysql-test/include/order_by.inc
mysql-test/r/group_by.result
mysql-test/r/group_min_max.result
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/innodb_mrr.result
mysql-test/r/innodb_mrr_cost.result
mysql-test/r/innodb_mrr_cost_icp.result
mysql-test/r/innodb_mrr_icp.result
mysql-test/r/innodb_mrr_none.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/myisam_icp.result
mysql-test/r/myisam_icp_none.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/r/select_found.result
mysql-test/r/subquery_mat_none.result
mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
mysql-test/suite/opt_trace/r/range_no_prot.result
sql/opt_range.cc
sql/records.cc
sql/sql_executor.cc
sql/sql_optimizer.cc
sql/sql_optimizer.h
sql/sql_select.cc
3704 Anitha Gopi 2012-01-05
Bug#12682554 - FAILED ASSERT AT HANDLER0ALTER.CC IN MANY TESTS
Removed tests from experimental group
modified:
mysql-test/collections/default.experimental
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2011-10-05 13:16:38 +0000
+++ b/mysql-test/include/icp_tests.inc 2012-01-05 13:13:15 +0000
@@ -1043,7 +1043,7 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
let $query=
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
=== modified file 'mysql-test/include/order_by.inc'
--- a/mysql-test/include/order_by.inc 2011-12-19 09:22:28 +0000
+++ b/mysql-test/include/order_by.inc 2012-01-05 13:13:15 +0000
@@ -1750,3 +1750,63 @@ INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1,2), (2,3);
SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1 ORDER BY a DESC) FROM t2;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #13528826
+--echo # TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+--echo #
+--echo #
+
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+ (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+ (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+ (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+
+--echo # number of rows in t1 was incorrectly used as an
+--echo # implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+
+--echo # Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+
+-- echo # A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #13531865
+--echo # TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+--echo # 'TYPE' IS REF_OR_NULL
+--echo #
+--echo #
+
+CREATE TABLE t1 (
+ a INT,
+ c INT,
+ UNIQUE KEY a_c (a,c),
+ KEY (a)) engine=myisam;
+
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+
+--echo # Using 'KEY a_c' for order-by opt, would have required
+--echo # REF_OR_NULL access which never can be order_by skipped.
+--echo # -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+
+DROP TABLE t1;
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_by.result 2012-01-05 13:13:15 +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
@@ -2165,7 +2165,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/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/group_min_max.result 2012-01-05 13:13:15 +0000
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 15 Using index
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_icp.result 2012-01-05 13:13:15 +0000
@@ -988,14 +988,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where
+1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Using index
1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2012-01-05 13:13:15 +0000
@@ -987,14 +987,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where
+1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Using index
1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result 2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr.result 2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
AND t2.pk IS NULL
ORDER BY i1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where
SELECT i1
FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result 2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result 2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
AND t2.pk IS NULL
ORDER BY i1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where
SELECT i1
FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result 2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result 2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
AND t2.pk IS NULL
ORDER BY i1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where
SELECT i1
FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result 2011-12-01 14:12:10 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result 2012-01-05 13:13:15 +0000
@@ -539,7 +539,7 @@ WHERE t2.i1 > 5
AND t2.pk IS NULL
ORDER BY i1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where
SELECT i1
FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result 2011-11-23 19:55:56 +0000
+++ b/mysql-test/r/innodb_mrr_none.result 2012-01-05 13:13:15 +0000
@@ -538,7 +538,7 @@ WHERE t2.i1 > 5
AND t2.pk IS NULL
ORDER BY i1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 system PRIMARY NULL NULL NULL 1
1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where
SELECT i1
FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bka.result 2012-01-05 13:13:15 +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 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result 2012-01-05 13:13:15 +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 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result 2012-01-05 13:13:15 +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 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_bnl.result 2012-01-05 13:13:15 +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 2011-12-15 12:12:14 +0000
+++ b/mysql-test/r/join_cache_nojb.result 2012-01-05 13:13:15 +0000
@@ -1445,7 +1445,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -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/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp.result 2012-01-05 13:13:15 +0000
@@ -983,14 +983,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using temporary; Using filesort
-1 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Using where
+1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2012-01-05 13:13:15 +0000
@@ -982,14 +982,14 @@ INSERT INTO t2 VALUES (0,9),(3,10),(4,6)
set @old_opt_switch=@@optimizer_switch;
SET optimizer_switch="block_nested_loop=off";
EXPLAIN SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using temporary; Using filesort
-1 SIMPLE t2 ALL PRIMARY,col_int_key NULL NULL NULL 6 Using where
+1 SIMPLE t2 ALL col_int_key NULL NULL NULL 6 Using where; Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
SELECT t2.col_int_key AS field1
-FROM t2 JOIN t1 ON t2.col_int_key
+FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
WHERE t2.pk < 7 AND t2.col_int_key <> 7
GROUP BY field1;
field1
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2012-01-05 13:13:15 +0000
@@ -1461,7 +1461,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
col
@@ -1470,7 +1470,7 @@ col
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
id select_type table type possible_keys key key_len ref rows Extra
-x x x ref_or_null a_c,a x x x x x
+x x x ref a_c,a x x x x x
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
col
@@ -2600,4 +2600,75 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
NULL
NULL
DROP TABLE t1, t2;
+#
+# Bug #13528826
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+#
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL
+#
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+# Using 'KEY a_c' for order-by opt, would have required
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
set optimizer_switch=default;
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2011-12-19 09:22:28 +0000
+++ b/mysql-test/r/order_by_none.result 2012-01-05 13:13:15 +0000
@@ -1460,7 +1460,7 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL)
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
col
@@ -1469,7 +1469,7 @@ col
EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
id select_type table type possible_keys key key_len ref rows Extra
-x x x ref_or_null a_c,a x x x x x
+x x x ref a_c,a x x x x x
# Must return 1 row
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
col
@@ -2599,4 +2599,75 @@ SELECT (SELECT 1 FROM t1 WHERE a=b AND c
NULL
NULL
DROP TABLE t1, t2;
+#
+# Bug #13528826
+# TEST_IF_CHEAPER_ORDERING(): CALCULATES INCORRECT 'SELECT_LIMIT'
+#
+#
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+# number of rows in t1 was incorrectly used as an
+# implicit limit-clause if not explicit specified
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# Query above used to be explained identical to this:
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+# A really high limit was required to give the correct explain
+EXPLAIN
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index
+DROP TABLE t1, t2;
+#
+# Bug #13531865
+# TEST_IF_SKIP_SORT_ORDER() INCORRECTLY SKIP FILESORT IF
+# 'TYPE' IS REF_OR_NULL
+#
+#
+CREATE TABLE t1 (
+a INT,
+c INT,
+UNIQUE KEY a_c (a,c),
+KEY (a)) engine=myisam;
+INSERT INTO t1 VALUES (1,10), (2,NULL), (2,10);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+# Using 'KEY a_c' for order-by opt, would have required
+# REF_OR_NULL access which never can be order_by skipped.
+# -> Keep initial REF on 'KEY a' selected by cond. optimizer
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+c
+NULL
+10
+EXPLAIN
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a_c,a a 5 const 1 Using where; Using filesort
+SELECT c FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+c
+10
+NULL
+DROP TABLE t1;
set optimizer_switch=default;
=== 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-01-05 13:13:15 +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 2011-11-25 10:39:07 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2012-01-05 13:13:15 +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
@@ -762,7 +762,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);
=== 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 2011-11-29 08:41:50 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result 2012-01-05 13:13:15 +0000
@@ -1779,14 +1779,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": {
@@ -1800,6 +1793,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 2011-12-01 14:12:10 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result 2012-01-05 13:13:15 +0000
@@ -875,10 +875,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 */
}
@@ -1410,10 +1406,6 @@ GROUP BY key2 {
"join_explain": {
"select#": 1,
"steps": [
- {
- "reconsidering_access_paths_for_index_ordering": {
- } /* reconsidering_access_paths_for_index_ordering */
- }
] /* steps */
} /* join_explain */
}
@@ -4618,14 +4610,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": {
@@ -4704,6 +4689,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 */
@@ -5044,18 +5036,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 */
}
@@ -6193,14 +6173,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": {
@@ -6215,6 +6188,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/opt_range.cc'
--- a/sql/opt_range.cc 2011-12-15 16:15:27 +0000
+++ b/sql/opt_range.cc 2012-01-05 13:13:15 +0000
@@ -1342,7 +1342,6 @@ QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT(
if (file)
{
range_end();
- head->set_keyread(FALSE);
if (free_file)
{
DBUG_PRINT("info", ("Freeing separate handler %p (free: %d)", file,
=== modified file 'sql/records.cc'
--- a/sql/records.cc 2011-12-14 12:32:55 +0000
+++ b/sql/records.cc 2012-01-05 13:13:15 +0000
@@ -325,6 +325,10 @@ void end_read_record(READ_RECORD *info)
my_free_lock(info->cache);
info->cache=0;
}
+ if (info->table && info->table->key_read)
+ {
+ info->table->set_keyread(FALSE);
+ }
if (info->table && info->table->created)
{
filesort_free_buffers(info->table,0);
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2011-12-19 09:53:30 +0000
+++ b/sql/sql_executor.cc 2012-01-05 13:13:15 +0000
@@ -118,11 +118,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?
*/
@@ -612,6 +607,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)
{
@@ -670,9 +668,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"));
@@ -944,11 +939,9 @@ JOIN::optimize_distinct()
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))
- order= NULL;
+ DBUG_ASSERT(ordered_index_usage==ordered_index_order_by);
+ if (ordered_index_usage==ordered_index_order_by)
+ order=NULL;
}
}
@@ -3779,19 +3772,22 @@ 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 2011-12-15 17:14:28 +0000
+++ b/sql/sql_optimizer.cc 2012-01-05 13:13:15 +0000
@@ -570,8 +570,10 @@ 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);
+ }
if ((group_list=create_distinct_group(thd, ref_ptrs,
order, fields_list, all_fields,
&all_order_fields_used)))
@@ -791,6 +793,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.
@@ -798,54 +820,78 @@ 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) ? m_select_limit : HA_POS_ERROR;
+
+ 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=0; // 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 2011-12-15 09:00:42 +0000
+++ b/sql/sql_optimizer.h 2012-01-05 13:13:15 +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 2011-12-19 09:53:30 +0000
+++ b/sql/sql_select.cc 2012-01-05 13:13:15 +0000
@@ -856,6 +856,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))
{
/*
@@ -865,24 +866,35 @@ 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");
-
+ }
DBUG_VOID_RETURN;
}
@@ -3572,6 +3584,38 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
}
/**
+ Test if REF_OR_NULL optimization will be used if the specified
+ ref_key is used for REF-access to 'tab'
+
+ @retval
+ true JT_REF_OR_NULL we be used
+ @retval
+ false no JT_REF_OR_NULL access
+*/
+bool
+is_ref_or_null_optimized(const JOIN_TAB *tab, uint ref_key)
+{
+ if (tab->keyuse)
+ {
+ const Key_use *keyuse= tab->keyuse;
+ while (keyuse->key != ref_key && keyuse->table == tab->table)
+ keyuse++;
+
+ table_map const_tables= tab->join->const_table_map;
+ do
+ {
+ if (!(keyuse->used_tables & ~const_tables))
+ {
+ if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)
+ return true;
+ }
+ keyuse++;
+ } while (keyuse->key == ref_key && keyuse->table == tab->table);
+ }
+ return false;
+}
+
+/**
Test if we can use one of the 'usable_keys' instead of 'ref' key
for sorting.
@@ -3584,12 +3628,13 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
*/
static uint
-test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts,
+test_if_subkey(ORDER *order, JOIN_TAB *tab, uint ref, uint ref_key_parts,
const key_map *usable_keys)
{
uint nr;
uint min_length= (uint) ~0;
uint best= MAX_KEY;
+ TABLE *table= tab->table;
KEY_PART_INFO *ref_key_part= table->key_info[ref].key_part;
KEY_PART_INFO *ref_key_part_end= ref_key_part + ref_key_parts;
@@ -3600,6 +3645,7 @@ test_if_subkey(ORDER *order, TABLE *tabl
table->key_info[nr].key_parts >= ref_key_parts &&
is_subkey(table->key_info[nr].key_part, ref_key_part,
ref_key_part_end) &&
+ !is_ref_or_null_optimized(tab, nr) &&
test_if_order_by_key(order, table, nr))
{
min_length= table->key_info[nr].key_length;
@@ -3729,6 +3775,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
Plan_change_watchdog watchdog(tab, no_changes);
+ /* Sorting a single row can always be skipped */
+ if (tab->type == JT_EQ_REF ||
+ tab->type == JT_CONST ||
+ tab->type == JT_SYSTEM)
+ {
+ DBUG_RETURN(1);
+ }
+
/*
Keys disabled by ALTER TABLE ... DISABLE KEYS should have already
been taken into account.
@@ -3810,7 +3864,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
if (table->covering_keys.is_set(ref_key))
usable_keys.intersect(table->covering_keys);
- if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
+ if ((new_ref_key= test_if_subkey(order, tab, ref_key, ref_key_parts,
&usable_keys)) < MAX_KEY)
{
/* Found key that can be used to retrieve data in sorted order */
@@ -3832,6 +3886,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
tab->join->const_table_map))
goto use_filesort;
+ DBUG_ASSERT(tab->type != JT_REF_OR_NULL && tab->type != JT_FT);
pick_table_access_method(tab);
}
else
@@ -3973,11 +4028,16 @@ check_reverse_order:
(select && select->quick && select->quick!=save_quick);
/*
- If ref_key used index tree reading only ('Using index' in EXPLAIN),
- and best_key doesn't, then revert the decision.
+ If 'best_key' has changed from prev. 'ref_key':
+ Update strategy for using index tree reading only
+ ('Using index' in EXPLAIN)
*/
- if (!table->covering_keys.is_set(best_key))
- table->set_keyread(FALSE);
+ if (best_key != ref_key)
+ {
+ const bool using_index=
+ (table->covering_keys.is_set(best_key) && !table->no_keyread);
+ table->set_keyread(using_index);
+ }
if (!quick_created)
{
if (select) // Throw any existing quick select
@@ -3988,8 +4048,6 @@ check_reverse_order:
join_read_first:join_read_last;
tab->type=JT_INDEX_SCAN; // Read with index_first(), index_next()
- if (table->covering_keys.is_set(best_key))
- table->set_keyread(TRUE);
table->file->ha_index_or_rnd_end();
if (tab->join->select_options & SELECT_DESCRIBE)
{
@@ -4007,6 +4065,7 @@ check_reverse_order:
method is actually used.
*/
DBUG_ASSERT(tab->select->quick);
+ DBUG_ASSERT(tab->select->quick->index==(uint)best_key);
tab->type=JT_ALL;
tab->use_quick=QS_RANGE;
tab->ref.key= -1;
@@ -4636,7 +4695,7 @@ bool JOIN::change_result(select_result *
@param ref_key
* 0 <= key < MAX_KEY - key number (hint) to start the search
* -1 - no key number provided
- @param select_limit LIMIT value
+ @param select_limit LIMIT value, or HA_POS_ERROR if no limit
@param [out] new_key Key number if success, otherwise undefined
@param [out] new_key_direction Return -1 (reverse) or +1 if success,
otherwise undefined
@@ -4684,6 +4743,7 @@ test_if_cheaper_ordering(const JOIN_TAB
ha_rows table_records= table->file->stats.records;
bool group= join && join->group && order == join->group_list;
ha_rows ref_key_quick_rows= HA_POS_ERROR;
+ const bool has_limit= (select_limit != HA_POS_ERROR);
/*
If not used with LIMIT, only use keys if the whole query can be
@@ -4776,38 +4836,47 @@ test_if_cheaper_ordering(const JOIN_TAB
be included into the result set.
*/
if (select_limit > table_records/rec_per_key)
- select_limit= table_records;
+ select_limit= table_records;
else
select_limit= (ha_rows) (select_limit*rec_per_key);
}
- /*
- If tab=tk is not the last joined table tn then to get first
- L records from the result set we can expect to retrieve
- only L/fanout(tk,tn) where fanout(tk,tn) says how many
- rows in the record set on average will match each row tk.
- Usually our estimates for fanouts are too pessimistic.
- So the estimate for L/fanout(tk,tn) will be too optimistic
- and as result we'll choose an index scan when using ref/range
- access + filesort will be cheaper.
- */
- select_limit= (ha_rows) (select_limit < fanout ?
- 1 : select_limit/fanout);
/*
- We assume that each of the tested indexes is not correlated
- with ref_key. Thus, to select first N records we have to scan
- N/selectivity(ref_key) index entries.
- selectivity(ref_key) = #scanned_records/#table_records =
- table->quick_condition_rows/table_records.
- In any case we can't select more than #table_records.
- N/(table->quick_condition_rows/table_records) > table_records
- <=> N > table->quick_condition_rows.
- */
- if (select_limit > table->quick_condition_rows)
- select_limit= table_records;
- else
- select_limit= (ha_rows) (select_limit *
- (double) table_records /
- table->quick_condition_rows);
+ If a LIMIT is in effect:
+ We take join-fanout and selectivity into consideration to
+ estimate how many rows to read from tab before we meet the
+ 'limit' for the final resultset.
+ */
+ if (has_limit)
+ {
+ /*
+ If tab=tk is not the last joined table tn then to get first
+ L records from the result set we can expect to retrieve
+ only L/fanout(tk,tn) where fanout(tk,tn) says how many
+ rows in the record set on average will match each row tk.
+ Usually our estimates for fanouts are too pessimistic.
+ So the estimate for L/fanout(tk,tn) will be too optimistic
+ and as result we'll choose an index scan when using ref/range
+ access + filesort will be cheaper.
+ */
+ select_limit= (ha_rows) (select_limit < fanout ?
+ 1 : select_limit/fanout);
+ /*
+ We assume that each of the tested indexes is not correlated
+ with ref_key. Thus, to select first N records we have to scan
+ N/selectivity(ref_key) index entries.
+ selectivity(ref_key) = #scanned_records/#table_records =
+ table->quick_condition_rows/table_records.
+ In any case we can't select more than #table_records.
+ N/(table->quick_condition_rows/table_records) > table_records
+ <=> N > table->quick_condition_rows.
+ */
+ if (select_limit > table->quick_condition_rows)
+ select_limit= table_records;
+ else
+ select_limit= (ha_rows) (select_limit *
+ (double) table_records /
+ table->quick_condition_rows);
+ }
rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1];
set_if_bigger(rec_per_key, 1);
/*
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (ole.john.aske:3704 to 3705) WL#5940 | Ole John Aske | 9 Jan |
