diff options
Diffstat (limited to 'mysql-test/main/sort_nest_index.result')
| -rw-r--r-- | mysql-test/main/sort_nest_index.result | 342 |
1 files changed, 342 insertions, 0 deletions
diff --git a/mysql-test/main/sort_nest_index.result b/mysql-test/main/sort_nest_index.result new file mode 100644 index 00000000000..7dae6c6c93f --- /dev/null +++ b/mysql-test/main/sort_nest_index.result @@ -0,0 +1,342 @@ +CREATE TABLE t1 (a int, b int, c int, KEY a_b (a,b), KEY a_c (a,c)); +insert into t1 values (0,1,0), (0,2,0), (0,3,0), (0,4,0), (0,5,0), (0,6,0); +insert into t1 values (1,7,1), (1,8,1), (1,9,1), (1,10,1), (1,11,1), (1,12,1); +insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2); +insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2); +# +# index a_b should be used, no need for filesort +# +set use_sort_nest= 1; +select a,b,c from t1 where a=1 and c=2 order by b limit 5; +a b c +1 7 2 +1 7 2 +1 8 2 +1 8 2 +1 9 2 +explain select a,b,c from t1 where a=1 and c=2 order by b limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_b,a_c a_b 5 NULL 10 Using index condition; Using where +set use_sort_nest= 0; +explain select a,b,c from t1 where a=1 and c=2 order by b limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_b,a_c a_b 5 NULL 18 Using where +drop table t1; +# +# Tests where Index(scan, ref or range access) satisfies the ORDERING +# +CREATE TABLE t1 (a int, b int, c int, KEY a_b (a,b), KEY a_c (a,c)); +insert into t1 values (0,1,0), (0,2,0), (0,3,0), (0,4,0), (0,5,0), (0,6,0); +insert into t1 values (1,7,1), (1,8,1), (1,9,1), (1,10,1), (1,11,1), (1,12,1); +insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2); +insert into t1 values (1,7,2), (1,8,2), (1,9,2), (1,10,2), (1,11,2), (1,12,2); +insert into t1 values (1,1,2); +# index key a_b, no need for filesort +set optimizer_trace=1; +set use_sort_nest=1; +select a,b,c from t1 where a=1 and c=2 order by b limit 10; +a b c +1 1 2 +1 7 2 +1 7 2 +1 8 2 +1 8 2 +1 9 2 +1 9 2 +1 10 2 +1 10 2 +1 11 2 +explain select a,b,c from t1 where a=1 and c=2 order by b limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_b,a_c a_b 5 NULL 19 Using index condition; Using where +set use_sort_nest=0; +explain select a,b,c from t1 where a=1 and c=2 order by b limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a_b,a_c a_b 5 NULL 19 Using where +drop table t1; +CREATE TABLE t1( +a int NOT NULL, +b char NULL, +PRIMARY KEY(a) +); +INSERT INTO t1 VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d'); +# +# Should use index condition +# +set use_sort_nest= 1; +SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a +LIMIT 2; +a b +1 a +2 b +EXPLAIN SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +set use_sort_nest= 0; +EXPLAIN SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +# +# Should not use index condition as ORDER by DESC is used +# +set use_sort_nest= 1; +EXPLAIN SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a DESC +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where +SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a DESC +LIMIT 2; +a b +2 b +1 a +set use_sort_nest= 0; +EXPLAIN SELECT * FROM t1 +WHERE a BETWEEN 1 and 2 +ORDER BY a DESC +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where +drop table t1; +create table t1(a int, b int, c int, key(a), key a_b(a,b)); +insert into t1 values (0,1,0), (0,2,0), (0,3,0); +insert into t1 values (1,6,1), (1,7,1), (1,5,1); +insert into t1 values (2,8,2), (2,9,3), (2,10,4); +insert into t1 values (3,1,5); +create table t2(a int, b int, c int, key(b), key(c)); +insert into t2 select a, b, c from t1; +# +# Testing using of Indexes on first non-const table +# +# +# Using range scan +# +set use_sort_nest= 1; +SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.b > 8 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +a b c a b c +2 9 3 2 9 3 +2 10 4 2 10 4 +EXPLAIN SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.b > 8 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,a_b a_b 10 NULL 2 Using index condition +1 SIMPLE t2 ref b b 5 test.t1.b 1 +set use_sort_nest= 0; +EXPLAIN SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.b > 8 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,a_b a_b 10 NULL 2 Using index condition +1 SIMPLE t2 ref b b 5 test.t1.b 1 +# +# Using ref access +# +set use_sort_nest= 1; +SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.c >= 1 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +a b c a b c +2 8 2 2 8 2 +2 9 3 2 9 3 +2 10 4 2 10 4 +EXPLAIN SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.c >= 1 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,a_b a_b 5 const 3 Using index condition; Using where +1 SIMPLE t2 ref b,c b 5 test.t1.b 1 Using where +set use_sort_nest= 0; +EXPLAIN SELECT * +FROM +t1,t2 +WHERE +t1.a=2 AND t2.c >= 1 AND +t1.b=t2.b +ORDER BY t1.b LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a,a_b a_b 5 const 3 Using where +1 SIMPLE t2 ref b,c b 5 test.t1.b 1 Using where +drop table t1,t2; +# TESTS with INDEX HINTS +set use_sort_nest=1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int,c int, key idx1(a), key idx2(a,b), key idx3(c)); +insert into t1 select a,a,a from t0 where a <5; +analyze table t1 persistent for all; +# +# Index idx1 to be used for index scan +# +set use_sort_nest=1; +SELECT * from t1 where b > 0 order by t1.a limit 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 where b > 0 order by t1.a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx1 5 NULL 2 Using where +set use_sort_nest=0; +EXPLAIN SELECT * from t1 where b > 0 order by t1.a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx1 5 NULL 2 Using where +# +# Index idx2 to be used for index scan(USE INDEX is used) +# +set use_sort_nest=1; +SELECT * from t1 USE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 USE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +set use_sort_nest=0; +EXPLAIN SELECT * from t1 USE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +# +# Index idx2 to be used for index scan(USE INDEX for ORDER BY is used) +# +set use_sort_nest=1; +SELECT * from t1 USE INDEX FOR ORDER BY(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +set use_sort_nest=0; +EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +# +# Use Filesort as idx3 does not resolve ORDER BY clause +# +set use_sort_nest=1; +SELECT * from t1 USE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using filesort +set use_sort_nest=0; +EXPLAIN SELECT * from t1 USE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using filesort +# +# Using index idx2 as idx1 is ignored +# +set use_sort_nest=1; +SELECT * from t1 IGNORE INDEX(idx1) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 IGNORE INDEX(idx1) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +set use_sort_nest=0; +EXPLAIN SELECT * from t1 IGNORE INDEX(idx1) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +# +# Use index idx2 for sorting, it is forced here +# +set use_sort_nest=1; +SELECT * from t1 FORCE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 FORCE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +set use_sort_nest=0; +EXPLAIN SELECT * from t1 FORCE INDEX(idx2) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL idx2 10 NULL 2 Using where +# +# Use FILESORT as idx3 cannot resolve ORDER BY clause +# +set use_sort_nest=1; +SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +a b c +1 1 1 +2 2 2 +EXPLAIN SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using filesort +set use_sort_nest=0; +EXPLAIN SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3) +WHERE b > 0 +ORDER BY t1.a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using filesort +drop table t0,t1; |