diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/main/mysqld--help.result | 2 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest.result | 2123 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest.test | 670 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_dbt3.result | 610 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_dbt3.test | 216 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_dbt3_innodb.result | 611 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_dbt3_innodb.test | 6 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_index.result | 342 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_index.test | 249 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_sj.result | 697 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_sj.test | 201 | ||||
| -rw-r--r-- | mysql-test/main/sort_nest_subselect.test | 99 |
12 files changed, 5826 insertions, 0 deletions
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index c55f535cd35..a9963b17b14 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1401,6 +1401,7 @@ The following specify which files/extra groups are read (specified before remain Prohibit update of a VIEW, which does not contain a key of the underlying table and the query uses a LIMIT clause (usually get from GUI tools) + --use-sort-nest Enable the sort nest --use-stat-tables=name Specifies how to use system statistics tables. One of: NEVER, COMPLEMENTARY, PREFERABLY, @@ -1780,6 +1781,7 @@ transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 transaction-read-only FALSE updatable-views-with-limit YES +use-sort-nest FALSE use-stat-tables PREFERABLY_FOR_QUERIES userstat FALSE verbose TRUE diff --git a/mysql-test/main/sort_nest.result b/mysql-test/main/sort_nest.result new file mode 100644 index 00000000000..11bfd8ad1db --- /dev/null +++ b/mysql-test/main/sort_nest.result @@ -0,0 +1,2123 @@ +set use_sort_nest=1; +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_100; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +# +# sort nest on (t2,t1) +# ref(sort-nest.b) access on table t3 +# +EXPLAIN SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 SIMPLE t3 ref a a 5 sort-nest.b 1 Using index +EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.a = t1.a" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.b desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["sort-nest.b"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } +} +SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +a b b a +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +DROP TABLE t0,t1,t2,t3; +CREATE TABLE t1(a int, b int); +INSERT INTO t1 SELECT seq-1, seq-1 from seq_1_to_100; +CREATE TABLE t2(a int, b int); +INSERT INTO t2(a,b) VALUES (1,1), (2,2); +INSERT INTO t2 SELECT seq-1, seq-1 from seq_1_to_100; +CREATE TABLE t3(a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +CREATE FUNCTION f1(a int) RETURNS INT +BEGIN +DECLARE b INT DEFAULT 0; +RETURN a + b; +END| +Covering 3 table joins + +# sorting on table t2 +# t2.a > 95 would be attached to table t2 +# t1.b=t2.a would be attached to table t1; +# t3.a= sort-nest.b would be attached to table t3 + +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t2.b", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 4.6875, + "attached_condition": "t2.a > 95" + } + } + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 4.6875, + "attached_condition": "t1.a = t2.a" + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a = t1.b" + } + } +} +SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +a b a b a b +96 96 96 96 96 96 +97 97 97 97 97 97 +98 98 98 98 98 98 +99 99 99 99 99 99 +# {t1,t2} part of the nest +# t1.a > 95 would be attached to table t1 +# t1.b=t2.a would be attached to table t2; +# t3.a= sort-nest.b would be attached to table t3 + +ALTER TABLE t2 ADD KEY(a); +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 1 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 4.6875, + "attached_condition": "t1.a > 95 and t1.a is not null" + }, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100 + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a = `sort-nest`.b" + } + } +} +SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b +LIMIT 5; +a b a b a b +96 96 96 96 96 96 +97 97 97 97 97 97 +98 98 98 98 98 98 +99 99 99 99 99 99 +ALTER TABLE t2 DROP KEY a; + +# {t1,t2} part of the sort nest +# (t2.a < 2 or t1.b > 98) would be attached to table t2 + +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE (t3.a < 2 and t2.a < 2) OR (t1.b > 98 and t3.b > 98) +ORDER BY t1.a, t2.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE (t3.a < 2 and t2.a < 2) OR (t1.b > 98 and t3.b > 98) +ORDER BY t1.a, t2.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL", + "attached_condition": "t2.a < 2 or t1.b > 98" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a, `sort-nest`.b", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a < 2 and `sort-nest`.a < 2 or `sort-nest`.b > 98 and t3.b > 98" + } + } +} +SELECT * FROM t1,t2,t3 +WHERE (t3.a < 2 and t2.a < 2) OR (t1.b > 98 and t3.b > 98) +ORDER BY t1.a, t2.b +LIMIT 5; +a b a b a b +0 0 1 1 0 0 +0 0 1 1 1 1 +1 1 1 1 0 0 +1 1 1 1 1 1 +2 2 1 1 0 0 + +# {t1,t2} part of the nest +# t2.a < 2 or f1(t1.b) attached to table t2 +# t1.b=t2.a would be attached to table t2; + +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE (t3.a<2 AND t2.a <2) OR (f1(t1.b) > 98 AND t3.b > 98) +ORDER BY t1.a,t2.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE (t3.a<2 AND t2.a <2) OR (f1(t1.b) > 98 AND t3.b > 98) +ORDER BY t1.a,t2.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a, `sort-nest`.b", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a < 2 and `sort-nest`.a < 2 or f1(`sort-nest`.b) > 98 and t3.b > 98" + } + } +} +SELECT * FROM t1,t2,t3 +WHERE (t3.a<2 AND t2.a <2) OR (f1(t1.b) > 98 AND t3.b > 98) +ORDER BY t1.a,t2.b +LIMIT 5; +a b a b a b +0 0 0 0 0 0 +0 0 0 0 1 1 +0 0 1 1 0 0 +0 0 1 1 1 1 +0 0 1 1 0 0 +# +# Removing constant from the order by clause +# +EXPLAIN SELECT * FROM t1,t2 +WHERE t1.a > 95 AND t1.a=t2.a +ORDER BY t2.a +LIMIT 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 +WHERE t1.a > 95 AND t1.a=t2.a +ORDER BY t2.a +LIMIT 4; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t2.a", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 4.6875, + "attached_condition": "t1.a > 95" + } + } + }, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 4.6875, + "attached_condition": "t2.a = t1.a" + } + } +} +SELECT * FROM t1,t2 +WHERE t1.a > 95 AND t1.a=t2.a +ORDER BY t2.a +LIMIT 4; +a b a b +96 96 96 96 +97 97 97 97 +98 98 98 98 +99 99 99 99 +EXPLAIN SELECT * FROM t1,t2 +WHERE t1.a > 95 and t1.a=t2.a +ORDER BY 1+2,t2.a limit 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 +WHERE t1.a > 95 and t1.a=t2.a +ORDER BY 1+2,t2.a limit 4; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t2.a", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 4.6875, + "attached_condition": "t1.a > 95" + } + } + }, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 4.6875, + "attached_condition": "t2.a = t1.a" + } + } +} +SELECT * FROM t1,t2 +WHERE t1.a > 95 and t1.a=t2.a +ORDER BY 1+2,t2.a limit 4; +a b a b +96 96 96 96 +97 97 97 97 +98 98 98 98 +99 99 99 99 +# +# Equality propagation, both the queries should use a +# sort nest on {t1,t2} +# +EXPLAIN SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t1.b DESC, t2.a DESC limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t1.b DESC, t2.a DESC limit 3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.b = `sort-nest`.b" + } + } +} +SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t1.b DESC, t2.a DESC limit 3; +b a b a +99 99 99 99 +99 98 99 99 +99 97 99 99 +EXPLAIN SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b DESC, t2.a DESC +LIMIT 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b DESC, t2.a DESC +LIMIT 3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.b = `sort-nest`.b" + } + } +} +SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b DESC, t2.a DESC +LIMIT 3; +b a b a +99 99 99 99 +99 98 99 99 +99 97 99 99 +# +# Equality propagation also for arguments of expressions, +# the plan should use a sort nest on {t1,t2} +# +EXPLAIN SELECT t3.b,t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b + 1 DESC, t2.a DESC +LIMIT 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT t3.b,t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b + 1 DESC, t2.a DESC +LIMIT 3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b + 1 desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.b = `sort-nest`.b" + } + } +} +SELECT t3.b,t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.b=t3.b +ORDER BY t3.b + 1 DESC, t2.a DESC +LIMIT 3; +b a b a +99 99 99 99 +99 98 99 99 +99 97 99 99 +# +# Rows for the sort-nest should be the cardinality of the join of +# inner tables of the sort-nest +# +# Rows for sort nest would be 9894 here +ALTER TABLE t1 ADD KEY(a); +EXPLAIN SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.a > 5 and t1.b=t3.b +ORDER BY t1.b DESC, t2.a DESC +LIMIT 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 100 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +SELECT t3.b, t2.a, t1.b, t1.a +FROM t1,t2,t3 +WHERE t1.a > 5 and t1.b=t3.b +ORDER BY t1.b DESC, t2.a DESC +LIMIT 3; +b a b a +99 99 99 99 +99 98 99 99 +99 97 99 99 +ALTER TABLE t1 DROP KEY a; +# +# With having clause we can't have a sort-nest +# +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a +HAVING t1.a > 95 +ORDER BY t2.b,t1.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where; Using join buffer (incremental, BNL join) +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a +HAVING t1.a > 95 +ORDER BY t2.b,t1.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "having_condition": "t1.a > 95", + "filesort": { + "sort_key": "t2.b, t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL", + "attached_condition": "t2.a = t1.a" + }, + "block-nl-join": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100 + }, + "buffer_type": "incremental", + "buffer_size": "149Kb", + "join_type": "BNL", + "attached_condition": "t3.a = t1.b" + } + } + } + } +} +SELECT * FROM t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a +HAVING t1.a > 95 +ORDER BY t2.b,t1.b +LIMIT 5; +a b a b a b +96 96 96 96 96 96 +97 97 97 97 97 97 +98 98 98 98 98 98 +99 99 99 99 99 99 +EXPLAIN SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b,t1.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 102 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b,t1.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 4.6875, + "attached_condition": "t1.a > 95" + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 102, + "filtered": 4.6875 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.a = t1.a" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b, `sort-nest`.b", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a = `sort-nest`.b" + } + } +} +SELECT * FROM t1,t2,t3 +WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a +ORDER BY t2.b,t1.b +LIMIT 5; +a b a b a b +96 96 96 96 96 96 +97 97 97 97 97 97 +98 98 98 98 98 98 +99 99 99 99 99 99 +# +# Selectivity estimates taken into account for sort-nest{t1,t2} +# +CREATE INDEX idx1 ON t1(b); +CREATE INDEX idx2 ON t2(a); +CREATE INDEX idx3 ON t3(b); +EXPLAIN SELECT * from t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a AND t1.b < 5 AND t3.b < 900 +ORDER BY t2.b +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 5 NULL 3 Using index condition; Using where +1 SIMPLE t2 ref idx2 idx2 5 test.t1.a 1 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL idx3 NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * from t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a AND t1.b < 5 AND t3.b < 900 +ORDER BY t2.b +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["idx1"], + "key": "idx1", + "key_length": "5", + "used_key_parts": ["b"], + "rows": 3, + "filtered": 100, + "index_condition": "t1.b < 5", + "attached_condition": "t1.a is not null" + }, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["idx2"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.a"], + "rows": 1, + "filtered": 100 + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "possible_keys": ["idx3"], + "rows": 1000, + "filtered": 0.6953, + "attached_condition": "t3.a = `sort-nest`.b and t3.b < 900" + } + } +} +SELECT * from t1,t2,t3 +WHERE t1.a=t2.a AND t1.b = t3.a AND t1.b < 5 AND t3.b < 900 +ORDER BY t2.b +LIMIT 5; +a b a b a b +0 0 0 0 0 0 +1 1 1 1 1 1 +1 1 1 1 1 1 +2 2 2 2 2 2 +2 2 2 2 2 2 +DROP INDEX idx1 ON t1; +DROP INDEX idx2 ON t2; +DROP INDEX idx3 ON t3; +DROP TABLE t1,t2,t3; +DROP FUNCTION f1; +Derived table inside a sort-nest +CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t1 VALUES +('r','x'), ('x','x'), ('x','x'), ('r','x'), ('x','x'); +CREATE TABLE t2 (f1 varchar(1), f2 varchar(1)); +INSERT INTO t2 VALUES ('s','x'); +CREATE TABLE t3 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t3 VALUES +(NULL,'x'), (NULL,'f'), ('t','x'), (NULL,'j'), ('g','x'); +CREATE TABLE t4 (f1 int, f2 varchar(1), KEY (f2,f1)) ; +INSERT INTO t4 VALUES (2,'x'), (1,'x'); +EXPLAIN SELECT t.f1 as f +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 +ORDER BY f LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t4 index f2 f2 9 NULL 2 Using where; Using index +1 PRIMARY <derived2> ref key1 key1 4 test.t4.f2 2 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 4 Using filesort +1 PRIMARY t3 ref f2 f2 4 sort-nest.f2 2 Using index +2 DERIVED t2 system NULL NULL NULL NULL 1 Using temporary +2 DERIVED t1 ALL f2 NULL NULL NULL 5 Using where +EXPLAIN FORMAT=JSON SELECT t.f1 as f +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 +ORDER BY f LIMIT 10; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t4", + "access_type": "index", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "9", + "used_key_parts": ["f2", "f1"], + "rows": 2, + "filtered": 100, + "attached_condition": "t4.f2 is not null", + "using_index": true + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key1"], + "key": "key1", + "key_length": "4", + "used_key_parts": ["f1"], + "ref": ["test.t4.f2"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "system", + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["f2"], + "rows": 5, + "filtered": 100, + "attached_condition": "t1.f2 = 'x'" + } + } + } + } + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.f1", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "4", + "used_key_parts": ["f2"], + "ref": ["sort-nest.f2"], + "rows": 2, + "filtered": 100, + "using_index": true + } + } +} +SELECT t.f1 as f +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 +ORDER BY f LIMIT 10; +f +x +x +x +x +x +x +should use the sort-nest too like the query above +EXPLAIN SELECT t4.f1 as f, t.f1 as g +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f,g +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t4 index f2 f2 9 NULL 2 Using where; Using index +1 PRIMARY <derived2> ref key1 key1 4 test.t4.f2 2 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 4 Using filesort +1 PRIMARY t3 ref f2 f2 4 sort-nest.f2 2 Using index +2 DERIVED t2 system NULL NULL NULL NULL 1 Using temporary +2 DERIVED t1 ALL f2 NULL NULL NULL 5 Using where +EXPLAIN FORMAT=JSON SELECT t4.f1 as f, t.f1 as g +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f,g +LIMIT 10; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t4", + "access_type": "index", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "9", + "used_key_parts": ["f2", "f1"], + "rows": 2, + "filtered": 100, + "attached_condition": "t4.f2 is not null", + "using_index": true + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key1"], + "key": "key1", + "key_length": "4", + "used_key_parts": ["f1"], + "ref": ["test.t4.f2"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "system", + "rows": 1, + "filtered": 100 + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["f2"], + "rows": 5, + "filtered": 100, + "attached_condition": "t1.f2 = 'x'" + } + } + } + } + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.f1, `sort-nest`.f1", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "4", + "used_key_parts": ["f2"], + "ref": ["sort-nest.f2"], + "rows": 2, + "filtered": 100, + "using_index": true + } + } +} +SELECT t4.f1 as f, t.f1 as g +FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 +WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f,g +LIMIT 10; +f g +1 x +1 x +1 x +2 x +2 x +2 x +DROP TABLE t1,t2,t3,t4; +views inside a sort-nest +CREATE TABLE t0 (x int); +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int, c int default 0); +INSERT t0 (x) VALUES (0),(10); +INSERT t1 (a) VALUES (1), (2); +INSERT t2 (b) VALUES (1), (2); +CREATE VIEW v1 as SELECT t2.b,t2.c FROM t1, t2 +WHERE t1.a=t2.b and t2.b < 3 WITH CHECK OPTION; +EXPLAIN SELECT * FROM v1,t0 +WHERE b<3 +ORDER BY x,b DESC +LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +EXPLAIN FORMAT=JSON SELECT * FROM v1,t0 +WHERE b<3 +ORDER BY x,b DESC +LIMIT 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.a < 3 and t1.a < 3" + }, + "block-nl-join": { + "table": { + "table_name": "t0", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.x, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t2.b = `sort-nest`.a" + } + } +} +SELECT * FROM v1,t0 +WHERE b<3 +ORDER BY x,b DESC +LIMIT 2; +b c x +2 0 0 +1 0 0 +DROP TABLE t0,t1,t2; +DROP VIEW v1; +# Primary key considered as the key that could achieve ordering +CREATE TABLE t1 (id char(32) NOT NULL primary key); +INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); +CREATE TABLE t2 (id char(32) NOT NULL primary key); +INSERT INTO t2 VALUES (0), (1), (2), (3); +EXPLAIN SELECT t1.id +FROM t1 INNER JOIN t2 ON t1.id=t2.id +ORDER BY t2.id LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index PRIMARY PRIMARY 32 NULL 2 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 32 test.t2.id 1 Using index +EXPLAIN FORMAT=JSON SELECT t1.id +FROM t1 INNER JOIN t2 ON t1.id=t2.id +ORDER BY t2.id LIMIT 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "index", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "32", + "used_key_parts": ["id"], + "rows": 2, + "filtered": 100, + "using_index": true + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "32", + "used_key_parts": ["id"], + "ref": ["test.t2.id"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } +} +SELECT t1.id +FROM t1 INNER JOIN t2 ON t1.id=t2.id +ORDER BY t2.id LIMIT 2; +id +0 +1 +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT seq-1, seq-1 from seq_1_to_10; +CREATE TABLE t2 as SELECT * from t1; +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +# +# Outer Join +# +# sort-nest(t2,t1) and t3 outside the nest +EXPLAIN SELECT * from t2,t1 left join t3 on t3.a=t1.b +order by t2.a desc,t1.a desc limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT * from t2,t1 left join t3 on t3.a=t1.b +order by t2.a desc,t1.a desc limit 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "trigcond(t3.a = `sort-nest`.b)" + } + } +} +SELECT * from t2,t1 left join t3 on t3.a=t1.b +order by t2.a desc,t1.a desc limit 5; +a b a b a b +9 9 9 9 9 9 +9 9 8 8 8 8 +9 9 7 7 7 7 +9 9 6 6 6 6 +9 9 5 5 5 5 +# +# no sort-nest as all the inner tables of the outer join will be +# inside the nest, this should use temporary table to sort after the +# entire join is computed +# +EXPLAIN SELECT * FROM t2 LEFT JOIN (t1 LEFT JOIN t3 ON t3.a=t1.b) +ON t2.b=t1.a +ORDER BY t2.a DESC,t1.a DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where; Using join buffer (incremental, BNL join) +EXPLAIN FORMAT=JSON SELECT * FROM t2 LEFT JOIN (t1 LEFT JOIN t3 ON t3.a=t1.b) +ON t2.b=t1.a +ORDER BY t2.a DESC,t1.a DESC LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "const_condition": "1", + "filesort": { + "sort_key": "t2.a desc, t1.a desc", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "141", + "join_type": "BNL", + "attached_condition": "trigcond(t1.a = t2.b)" + }, + "block-nl-join": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100 + }, + "buffer_type": "incremental", + "buffer_size": "1Kb", + "join_type": "BNL", + "attached_condition": "trigcond(t3.a = t1.b)" + } + } + } + } +} +SELECT * FROM t2 LEFT JOIN (t1 LEFT JOIN t3 ON t3.a=t1.b) +ON t2.b=t1.a +ORDER BY t2.a DESC,t1.a DESC LIMIT 5; +a b a b a b +9 9 9 9 9 9 +8 8 8 8 8 8 +7 7 7 7 7 7 +6 6 6 6 6 6 +5 5 5 5 5 5 +DROP TABLE t1,t2,t3; +# +# Sort-nest with prepared statements +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT seq-1,seq-1 from seq_1_to_10; +CREATE TABLE t2 as SELECT * from t1; +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +# sort-nest on table t1,t2 +prepare ps1 from "EXPLAIN SELECT * FROM t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC,t1.a DESC + LIMIT 5"; +EXECUTE ps1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +EXECUTE ps1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 Using where +# sort-nest on table t1,t2 +PREPARE ps2 from "EXPLAIN FORMAT=JSON + SELECT * from t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC, t1.a DESC + LIMIT 5"; +EXECUTE ps2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a = `sort-nest`.b + 1" + } + } +} +EXECUTE ps2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100, + "attached_condition": "t3.a = `sort-nest`.b + 1" + } + } +} +# sort-nest on table t1,t2 +PREPARE ps3 from "SELECT * from t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC, t1.a DESC + LIMIT 5"; +EXECUTE ps3; +a b a b a b +9 9 9 9 10 10 +9 9 8 8 9 9 +9 9 7 7 8 8 +9 9 6 6 7 7 +9 9 5 5 6 6 +EXECUTE ps3; +a b a b a b +9 9 9 9 10 10 +9 9 8 8 9 9 +9 9 7 7 8 8 +9 9 6 6 7 7 +9 9 5 5 6 6 +DEALLOCATE PREPARE ps1; +DEALLOCATE PREPARE ps2; +DEALLOCATE PREPARE ps3; +DROP TABLE t1,t2,t3; +# INDEPENDENT SUBQUERIES +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a,a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3 (a int, b int, c int); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_1000; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +# +# sort-nest(t2,t1) and independent subquery in the SELECT list +# +EXPLAIN SELECT (SELECT A.a FROM t3 A WHERE A.a > 5 limit 1) as x, +t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a = t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t3 ALL NULL NULL NULL NULL 1000 +2 SUBQUERY A ALL NULL NULL NULL NULL 1000 Using where +EXPLAIN FORMAT=JSON SELECT (SELECT A.a FROM t3 A WHERE A.a > 5 limit 1) as x, +t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a = t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.a = t1.a" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.b desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100 + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "A", + "access_type": "ALL", + "rows": 1000, + "filtered": 99.219, + "attached_condition": "A.a > 5" + } + } + } + ] + } +} +SELECT (SELECT A.a FROM t3 A WHERE A.a > 5 limit 1) as x, +t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a = t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +x b b a +6 4 4 0 +6 4 4 1 +6 4 4 2 +6 4 4 3 +6 4 4 4 +DROP TABLE t0,t1,t2,t3; +# +# Const table should not form the sort-nest +# +CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY); +CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY); +CREATE TABLE t3 (i3 integer); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), +(9), (10), (11), (12); +INSERT INTO t2 SELECT * FROM t1; +EXPLAIN SELECT t1.*, t2.* +FROM t1 JOIN t2 ON t1.i1 = t2.i2 +LEFT JOIN t3 ON t2.i2 = t3.i3 +ORDER BY t1.i1 +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 system NULL NULL NULL NULL 0 Const row not found +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 12 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 Using index +SELECT t1.*, t2.* +FROM t1 JOIN t2 ON t1.i1 = t2.i2 +LEFT JOIN t3 ON t2.i2 = t3.i3 +ORDER BY t1.i1 +LIMIT 5; +i1 i2 +1 1 +2 2 +3 3 +4 4 +5 5 +DROP TABLE t1,t2,t3; +# All tables are const tables +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (0,0); +CREATE TABLE t2(a int, b int); +INSERT INTO t2 VALUES (0,0); +CREATE TABLE t3(a int, b int); +INSERT INTO t3 VALUES (0,0); +EXPLAIN SELECT t1.a,t2.a,t3.a +FROM t1,t2,t3 +WHERE t1.b = t2.b AND t3.b=t1.b +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 system NULL NULL NULL NULL 1 +1 SIMPLE t3 system NULL NULL NULL NULL 1 +SELECT t1.a,t2.a,t3.a +FROM t1,t2,t3 +WHERE t1.b = t2.b AND t3.b=t1.b +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +a a a +0 0 0 +DROP TABLE t1,t2,t3; +# +# 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 +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 +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 +DROP TABLE t1; +# +# Testing ORDER BY LIMIT with OFFSET, should show the same plan and same +# estimate of rows for the sort-nest +# +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 as SELECT * from t1; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_100; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +EXPLAIN SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t3.a = t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE t3 ref a a 5 sort-nest.a 1 Using index +SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t3.a = t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 10; +a b b a +9 9 9 9 +8 8 8 8 +7 7 7 7 +6 6 6 6 +5 5 5 5 +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +EXPLAIN SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t3.a=t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5 OFFSET 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE t3 ref a a 5 sort-nest.a 1 Using index +SELECT t1.a, t2.b, t1.b, t3.a +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t3.a=t2.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5 OFFSET 5; +a b b a +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +drop table t0,t1,t2,t3; +# +# Constant removed from ORDER BY , so no need of sorting +# +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 as SELECT * from t1; +set use_sort_nest=1; +EXPLAIN SELECT * +FROM t1,t2 +WHERE t1.a=t2.a and t1.b= 4 +ORDER BY t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where +SELECT * +FROM t1,t2 +WHERE t1.a=t2.a and t1.b= 4 +ORDER BY t1.b DESC +LIMIT 5; +a b a b +4 4 4 4 +drop table t0,t1,t2; +# +# ORDER BY clause containing expressions +# +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_10; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY abs(t3.a+t1.b) DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 SIMPLE t3 ref a a 5 sort-nest.a 1 Using index +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY abs(t3.a+t1.b) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +set use_sort_nest=0; +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY abs(t3.a+t1.b) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +# +# No sort nest where ORDER BY item are expensive to compute like +# stored functions, subqueries etc +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN +RETURN a; +END| +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY f1(t3.a+t1.b) DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using index +EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY f1(t3.a+t1.b) DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "f1(t3.a + t1.b) desc", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.b = t1.a and t2.a is not null" + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } + } + } +} +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY f1(t3.a+t1.b) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +set use_sort_nest=0; +EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY f1(t3.a+t1.b) DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using index +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY f1(t3.a+t1.b) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +drop function f1; +# +# Window function in order by clause, sort-nest not allowed +# +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b AND t2.a=t3.a +ORDER BY row_number() OVER (ORDER BY t1.a) DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ref a a 5 test.t2.a 1 Using index +EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b AND t2.a=t3.a +ORDER BY row_number() OVER (ORDER BY t1.a) DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "row_number() over ( order by t1.a) desc", + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t1.a" + } + }, + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.b = t1.a and t2.a is not null" + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100, + "using_index": true + } + } + } + } + } +} +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b AND t2.a=t3.a +ORDER BY row_number() OVER (ORDER BY t1.a) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +set use_sort_nest=0; +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b AND t2.a=t3.a +ORDER BY row_number() OVER (ORDER BY t1.a) DESC +LIMIT 5; +a b b t1.b + t2.b +4 4 4 8 +3 3 3 6 +2 2 2 4 +1 1 1 2 +0 0 0 0 +# +# Subqueries used in order by clause +# +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY (select A.a+t1.b from t1 A limit 1) +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ref a a 5 test.t2.a 1 Using index +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 5 +EXPLAIN FORMAT=JSON SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY (select A.a+t1.b from t1 A limit 1) +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "(subquery#2)", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.b = t1.a and t2.a is not null" + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100, + "using_index": true + }, + "subqueries": [ + { + "expression_cache": { + "state": "uninitialized", + "query_block": { + "select_id": 2, + "table": { + "table_name": "A", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + ] + } + } + } +} +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY (select A.a+t1.b from t1 A limit 1) +LIMIT 5; +a b b t1.b + t2.b +0 0 0 0 +1 1 1 2 +2 2 2 4 +3 3 3 6 +4 4 4 8 +set use_sort_nest=0; +SELECT t1.a, t2.b, t1.b, t1.b + t2.b +FROM t1,t2, t3 +WHERE t1.a=t2.b and t2.a=t3.a +ORDER BY (select A.a+t1.b from t1 A limit 1) +LIMIT 5; +a b b t1.b + t2.b +0 0 0 0 +1 1 1 2 +2 2 2 4 +3 3 3 6 +4 4 4 8 +drop table t0,t1,t2,t3; diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test new file mode 100644 index 00000000000..a26dba836fe --- /dev/null +++ b/mysql-test/main/sort_nest.test @@ -0,0 +1,670 @@ +--source include/have_sequence.inc + +set use_sort_nest=1; + +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_100; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +--echo # +--echo # sort nest on (t2,t1) +--echo # ref(sort-nest.b) access on table t3 +--echo # + +let $query= SELECT t1.a, t2.b, t1.b, t3.a + FROM t1,t2,t3 + WHERE t1.a=t2.a AND t2.b=t3.a + ORDER BY t2.b DESC, t1.b DESC + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +DROP TABLE t0,t1,t2,t3; + +CREATE TABLE t1(a int, b int); +INSERT INTO t1 SELECT seq-1, seq-1 from seq_1_to_100; + +CREATE TABLE t2(a int, b int); +INSERT INTO t2(a,b) VALUES (1,1), (2,2); +INSERT INTO t2 SELECT seq-1, seq-1 from seq_1_to_100; +CREATE TABLE t3(a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +delimiter |; +CREATE FUNCTION f1(a int) RETURNS INT +BEGIN + DECLARE b INT DEFAULT 0; + RETURN a + b; +END| +delimiter ;| + +--echo Covering 3 table joins + +--echo +--echo # sorting on table t2 +--echo # t2.a > 95 would be attached to table t2 +--echo # t1.b=t2.a would be attached to table t1; +--echo # t3.a= sort-nest.b would be attached to table t3 +--echo + +let $query= SELECT * FROM t1,t2,t3 + WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a + ORDER BY t2.b + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # {t1,t2} part of the nest +--echo # t1.a > 95 would be attached to table t1 +--echo # t1.b=t2.a would be attached to table t2; +--echo # t3.a= sort-nest.b would be attached to table t3 +--echo + +let $query= SELECT * FROM t1,t2,t3 + WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a + ORDER BY t2.b + LIMIT 5; + +ALTER TABLE t2 ADD KEY(a); +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +ALTER TABLE t2 DROP KEY a; + +--echo +--echo # {t1,t2} part of the sort nest +--echo # (t2.a < 2 or t1.b > 98) would be attached to table t2 +--echo + +let $query= SELECT * FROM t1,t2,t3 + WHERE (t3.a < 2 and t2.a < 2) OR (t1.b > 98 and t3.b > 98) + ORDER BY t1.a, t2.b + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo +--echo # {t1,t2} part of the nest +--echo # t2.a < 2 or f1(t1.b) attached to table t2 +--echo # t1.b=t2.a would be attached to table t2; +--echo + +let $query= SELECT * FROM t1,t2,t3 + WHERE (t3.a<2 AND t2.a <2) OR (f1(t1.b) > 98 AND t3.b > 98) + ORDER BY t1.a,t2.b + LIMIT 5; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # Removing constant from the order by clause +--echo # + +let $query= SELECT * FROM t1,t2 + WHERE t1.a > 95 AND t1.a=t2.a + ORDER BY t2.a + LIMIT 4; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +let $query= SELECT * FROM t1,t2 + WHERE t1.a > 95 and t1.a=t2.a + ORDER BY 1+2,t2.a limit 4; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # Equality propagation, both the queries should use a +--echo # sort nest on {t1,t2} +--echo # + +let $query= SELECT t3.b, t2.a, t1.b, t1.a + FROM t1,t2,t3 + WHERE t1.b=t3.b + ORDER BY t1.b DESC, t2.a DESC limit 3; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +let $query= SELECT t3.b, t2.a, t1.b, t1.a + FROM t1,t2,t3 + WHERE t1.b=t3.b + ORDER BY t3.b DESC, t2.a DESC + LIMIT 3; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # Equality propagation also for arguments of expressions, +--echo # the plan should use a sort nest on {t1,t2} +--echo # + +let $query=SELECT t3.b,t2.a, t1.b, t1.a + FROM t1,t2,t3 + WHERE t1.b=t3.b + ORDER BY t3.b + 1 DESC, t2.a DESC + LIMIT 3; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # Rows for the sort-nest should be the cardinality of the join of +--echo # inner tables of the sort-nest +--echo # + +--echo # Rows for sort nest would be 9894 here + +ALTER TABLE t1 ADD KEY(a); +let $query= SELECT t3.b, t2.a, t1.b, t1.a + FROM t1,t2,t3 + WHERE t1.a > 5 and t1.b=t3.b + ORDER BY t1.b DESC, t2.a DESC + LIMIT 3; + +eval EXPLAIN $query; +eval $query; +ALTER TABLE t1 DROP KEY a; + +--echo # +--echo # With having clause we can't have a sort-nest +--echo # + +let $query= SELECT * FROM t1,t2,t3 + WHERE t1.a=t2.a AND t1.b = t3.a + HAVING t1.a > 95 + ORDER BY t2.b,t1.b + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +let $query= SELECT * FROM t1,t2,t3 + WHERE t1.a > 95 AND t1.a=t2.a AND t1.b = t3.a + ORDER BY t2.b,t1.b + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # Selectivity estimates taken into account for sort-nest{t1,t2} +--echo # + +CREATE INDEX idx1 ON t1(b); +CREATE INDEX idx2 ON t2(a); +CREATE INDEX idx3 ON t3(b); + +let $query= SELECT * from t1,t2,t3 + WHERE t1.a=t2.a AND t1.b = t3.a AND t1.b < 5 AND t3.b < 900 + ORDER BY t2.b + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +DROP INDEX idx1 ON t1; +DROP INDEX idx2 ON t2; +DROP INDEX idx3 ON t3; +DROP TABLE t1,t2,t3; +DROP FUNCTION f1; + +--echo Derived table inside a sort-nest + +CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t1 VALUES +('r','x'), ('x','x'), ('x','x'), ('r','x'), ('x','x'); + +CREATE TABLE t2 (f1 varchar(1), f2 varchar(1)); +INSERT INTO t2 VALUES ('s','x'); + +CREATE TABLE t3 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t3 VALUES +(NULL,'x'), (NULL,'f'), ('t','x'), (NULL,'j'), ('g','x'); + +CREATE TABLE t4 (f1 int, f2 varchar(1), KEY (f2,f1)) ; +INSERT INTO t4 VALUES (2,'x'), (1,'x'); + +let $query= SELECT t.f1 as f + FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 + WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 + ORDER BY f LIMIT 10; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo should use the sort-nest too like the query above + +let $query= SELECT t4.f1 as f, t.f1 as g + FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 + WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f,g + LIMIT 10; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +DROP TABLE t1,t2,t3,t4; + +--echo views inside a sort-nest +CREATE TABLE t0 (x int); +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int, c int default 0); + +INSERT t0 (x) VALUES (0),(10); +INSERT t1 (a) VALUES (1), (2); +INSERT t2 (b) VALUES (1), (2); + +CREATE VIEW v1 as SELECT t2.b,t2.c FROM t1, t2 + WHERE t1.a=t2.b and t2.b < 3 WITH CHECK OPTION; + +let $query= SELECT * FROM v1,t0 + WHERE b<3 + ORDER BY x,b DESC + LIMIT 2; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; +DROP TABLE t0,t1,t2; +DROP VIEW v1; + +--echo # Primary key considered as the key that could achieve ordering + +CREATE TABLE t1 (id char(32) NOT NULL primary key); +INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); +CREATE TABLE t2 (id char(32) NOT NULL primary key); +INSERT INTO t2 VALUES (0), (1), (2), (3); + +let $query= SELECT t1.id + FROM t1 INNER JOIN t2 ON t1.id=t2.id + ORDER BY t2.id LIMIT 2; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +DROP TABLE t1,t2; + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT seq-1, seq-1 from seq_1_to_10; +CREATE TABLE t2 as SELECT * from t1; + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +let $query= SELECT * from t2,t1 left join t3 on t3.a=t1.b + order by t2.a desc,t1.a desc limit 5; + +--echo # +--echo # Outer Join +--echo # + +--echo # sort-nest(t2,t1) and t3 outside the nest +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # +--echo # no sort-nest as all the inner tables of the outer join will be +--echo # inside the nest, this should use temporary table to sort after the +--echo # entire join is computed +--echo # + +let $query= SELECT * FROM t2 LEFT JOIN (t1 LEFT JOIN t3 ON t3.a=t1.b) + ON t2.b=t1.a + ORDER BY t2.a DESC,t1.a DESC LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; +DROP TABLE t1,t2,t3; + +--echo # +--echo # Sort-nest with prepared statements +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT seq-1,seq-1 from seq_1_to_10; +CREATE TABLE t2 as SELECT * from t1; + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 from seq_1_to_1000; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +--echo # sort-nest on table t1,t2 +prepare ps1 from "EXPLAIN SELECT * FROM t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC,t1.a DESC + LIMIT 5"; + +EXECUTE ps1; +EXECUTE ps1; + +--echo # sort-nest on table t1,t2 +PREPARE ps2 from "EXPLAIN FORMAT=JSON + SELECT * from t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC, t1.a DESC + LIMIT 5"; +EXECUTE ps2; +EXECUTE ps2; + +--echo # sort-nest on table t1,t2 +PREPARE ps3 from "SELECT * from t2,t1,t3 + WHERE t3.a=t1.b+1 + ORDER BY t2.a DESC, t1.a DESC + LIMIT 5"; +EXECUTE ps3; +EXECUTE ps3; + +DEALLOCATE PREPARE ps1; +DEALLOCATE PREPARE ps2; +DEALLOCATE PREPARE ps3; +DROP TABLE t1,t2,t3; + +--echo # INDEPENDENT SUBQUERIES + +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a,a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3 (a int, b int, c int); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_1000; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +--echo # +--echo # sort-nest(t2,t1) and independent subquery in the SELECT list +--echo # + +let $query= SELECT (SELECT A.a FROM t3 A WHERE A.a > 5 limit 1) as x, + t2.b, t1.b, t3.a + FROM t1,t2,t3 + WHERE t1.a = t2.a + ORDER BY t2.b DESC, t1.b DESC + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +DROP TABLE t0,t1,t2,t3; + +--echo # +--echo # Const table should not form the sort-nest +--echo # + +CREATE TABLE t1 (i1 integer NOT NULL PRIMARY KEY); +CREATE TABLE t2 (i2 integer NOT NULL PRIMARY KEY); +CREATE TABLE t3 (i3 integer); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), + (9), (10), (11), (12); + +INSERT INTO t2 SELECT * FROM t1; + +let $query= SELECT t1.*, t2.* + FROM t1 JOIN t2 ON t1.i1 = t2.i2 + LEFT JOIN t3 ON t2.i2 = t3.i3 + ORDER BY t1.i1 + LIMIT 5; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1,t2,t3; + +--echo # All tables are const tables + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (0,0); +CREATE TABLE t2(a int, b int); +INSERT INTO t2 VALUES (0,0); +CREATE TABLE t3(a int, b int); +INSERT INTO t3 VALUES (0,0); + +let $query= SELECT t1.a,t2.a,t3.a + FROM t1,t2,t3 + WHERE t1.b = t2.b AND t3.b=t1.b + ORDER BY t2.a DESC,t1.a DESC + LIMIT 5; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Tests where Index(scan, ref or range access) satisfies the ORDERING +--echo # + +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); + +--echo # index key a_b, no need for filesort + +let $query= SELECT a,b,c FROM t1 + WHERE a=1 and c=2 + ORDER BY b + LIMIT 10; + +eval EXPLAIN $query; +eval $query; +DROP TABLE t1; + +--echo # +--echo # Testing ORDER BY LIMIT with OFFSET, should show the same plan and same +--echo # estimate of rows for the sort-nest +--echo # + +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 as SELECT * from t1; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_100; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + +let $query= SELECT t1.a, t2.b, t1.b, t3.a + FROM t1,t2,t3 + WHERE t1.a=t2.a AND t3.a = t2.a + ORDER BY t2.b DESC, t1.b DESC + LIMIT 10; +eval EXPLAIN $query; +eval $query; + +let $query= SELECT t1.a, t2.b, t1.b, t3.a + FROM t1,t2,t3 + WHERE t1.a=t2.a AND t3.a=t2.a + ORDER BY t2.b DESC, t1.b DESC + LIMIT 5 OFFSET 5; + +eval EXPLAIN $query; +eval $query; + +drop table t0,t1,t2,t3; + + +--echo # +--echo # Constant removed from ORDER BY , so no need of sorting +--echo # + +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0; +CREATE TABLE t2 as SELECT * from t1; + +let $query= SELECT * + FROM t1,t2 + WHERE t1.a=t2.a and t1.b= 4 + ORDER BY t1.b DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval $query; + +drop table t0,t1,t2; + +--echo # +--echo # ORDER BY clause containing expressions +--echo # + +CREATE TABLE t0 (a int); +INSERT INTO t0 SELECT seq-1 from seq_1_to_10; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t2 as SELECT * from t1 where a < 5; +CREATE TABLE t3(a int, b int, c int, key(a)); +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_10; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +--enable_result_log + + +let $query= SELECT t1.a, t2.b, t1.b, t1.b + t2.b + FROM t1,t2, t3 + WHERE t1.a=t2.b and t2.a=t3.a + ORDER BY abs(t3.a+t1.b) DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +--echo # +--echo # No sort nest where ORDER BY item are expensive to compute like +--echo # stored functions, subqueries etc + +delimiter |; + +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN + RETURN a; +END| + +delimiter ;| + +let $query= SELECT t1.a, t2.b, t1.b, t1.b + t2.b + FROM t1,t2, t3 + WHERE t1.a=t2.b and t2.a=t3.a + ORDER BY f1(t3.a+t1.b) DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval EXPLAIN $query; +eval $query; + +drop function f1; + +--echo # +--echo # Window function in order by clause, sort-nest not allowed +--echo # + +let $query= SELECT t1.a, t2.b, t1.b, t1.b + t2.b + FROM t1,t2, t3 + WHERE t1.a=t2.b AND t2.a=t3.a + ORDER BY row_number() OVER (ORDER BY t1.a) DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +--echo # +--echo # Subqueries used in order by clause +--echo # + +let $query= SELECT t1.a, t2.b, t1.b, t1.b + t2.b + FROM t1,t2, t3 + WHERE t1.a=t2.b and t2.a=t3.a + ORDER BY (select A.a+t1.b from t1 A limit 1) + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +drop table t0,t1,t2,t3; diff --git a/mysql-test/main/sort_nest_dbt3.result b/mysql-test/main/sort_nest_dbt3.result new file mode 100644 index 00000000000..6143ede4d1b --- /dev/null +++ b/mysql-test/main/sort_nest_dbt3.result @@ -0,0 +1,610 @@ +create database dbt3; +use dbt3; +Table Op Msg_type Msg_text +dbt3.customer analyze status Engine-independent statistics collected +dbt3.customer analyze status OK +Table Op Msg_type Msg_text +dbt3.orders analyze status Engine-independent statistics collected +dbt3.orders analyze status OK +Table Op Msg_type Msg_text +dbt3.lineitem analyze status Engine-independent statistics collected +dbt3.lineitem analyze status OK +Table Op Msg_type Msg_text +dbt3.nation analyze status Engine-independent statistics collected +dbt3.nation analyze status OK +# done to avoid filter for now +set optimizer_switch='rowid_filter=off'; +set use_sort_nest=1; +# +# USING INDEXES FOR ORDERING +# +# +# Using index scan on first table +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders index i_o_custkey i_o_orderdate 4 NULL 5 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +c_nationkey c_name o_orderDate o_totalprice c_acctbal +16 Customer#000000088 1998-08-02 131752.07 8031.44 +0 Customer#000000080 1998-07-30 141858.97 7383.53 +10 Customer#000000049 1998-07-29 37776.79 4573.94 +3 Customer#000000022 1998-07-28 139104.17 591.98 +3 Customer#000000022 1998-07-27 82746.74 591.98 +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders index i_o_custkey i_o_orderdate 4 NULL 5 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +c_nationkey c_name o_orderDate o_totalprice c_acctbal +16 Customer#000000088 1998-08-02 131752.07 8031.44 +0 Customer#000000080 1998-07-30 141858.97 7383.53 +10 Customer#000000049 1998-07-29 37776.79 4573.94 +3 Customer#000000022 1998-07-28 139104.17 591.98 +3 Customer#000000022 1998-07-27 82746.74 591.98 +# +# Using range access for customer with index on (c), this does +# the ordering +# +alter table orders add key o_orderdate(o_orderDATE, o_custkey); +set use_sort_nest=1; +explain SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey,o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +o_custkey c_name o_orderDate o_totalprice c_acctbal +130 Customer#000000130 1997-07-30 67979.49 5073.58 +101 Customer#000000101 1997-07-31 79189.58 7470.96 +13 Customer#000000013 1997-07-31 125188.72 3857.34 +64 Customer#000000064 1997-08-03 76164.41 -646.64 +50 Customer#000000050 1997-08-03 20791.5 4266.13 +set use_sort_nest=0; +explain SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey,o_orderdate i_o_orderdate 4 NULL 242 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +o_custkey c_name o_orderDate o_totalprice c_acctbal +130 Customer#000000130 1997-07-30 67979.49 5073.58 +101 Customer#000000101 1997-07-31 79189.58 7470.96 +13 Customer#000000013 1997-07-31 125188.72 3857.34 +64 Customer#000000064 1997-08-03 76164.41 -646.64 +50 Customer#000000050 1997-08-03 20791.5 4266.13 +alter table orders drop key o_orderDate; +# +# USING FILESORT +# +# +# Filesort on first table (orders) +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 197 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +5 Customer#000000010 1997-04-04 258779.02 2753.54 ETHIOPIA +0 Customer#000000076 1997-08-24 231831.35 5745.33 ALGERIA +9 Customer#000000094 1997-10-17 224382.57 5500.11 INDONESIA +6 Customer#000000046 1997-05-26 216826.73 5744.59 FRANCE +3 Customer#000000005 1997-04-23 210643.96 794.47 CANADA +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +3 Customer#000000005 1997-11-09 190142.17 794.47 CANADA +9 Customer#000000094 1997-11-30 187516.29 5500.11 INDONESIA +3 Customer#000000005 1997-11-12 185496.66 794.47 CANADA +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 197 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +5 Customer#000000010 1997-04-04 258779.02 2753.54 ETHIOPIA +0 Customer#000000076 1997-08-24 231831.35 5745.33 ALGERIA +9 Customer#000000094 1997-10-17 224382.57 5500.11 INDONESIA +6 Customer#000000046 1997-05-26 216826.73 5744.59 FRANCE +3 Customer#000000005 1997-04-23 210643.96 794.47 CANADA +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +3 Customer#000000005 1997-11-09 190142.17 794.47 CANADA +9 Customer#000000094 1997-11-30 187516.29 5500.11 INDONESIA +3 Customer#000000005 1997-11-12 185496.66 794.47 CANADA +# +# Filesort on first table (lineitem) +# +show create table orders; +Table Create Table +orders CREATE TABLE `orders` ( + `o_orderkey` int(11) NOT NULL, + `o_custkey` int(11) DEFAULT NULL, + `o_orderstatus` char(1) DEFAULT NULL, + `o_totalprice` double DEFAULT NULL, + `o_orderDATE` date DEFAULT NULL, + `o_orderpriority` char(15) DEFAULT NULL, + `o_clerk` char(15) DEFAULT NULL, + `o_shippriority` int(11) DEFAULT NULL, + `o_comment` varchar(79) DEFAULT NULL, + PRIMARY KEY (`o_orderkey`), + KEY `i_o_orderdate` (`o_orderDATE`), + KEY `i_o_custkey` (`o_custkey`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +set use_sort_nest=1; +explain SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 812 Using index condition; Using filesort +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 +SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +l_extendedprice o_orderkey o_totalprice l_shipDATE +54559.5 1574 179923.54 1996-12-14 +53508.5 2342 104038.78 1996-08-28 +53458 1153 220727.97 1996-06-27 +53075.82 2721 59180.25 1996-02-14 +52830.33 1284 106122.38 1996-04-11 +52657.5 2276 141159.63 1996-07-13 +52290.84 4773 196080.26 1996-01-26 +51752.16 1607 166335.03 1996-02-22 +51751.35 1700 89143.36 1996-09-26 +51709.4 1254 94649.25 1996-03-07 +set use_sort_nest=0; +explain SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 812 Using index condition; Using filesort +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 +SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +l_extendedprice o_orderkey o_totalprice l_shipDATE +54559.5 1574 179923.54 1996-12-14 +53508.5 2342 104038.78 1996-08-28 +53458 1153 220727.97 1996-06-27 +53075.82 2721 59180.25 1996-02-14 +52830.33 1284 106122.38 1996-04-11 +52657.5 2276 141159.63 1996-07-13 +52290.84 4773 196080.26 1996-01-26 +51752.16 1607 166335.03 1996-02-22 +51751.35 1700 89143.36 1996-09-26 +51709.4 1254 94649.25 1996-03-07 +# +# Using Filesort with Sort Nest +# +# +# FILESORT USING SORT-NEST on (orders, customer) +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 20 Using index condition; Using where +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 sort-nest.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +2 Customer#000000101 1997-07-31 79189.58 7470.96 BRAZIL +3 Customer#000000013 1997-07-31 125188.72 3857.34 CANADA +9 Customer#000000130 1997-07-30 67979.49 5073.58 INDONESIA +11 Customer#000000148 1997-07-29 88448.24 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +11 Customer#000000052 1997-07-28 46393.97 5630.28 IRAQ +4 Customer#000000004 1997-07-25 11405.4 2866.83 EGYPT +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +18 Customer#000000082 1997-07-20 15082.82 9468.34 CHINA +10 Customer#000000055 1997-07-16 46380.69 4572.11 IRAN +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 20 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +2 Customer#000000101 1997-07-31 79189.58 7470.96 BRAZIL +3 Customer#000000013 1997-07-31 125188.72 3857.34 CANADA +9 Customer#000000130 1997-07-30 67979.49 5073.58 INDONESIA +11 Customer#000000148 1997-07-29 88448.24 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +11 Customer#000000052 1997-07-28 46393.97 5630.28 IRAQ +4 Customer#000000004 1997-07-25 11405.4 2866.83 EGYPT +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +18 Customer#000000082 1997-07-20 15082.82 9468.34 CHINA +10 Customer#000000055 1997-07-16 46380.69 4572.11 IRAN +# +# Sort-nest on table (lineitem, customer) +# +set use_sort_nest=1; +explain SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 NULL 330 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 dbt3.lineitem.l_orderkey 1 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +c_acctbal c_name o_orderDate l_extendedprice l_orderkey +1842.49 Customer#000000124 1997-11-06 54759.5 5857 +2135.6 Customer#000000148 1997-04-14 53909.8 5952 +4681.03 Customer#000000016 1998-07-06 53811.31 5761 +794.47 Customer#000000005 1997-04-23 53758.5 5859 +-234.12 Customer#000000125 1997-01-11 53468.31 5829 +5121.28 Customer#000000079 1998-05-31 53208 5633 +5744.59 Customer#000000046 1998-04-14 50770.37 5604 +-917.75 Customer#000000037 1997-07-13 50310.72 5793 +121.65 Customer#000000002 1998-05-28 49830.24 5507 +121.65 Customer#000000002 1998-05-28 49542.24 5507 +7470.96 Customer#000000101 1997-05-27 49411.82 5923 +5327.38 Customer#000000095 1997-10-04 48859.36 5505 +1842.49 Customer#000000124 1997-11-06 48661.41 5857 +4573.94 Customer#000000049 1997-02-14 48557.11 5762 +-646.64 Customer#000000064 1997-01-01 48219.92 5895 +-646.64 Customer#000000064 1997-01-01 48039.64 5895 +5121.28 Customer#000000079 1998-05-31 48004.8 5633 +9904.28 Customer#000000043 1998-02-06 47339.52 5671 +8959.65 Customer#000000149 1996-11-12 47247.52 5606 +5744.59 Customer#000000046 1998-04-14 45589.72 5604 +set use_sort_nest=0; +explain SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 NULL 330 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 dbt3.lineitem.l_orderkey 1 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +c_acctbal c_name o_orderDate l_extendedprice l_orderkey +1842.49 Customer#000000124 1997-11-06 54759.5 5857 +2135.6 Customer#000000148 1997-04-14 53909.8 5952 +4681.03 Customer#000000016 1998-07-06 53811.31 5761 +794.47 Customer#000000005 1997-04-23 53758.5 5859 +-234.12 Customer#000000125 1997-01-11 53468.31 5829 +5121.28 Customer#000000079 1998-05-31 53208 5633 +5744.59 Customer#000000046 1998-04-14 50770.37 5604 +-917.75 Customer#000000037 1997-07-13 50310.72 5793 +121.65 Customer#000000002 1998-05-28 49830.24 5507 +121.65 Customer#000000002 1998-05-28 49542.24 5507 +7470.96 Customer#000000101 1997-05-27 49411.82 5923 +5327.38 Customer#000000095 1997-10-04 48859.36 5505 +1842.49 Customer#000000124 1997-11-06 48661.41 5857 +4573.94 Customer#000000049 1997-02-14 48557.11 5762 +-646.64 Customer#000000064 1997-01-01 48219.92 5895 +-646.64 Customer#000000064 1997-01-01 48039.64 5895 +5121.28 Customer#000000079 1998-05-31 48004.8 5633 +9904.28 Customer#000000043 1998-02-06 47339.52 5671 +8959.65 Customer#000000149 1996-11-12 47247.52 5606 +5744.59 Customer#000000046 1998-04-14 45589.72 5604 +######################################################################## +# +# Sort-nest on table (customer, orders, lineitem) +# +set use_sort_nest=1; +explain SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE customer range PRIMARY,i_c_nationkey PRIMARY 4 NULL 7 Using index condition +1 SIMPLE orders ref PRIMARY,i_o_custkey i_o_custkey 5 dbt3.customer.c_custkey 15 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 20 Using filesort +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 sort-nest.c_nationkey 1 Using index +SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +l_extendedprice c_acctbal o_totalprice o_orderDate l_orderkey +53758.5 794.47 210643.96 1997-04-23 5859 +49830.24 121.65 140363.7 1998-05-28 5507 +49542.24 121.65 140363.7 1998-05-28 5507 +48745.11 6819.74 122823.78 1993-04-05 5794 +47992.64 6819.74 140838.11 1998-06-26 5763 +47615.98 6819.74 182966.39 1994-07-17 5572 +46705.74 9561.95 101429.61 1993-04-21 5670 +44467.59 121.65 44777.63 1992-07-08 5893 +44442.3 6819.74 122823.78 1993-04-05 5794 +39723.6 794.47 210643.96 1997-04-23 5859 +37048.32 9561.95 95312.81 1992-03-28 5953 +36860.25 794.47 210643.96 1997-04-23 5859 +32996.16 6819.74 140838.11 1998-06-26 5763 +31416.68 6819.74 182966.39 1994-07-17 5572 +31219.32 794.47 210643.96 1997-04-23 5859 +31042.34 9561.95 95312.81 1992-03-28 5953 +29462.13 794.47 210643.96 1997-04-23 5859 +28948.59 6819.74 182966.39 1994-07-17 5572 +26732.43 9561.95 101429.61 1993-04-21 5670 +24590.68 9561.95 95312.81 1992-03-28 5953 +set use_sort_nest=0; +explain SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE customer range PRIMARY,i_c_nationkey PRIMARY 4 NULL 7 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 Using index +1 SIMPLE orders ref PRIMARY,i_o_custkey i_o_custkey 5 dbt3.customer.c_custkey 15 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 +SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +l_extendedprice c_acctbal o_totalprice o_orderDate l_orderkey +53758.5 794.47 210643.96 1997-04-23 5859 +49830.24 121.65 140363.7 1998-05-28 5507 +49542.24 121.65 140363.7 1998-05-28 5507 +48745.11 6819.74 122823.78 1993-04-05 5794 +47992.64 6819.74 140838.11 1998-06-26 5763 +47615.98 6819.74 182966.39 1994-07-17 5572 +46705.74 9561.95 101429.61 1993-04-21 5670 +44467.59 121.65 44777.63 1992-07-08 5893 +44442.3 6819.74 122823.78 1993-04-05 5794 +39723.6 794.47 210643.96 1997-04-23 5859 +37048.32 9561.95 95312.81 1992-03-28 5953 +36860.25 794.47 210643.96 1997-04-23 5859 +32996.16 6819.74 140838.11 1998-06-26 5763 +31416.68 6819.74 182966.39 1994-07-17 5572 +31219.32 794.47 210643.96 1997-04-23 5859 +31042.34 9561.95 95312.81 1992-03-28 5953 +29462.13 794.47 210643.96 1997-04-23 5859 +28948.59 6819.74 182966.39 1994-07-17 5572 +26732.43 9561.95 101429.61 1993-04-21 5670 +24590.68 9561.95 95312.81 1992-03-28 5953 +drop database dbt3; diff --git a/mysql-test/main/sort_nest_dbt3.test b/mysql-test/main/sort_nest_dbt3.test new file mode 100644 index 00000000000..503bed0733a --- /dev/null +++ b/mysql-test/main/sort_nest_dbt3.test @@ -0,0 +1,216 @@ +create database dbt3; +use dbt3; +--disable_query_log +--source include/dbt3_s001.inc + + +analyze table customer persistent for all; +analyze table orders persistent for all; +analyze table lineitem persistent for all; +analyze table nation persistent for all; +--enable_query_log + +--echo # done to avoid filter for now +set optimizer_switch='rowid_filter=off'; +set use_sort_nest=1; + +--echo # +--echo # USING INDEXES FOR ORDERING +--echo # + + +--echo # +--echo # Using index scan on first table +--echo # + +let $query= SELECT + c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal + FROM + orders, customer + WHERE + c_custkey= o_custkey + ORDER BY o_orderDATE DESC + LIMIT 5; + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + +--echo # +--echo # Using range access for customer with index on (c), this does +--echo # the ordering +--echo # + +alter table orders add key o_orderdate(o_orderDATE, o_custkey); + +let $query= SELECT + o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal + FROM + orders, customer + WHERE + c_custkey= o_custkey AND + o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' + ORDER BY o_orderDATE, o_custkey DESC + LIMIT 5; + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + +alter table orders drop key o_orderDate; + +############################################################################### + +--echo # +--echo # USING FILESORT +--echo # + +--echo # +--echo # Filesort on first table (orders) +--echo # + +let $query= SELECT + c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name + FROM + orders, customer, nation + WHERE + c_custkey= o_custkey AND c_nationkey=n_nationkey AND + o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' + ORDER BY o_totalprice DESC + LIMIT 10; + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + + +--echo # +--echo # Filesort on first table (lineitem) +--echo # + +show create table orders; + +let $query= SELECT + l_extendedprice, o_orderkey, o_totalprice, l_shipDATE + FROM + orders, lineitem + WHERE + o_orderkey = l_orderkey AND + l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC + LIMIT 10; + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + +############################################################################### + + +--echo # +--echo # Using Filesort with Sort Nest +--echo # + +--echo # +--echo # FILESORT USING SORT-NEST on (orders, customer) +--echo # + +let $query= SELECT + c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name + FROM + orders, customer, nation + WHERE + c_custkey= o_custkey AND c_nationkey=n_nationkey AND + o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' + ORDER BY o_orderDATE DESC, c_acctbal DESC + LIMIT 10; + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + + + +--echo # +--echo # Sort-nest on table (lineitem, customer) +--echo # + +let $query= SELECT + c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey + FROM + orders, customer, lineitem + WHERE + c_custkey= o_custkey + AND + l_orderkey = o_orderkey + AND + l_orderkey between 5500 AND 6000 + AND + l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC + LIMIT 20; + + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + +--echo ######################################################################## + +--echo # +--echo # Sort-nest on table (customer, orders, lineitem) +--echo # + +let $query= SELECT + l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey + FROM + orders, customer, lineitem, nation + WHERE + c_custkey= o_custkey + AND + l_orderkey = o_orderkey + AND + n_nationkey = c_nationkey + AND + l_orderkey between 5500 AND 6000 + AND + c_custkey between 1 and 10 + ORDER BY + l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC + LIMIT 20; + + +set use_sort_nest=1; +eval explain $query; +eval $query; + +set use_sort_nest=0; +eval explain $query; +eval $query; + + +drop database dbt3; diff --git a/mysql-test/main/sort_nest_dbt3_innodb.result b/mysql-test/main/sort_nest_dbt3_innodb.result new file mode 100644 index 00000000000..9c4c8144225 --- /dev/null +++ b/mysql-test/main/sort_nest_dbt3_innodb.result @@ -0,0 +1,611 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +create database dbt3; +use dbt3; +Table Op Msg_type Msg_text +dbt3.customer analyze status Engine-independent statistics collected +dbt3.customer analyze status OK +Table Op Msg_type Msg_text +dbt3.orders analyze status Engine-independent statistics collected +dbt3.orders analyze status OK +Table Op Msg_type Msg_text +dbt3.lineitem analyze status Engine-independent statistics collected +dbt3.lineitem analyze status OK +Table Op Msg_type Msg_text +dbt3.nation analyze status Engine-independent statistics collected +dbt3.nation analyze status OK +# done to avoid filter for now +set optimizer_switch='rowid_filter=off'; +set use_sort_nest=1; +# +# USING INDEXES FOR ORDERING +# +# +# Using index scan on first table +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders index i_o_custkey i_o_orderdate 4 NULL 5 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +c_nationkey c_name o_orderDate o_totalprice c_acctbal +16 Customer#000000088 1998-08-02 131752.07 8031.44 +0 Customer#000000080 1998-07-30 141858.97 7383.53 +10 Customer#000000049 1998-07-29 37776.79 4573.94 +3 Customer#000000022 1998-07-28 139104.17 591.98 +3 Customer#000000022 1998-07-27 82746.74 591.98 +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders index i_o_custkey i_o_orderdate 4 NULL 5 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey +ORDER BY o_orderDATE DESC +LIMIT 5; +c_nationkey c_name o_orderDate o_totalprice c_acctbal +16 Customer#000000088 1998-08-02 131752.07 8031.44 +0 Customer#000000080 1998-07-30 141858.97 7383.53 +10 Customer#000000049 1998-07-29 37776.79 4573.94 +3 Customer#000000022 1998-07-28 139104.17 591.98 +3 Customer#000000022 1998-07-27 82746.74 591.98 +# +# Using range access for customer with index on (c), this does +# the ordering +# +alter table orders add key o_orderdate(o_orderDATE, o_custkey); +set use_sort_nest=1; +explain SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey,o_orderdate i_o_orderdate 4 NULL 217 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +o_custkey c_name o_orderDate o_totalprice c_acctbal +130 Customer#000000130 1997-07-30 67979.49 5073.58 +101 Customer#000000101 1997-07-31 79189.58 7470.96 +13 Customer#000000013 1997-07-31 125188.72 3857.34 +64 Customer#000000064 1997-08-03 76164.41 -646.64 +50 Customer#000000050 1997-08-03 20791.5 4266.13 +set use_sort_nest=0; +explain SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey,o_orderdate i_o_orderdate 4 NULL 217 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +o_custkey, c_name, o_orderDate, o_totalprice, c_acctbal +FROM +orders, customer +WHERE +c_custkey= o_custkey AND +o_orderDATE >='1997-07-30' and o_orderDATE <= '1998-07-30' +ORDER BY o_orderDATE, o_custkey DESC +LIMIT 5; +o_custkey c_name o_orderDate o_totalprice c_acctbal +130 Customer#000000130 1997-07-30 67979.49 5073.58 +101 Customer#000000101 1997-07-31 79189.58 7470.96 +13 Customer#000000013 1997-07-31 125188.72 3857.34 +64 Customer#000000064 1997-08-03 76164.41 -646.64 +50 Customer#000000050 1997-08-03 20791.5 4266.13 +alter table orders drop key o_orderDate; +# +# USING FILESORT +# +# +# Filesort on first table (orders) +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 201 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +5 Customer#000000010 1997-04-04 258779.02 2753.54 ETHIOPIA +0 Customer#000000076 1997-08-24 231831.35 5745.33 ALGERIA +9 Customer#000000094 1997-10-17 224382.57 5500.11 INDONESIA +6 Customer#000000046 1997-05-26 216826.73 5744.59 FRANCE +3 Customer#000000005 1997-04-23 210643.96 794.47 CANADA +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +3 Customer#000000005 1997-11-09 190142.17 794.47 CANADA +9 Customer#000000094 1997-11-30 187516.29 5500.11 INDONESIA +3 Customer#000000005 1997-11-12 185496.66 794.47 CANADA +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 201 Using index condition; Using where; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-01-30' and o_orderDATE <= '1997-12-31' +ORDER BY o_totalprice DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +5 Customer#000000010 1997-04-04 258779.02 2753.54 ETHIOPIA +0 Customer#000000076 1997-08-24 231831.35 5745.33 ALGERIA +9 Customer#000000094 1997-10-17 224382.57 5500.11 INDONESIA +6 Customer#000000046 1997-05-26 216826.73 5744.59 FRANCE +3 Customer#000000005 1997-04-23 210643.96 794.47 CANADA +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +3 Customer#000000005 1997-11-09 190142.17 794.47 CANADA +9 Customer#000000094 1997-11-30 187516.29 5500.11 INDONESIA +3 Customer#000000005 1997-11-12 185496.66 794.47 CANADA +# +# Filesort on first table (lineitem) +# +show create table orders; +Table Create Table +orders CREATE TABLE `orders` ( + `o_orderkey` int(11) NOT NULL, + `o_custkey` int(11) DEFAULT NULL, + `o_orderstatus` char(1) DEFAULT NULL, + `o_totalprice` double DEFAULT NULL, + `o_orderDATE` date DEFAULT NULL, + `o_orderpriority` char(15) DEFAULT NULL, + `o_clerk` char(15) DEFAULT NULL, + `o_shippriority` int(11) DEFAULT NULL, + `o_comment` varchar(79) DEFAULT NULL, + PRIMARY KEY (`o_orderkey`), + KEY `i_o_orderdate` (`o_orderDATE`), + KEY `i_o_custkey` (`o_custkey`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +set use_sort_nest=1; +explain SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 910 Using index condition; Using filesort +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 +SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +l_extendedprice o_orderkey o_totalprice l_shipDATE +54559.5 1574 179923.54 1996-12-14 +53508.5 2342 104038.78 1996-08-28 +53458 1153 220727.97 1996-06-27 +53075.82 2721 59180.25 1996-02-14 +52830.33 1284 106122.38 1996-04-11 +52657.5 2276 141159.63 1996-07-13 +52290.84 4773 196080.26 1996-01-26 +51752.16 1607 166335.03 1996-02-22 +51751.35 1700 89143.36 1996-09-26 +51709.4 1254 94649.25 1996-03-07 +set use_sort_nest=0; +explain SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 910 Using index condition; Using filesort +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 +SELECT +l_extendedprice, o_orderkey, o_totalprice, l_shipDATE +FROM +orders, lineitem +WHERE +o_orderkey = l_orderkey AND +l_shipDATE >= '1996-01-01' AND l_shipDATE <= '1996-12-31' + ORDER BY l_extendedprice DESC +LIMIT 10; +l_extendedprice o_orderkey o_totalprice l_shipDATE +54559.5 1574 179923.54 1996-12-14 +53508.5 2342 104038.78 1996-08-28 +53458 1153 220727.97 1996-06-27 +53075.82 2721 59180.25 1996-02-14 +52830.33 1284 106122.38 1996-04-11 +52657.5 2276 141159.63 1996-07-13 +52290.84 4773 196080.26 1996-01-26 +51752.16 1607 166335.03 1996-02-22 +51751.35 1700 89143.36 1996-09-26 +51709.4 1254 94649.25 1996-03-07 +# +# Using Filesort with Sort Nest +# +# +# FILESORT USING SORT-NEST on (orders, customer) +# +set use_sort_nest=1; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 20 Using index condition; Using where +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 10 Using filesort +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 sort-nest.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +2 Customer#000000101 1997-07-31 79189.58 7470.96 BRAZIL +3 Customer#000000013 1997-07-31 125188.72 3857.34 CANADA +9 Customer#000000130 1997-07-30 67979.49 5073.58 INDONESIA +11 Customer#000000148 1997-07-29 88448.24 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +11 Customer#000000052 1997-07-28 46393.97 5630.28 IRAQ +4 Customer#000000004 1997-07-25 11405.4 2866.83 EGYPT +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +18 Customer#000000082 1997-07-20 15082.82 9468.34 CHINA +10 Customer#000000055 1997-07-16 46380.69 4572.11 IRAN +set use_sort_nest=0; +explain SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 20 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 +SELECT +c_nationkey, c_name, o_orderDate, o_totalprice, c_acctbal, n_name +FROM +orders, customer, nation +WHERE +c_custkey= o_custkey AND c_nationkey=n_nationkey AND +o_orderDATE >='1997-07-01' and o_orderDATE <= '1997-07-31' +ORDER BY o_orderDATE DESC, c_acctbal DESC +LIMIT 10; +c_nationkey c_name o_orderDate o_totalprice c_acctbal n_name +2 Customer#000000101 1997-07-31 79189.58 7470.96 BRAZIL +3 Customer#000000013 1997-07-31 125188.72 3857.34 CANADA +9 Customer#000000130 1997-07-30 67979.49 5073.58 INDONESIA +11 Customer#000000148 1997-07-29 88448.24 2135.6 IRAQ +2 Customer#000000101 1997-07-28 204163.1 7470.96 BRAZIL +11 Customer#000000052 1997-07-28 46393.97 5630.28 IRAQ +4 Customer#000000004 1997-07-25 11405.4 2866.83 EGYPT +11 Customer#000000148 1997-07-25 206179.68 2135.6 IRAQ +18 Customer#000000082 1997-07-20 15082.82 9468.34 CHINA +10 Customer#000000055 1997-07-16 46380.69 4572.11 IRAN +# +# Sort-nest on table (lineitem, customer) +# +set use_sort_nest=1; +explain SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_custkey PRIMARY 4 NULL 125 Using where; Using temporary; Using filesort +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 Using where +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +c_acctbal c_name o_orderDate l_extendedprice l_orderkey +1842.49 Customer#000000124 1997-11-06 54759.5 5857 +2135.6 Customer#000000148 1997-04-14 53909.8 5952 +4681.03 Customer#000000016 1998-07-06 53811.31 5761 +794.47 Customer#000000005 1997-04-23 53758.5 5859 +-234.12 Customer#000000125 1997-01-11 53468.31 5829 +5121.28 Customer#000000079 1998-05-31 53208 5633 +5744.59 Customer#000000046 1998-04-14 50770.37 5604 +-917.75 Customer#000000037 1997-07-13 50310.72 5793 +121.65 Customer#000000002 1998-05-28 49830.24 5507 +121.65 Customer#000000002 1998-05-28 49542.24 5507 +7470.96 Customer#000000101 1997-05-27 49411.82 5923 +5327.38 Customer#000000095 1997-10-04 48859.36 5505 +1842.49 Customer#000000124 1997-11-06 48661.41 5857 +4573.94 Customer#000000049 1997-02-14 48557.11 5762 +-646.64 Customer#000000064 1997-01-01 48219.92 5895 +-646.64 Customer#000000064 1997-01-01 48039.64 5895 +5121.28 Customer#000000079 1998-05-31 48004.8 5633 +9904.28 Customer#000000043 1998-02-06 47339.52 5671 +8959.65 Customer#000000149 1996-11-12 47247.52 5606 +5744.59 Customer#000000046 1998-04-14 45589.72 5604 +set use_sort_nest=0; +explain SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_custkey PRIMARY 4 NULL 125 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3.orders.o_custkey 1 +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 Using where +SELECT +c_acctbal, c_name, o_orderDate, l_extendedprice, l_orderkey +FROM +orders, customer, lineitem +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +l_orderkey between 5500 AND 6000 +AND +l_shipDATE >= '1997-01-01' and l_shipDATE <= '1998-08-10' + ORDER BY l_extendedprice DESC, c_acctbal DESC +LIMIT 20; +c_acctbal c_name o_orderDate l_extendedprice l_orderkey +1842.49 Customer#000000124 1997-11-06 54759.5 5857 +2135.6 Customer#000000148 1997-04-14 53909.8 5952 +4681.03 Customer#000000016 1998-07-06 53811.31 5761 +794.47 Customer#000000005 1997-04-23 53758.5 5859 +-234.12 Customer#000000125 1997-01-11 53468.31 5829 +5121.28 Customer#000000079 1998-05-31 53208 5633 +5744.59 Customer#000000046 1998-04-14 50770.37 5604 +-917.75 Customer#000000037 1997-07-13 50310.72 5793 +121.65 Customer#000000002 1998-05-28 49830.24 5507 +121.65 Customer#000000002 1998-05-28 49542.24 5507 +7470.96 Customer#000000101 1997-05-27 49411.82 5923 +5327.38 Customer#000000095 1997-10-04 48859.36 5505 +1842.49 Customer#000000124 1997-11-06 48661.41 5857 +4573.94 Customer#000000049 1997-02-14 48557.11 5762 +-646.64 Customer#000000064 1997-01-01 48219.92 5895 +-646.64 Customer#000000064 1997-01-01 48039.64 5895 +5121.28 Customer#000000079 1998-05-31 48004.8 5633 +9904.28 Customer#000000043 1998-02-06 47339.52 5671 +8959.65 Customer#000000149 1996-11-12 47247.52 5606 +5744.59 Customer#000000046 1998-04-14 45589.72 5604 +######################################################################## +# +# Sort-nest on table (customer, orders, lineitem) +# +set use_sort_nest=1; +explain SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_custkey PRIMARY 4 NULL 125 Using where +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 +1 SIMPLE lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 +1 SIMPLE <sort-nest> ALL NULL NULL NULL NULL 20 Using filesort +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 sort-nest.c_nationkey 1 Using index +SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +l_extendedprice c_acctbal o_totalprice o_orderDate l_orderkey +53758.5 794.47 210643.96 1997-04-23 5859 +49830.24 121.65 140363.7 1998-05-28 5507 +49542.24 121.65 140363.7 1998-05-28 5507 +48745.11 6819.74 122823.78 1993-04-05 5794 +47992.64 6819.74 140838.11 1998-06-26 5763 +47615.98 6819.74 182966.39 1994-07-17 5572 +46705.74 9561.95 101429.61 1993-04-21 5670 +44467.59 121.65 44777.63 1992-07-08 5893 +44442.3 6819.74 122823.78 1993-04-05 5794 +39723.6 794.47 210643.96 1997-04-23 5859 +37048.32 9561.95 95312.81 1992-03-28 5953 +36860.25 794.47 210643.96 1997-04-23 5859 +32996.16 6819.74 140838.11 1998-06-26 5763 +31416.68 6819.74 182966.39 1994-07-17 5572 +31219.32 794.47 210643.96 1997-04-23 5859 +31042.34 9561.95 95312.81 1992-03-28 5953 +29462.13 794.47 210643.96 1997-04-23 5859 +28948.59 6819.74 182966.39 1994-07-17 5572 +26732.43 9561.95 101429.61 1993-04-21 5670 +24590.68 9561.95 95312.81 1992-03-28 5953 +set use_sort_nest=0; +explain SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_custkey PRIMARY 4 NULL 125 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 Using index +1 SIMPLE lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3.orders.o_orderkey 4 +SELECT +l_extendedprice, c_acctbal, o_totalprice, o_orderDate, l_orderkey +FROM +orders, customer, lineitem, nation +WHERE +c_custkey= o_custkey +AND +l_orderkey = o_orderkey +AND +n_nationkey = c_nationkey +AND +l_orderkey between 5500 AND 6000 +AND +c_custkey between 1 and 10 +ORDER BY +l_extendedprice DESC, c_acctbal DESC, o_totalprice DESC +LIMIT 20; +l_extendedprice c_acctbal o_totalprice o_orderDate l_orderkey +53758.5 794.47 210643.96 1997-04-23 5859 +49830.24 121.65 140363.7 1998-05-28 5507 +49542.24 121.65 140363.7 1998-05-28 5507 +48745.11 6819.74 122823.78 1993-04-05 5794 +47992.64 6819.74 140838.11 1998-06-26 5763 +47615.98 6819.74 182966.39 1994-07-17 5572 +46705.74 9561.95 101429.61 1993-04-21 5670 +44467.59 121.65 44777.63 1992-07-08 5893 +44442.3 6819.74 122823.78 1993-04-05 5794 +39723.6 794.47 210643.96 1997-04-23 5859 +37048.32 9561.95 95312.81 1992-03-28 5953 +36860.25 794.47 210643.96 1997-04-23 5859 +32996.16 6819.74 140838.11 1998-06-26 5763 +31416.68 6819.74 182966.39 1994-07-17 5572 +31219.32 794.47 210643.96 1997-04-23 5859 +31042.34 9561.95 95312.81 1992-03-28 5953 +29462.13 794.47 210643.96 1997-04-23 5859 +28948.59 6819.74 182966.39 1994-07-17 5572 +26732.43 9561.95 101429.61 1993-04-21 5670 +24590.68 9561.95 95312.81 1992-03-28 5953 +drop database dbt3; diff --git a/mysql-test/main/sort_nest_dbt3_innodb.test b/mysql-test/main/sort_nest_dbt3_innodb.test new file mode 100644 index 00000000000..d72329eaab6 --- /dev/null +++ b/mysql-test/main/sort_nest_dbt3_innodb.test @@ -0,0 +1,6 @@ +--source include/have_innodb.inc + +SET SESSION STORAGE_ENGINE='InnoDB'; + +--source sort_nest_dbt3.test + 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; diff --git a/mysql-test/main/sort_nest_index.test b/mysql-test/main/sort_nest_index.test new file mode 100644 index 00000000000..08856fc1723 --- /dev/null +++ b/mysql-test/main/sort_nest_index.test @@ -0,0 +1,249 @@ +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); + +--echo # +--echo # index a_b should be used, no need for filesort +--echo # + +let $query= select a,b,c from t1 where a=1 and c=2 order by b limit 5; +set use_sort_nest= 1; +eval $query; +eval explain $query; + +set use_sort_nest= 0; +eval explain $query; +drop table t1; + +--echo # +--echo # Tests where Index(scan, ref or range access) satisfies the ORDERING +--echo # + +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); + +--echo # index key a_b, no need for filesort + +let $query= select a,b,c from t1 where a=1 and c=2 order by b limit 10; +set optimizer_trace=1; + +set use_sort_nest=1; +eval $query; +eval explain $query; + +set use_sort_nest=0; +eval explain $query; + +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'); + +--echo # +--echo # Should use index condition +--echo # + +let $query= SELECT * FROM t1 + WHERE a BETWEEN 1 and 2 + ORDER BY a + LIMIT 2; + +set use_sort_nest= 1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest= 0; +eval EXPLAIN $query; + +--echo # +--echo # Should not use index condition as ORDER by DESC is used +--echo # + +let $query= SELECT * FROM t1 + WHERE a BETWEEN 1 and 2 + ORDER BY a DESC + LIMIT 2; + +set use_sort_nest= 1; +eval EXPLAIN $query; +eval $query; + +set use_sort_nest= 0; +eval EXPLAIN $query; + +drop table t1; + +create table t1(a int, b int, c int, key(a), key a_b(a,b)); # 10 rows +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)); # 10 rows +insert into t2 select a, b, c from t1; + +--echo # +--echo # Testing using of Indexes on first non-const table +--echo # + +--echo # +--echo # Using range scan +--echo # +let $query= SELECT * + FROM + t1,t2 + WHERE + t1.a=2 AND t2.b > 8 AND + t1.b=t2.b + ORDER BY t1.b LIMIT 10; + +set use_sort_nest= 1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest= 0; +eval EXPLAIN $query; + +--echo # +--echo # Using ref access +--echo # +let $query= SELECT * + FROM + t1,t2 + WHERE + t1.a=2 AND t2.c >= 1 AND + t1.b=t2.b + ORDER BY t1.b LIMIT 10; + +set use_sort_nest= 1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest= 0; +eval EXPLAIN $query; + +drop table t1,t2; + +--echo # 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; # 5 rows + +--disable_result_log +analyze table t1 persistent for all; +--enable_result_log + +--echo # +--echo # Index idx1 to be used for index scan +--echo # + +let $query= SELECT * from t1 where b > 0 order by t1.a limit 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Index idx2 to be used for index scan(USE INDEX is used) +--echo # + +let $query= SELECT * from t1 USE INDEX(idx2) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Index idx2 to be used for index scan(USE INDEX for ORDER BY is used) +--echo # + +let $query= SELECT * from t1 USE INDEX FOR ORDER BY(idx2) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Use Filesort as idx3 does not resolve ORDER BY clause +--echo # + +let $query= SELECT * from t1 USE INDEX FOR ORDER BY(idx3) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Using index idx2 as idx1 is ignored +--echo # + +let $query= SELECT * from t1 IGNORE INDEX(idx1) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Use index idx2 for sorting, it is forced here +--echo # + +let $query= SELECT * from t1 FORCE INDEX(idx2) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +--echo # +--echo # Use FILESORT as idx3 cannot resolve ORDER BY clause +--echo # + +let $query= SELECT * from t1 FORCE INDEX FOR ORDER BY(idx3) + WHERE b > 0 + ORDER BY t1.a LIMIT 2; + +set use_sort_nest=1; +eval $query; +eval EXPLAIN $query; + +set use_sort_nest=0; +eval EXPLAIN $query; + +drop table t0,t1; diff --git a/mysql-test/main/sort_nest_sj.result b/mysql-test/main/sort_nest_sj.result new file mode 100644 index 00000000000..73405cd8832 --- /dev/null +++ b/mysql-test/main/sort_nest_sj.result @@ -0,0 +1,697 @@ +# +# SORT-NEST WITH SEMI JOINS +# + +# MERGED SEMI-JOINS + +# SEMI JOIN MATERIALIZATION SCAN with SORT-NEST +CREATE TABLE t0(a int); +CREATE TABLE t1 (a int, b int, c int); +CREATE TABLE t2 (a int, b int, c int); +CREATE TABLE t3 (a int, b int, c int, key(a)); +CREATE TABLE t4 (a int, b int, c int, key(a)); +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t2 SELECT a,a,a FROM t0; +INSERT INTO t3 SELECT a,a,a FROM t0; +INSERT INTO t4 SELECT a,a,a FROM t0; +ANALYZE TABLE t0 PERSISTENT FOR ALL; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +# SJM scan inside the sort-nest +# sort-nest includes (t2, <subquery2>) +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t1.b DESC ,t2.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 MATERIALIZED t4 range a a 5 NULL 3 Using where; Using index +2 MATERIALIZED t3 ref a a 5 test.t4.a 1 +EXPLAIN FORMAT=JSON SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t1.b DESC ,t2.b DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + }, + "table": { + "table_name": "<subquery2>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "rows": 3, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t4", + "access_type": "range", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "rows": 3, + "filtered": 100, + "attached_condition": "t4.a < 3 and t4.a is not null", + "using_index": true + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t4.a"], + "rows": 1, + "filtered": 100 + } + } + } + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.b desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 100, + "filtered": 100, + "attached_condition": "t1.a = `sort-nest`.a and t1.b = `sort-nest`.b" + } + } +} +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t1.b DESC ,t2.b DESC +LIMIT 5; +a a b b +2 2 2 2 +1 1 1 1 +0 0 0 0 +set use_sort_nest=0; +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t1.b DESC ,t2.b DESC +LIMIT 5; +a a b b +2 2 2 2 +1 1 1 1 +0 0 0 0 +# +# SJM scan table is the first table inside the sort-nest +# +alter table t2 add key(b); +set use_sort_nest=1; +EXPLAIN SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND t2.b < 5 AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t2 ALL b NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where +2 MATERIALIZED t4 range a a 5 NULL 3 Using where; Using index +2 MATERIALIZED t3 ref a a 5 test.t4.a 1 +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND t2.b < 5 AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +a a b b +2 2 2 2 +1 1 1 1 +0 0 0 0 +set use_sort_nest= 0; +SELECT t1.a, t2.a, t1.b,t2.b +FROM t1, t2 +WHERE t1.a=t2.a AND t2.b < 5 AND +t1.b IN (SELECT t3.b FROM t3,t4 +WHERE t3.a < 3 AND t3.a=t4.a) +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5; +a a b b +2 2 2 2 +1 1 1 1 +0 0 0 0 +DROP TABLE t0, t1, t2, t3, t4; +# +# SJM Lookup with sort-nest, where SJM lookup table is outside the +# sort-nest +# +create table t1 (a int, b int, c int, key(a)); +create table t2 (a int, b int, c int, key(c)); +create table t3 (a int, b int, c int, key(a)); +create table t4 (a int, b int, c int); +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_10; +INSERT INTO t2 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_1000; +INSERT INTO t4 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +set use_sort_nest= 1; +EXPLAIN SELECT t1.a, t2.a, t2.b +FROM t1, t2 +WHERE t2.a in (SELECT t3.b from t3) +AND t1.a= t2.b +AND t1.a < 5 +ORDER BY t1.b DESC, t2.a DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL a NULL NULL NULL 10 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 1000 +EXPLAIN FORMAT=JSON SELECT t1.a, t2.a, t2.b +FROM t1, t2 +WHERE t2.a in (SELECT t3.b from t3) +AND t1.a= t2.b +AND t1.a < 5 +ORDER BY t1.b DESC, t2.a DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["a"], + "rows": 10, + "filtered": 50, + "attached_condition": "t1.a < 5" + }, + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 100, + "filtered": 5.4688 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t2.b = t1.a" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["b"], + "ref": ["func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 1000, + "filtered": 100 + } + } + } + } + } +} +SELECT t1.a, t2.a, t2.b +FROM t1, t2 +WHERE t2.a in (SELECT t3.b from t3) +AND t1.a= t2.b +AND t1.a < 5 +ORDER BY t1.b DESC, t2.a DESC +LIMIT 5; +a a b +4 4 4 +3 3 3 +2 2 2 +1 1 1 +0 0 0 +set use_sort_nest= 0; +SELECT t1.a, t2.a, t2.b +FROM t1, t2 +WHERE t2.a in (SELECT t3.b from t3) +AND t1.a= t2.b +AND t1.a < 5 +ORDER BY t1.b DESC, t2.a DESC +LIMIT 5; +a a b +4 4 4 +3 3 3 +2 2 2 +1 1 1 +0 0 0 +DROP TABLE t1, t2, t3, t4; +# +# Firstmatch strategy +# +set @save_optimizer_switch=@@optimizer_switch; +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 SELECT a,a,a from t0; +create table t2 as SELECT * from t1; +create table t3 as SELECT * from t1; +set use_sort_nest=1; +EXPLAIN SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t1 ref a a 5 test.t2.a 1 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(<sort-nest>) +EXPLAIN FORMAT=JSON SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100 + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.c desc, `sort-nest`.c desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t3.b = `sort-nest`.b and t3.c <= `sort-nest`.c", + "first_match": "<sort-nest>" + } + } +} +SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +a b c a b c +9 9 9 9 9 9 +8 8 8 8 8 8 +7 7 7 7 7 7 +6 6 6 6 6 6 +5 5 5 5 5 5 +set use_sort_nest=0; +SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +a b c a b c +9 9 9 9 9 9 +8 8 8 8 8 8 +7 7 7 7 7 7 +6 6 6 6 6 6 +5 5 5 5 5 5 +set optimizer_switch='firstmatch=off'; +# +# Duplicate Weedout strategy +# +set use_sort_nest=1; +EXPLAIN SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t1 ref a a 5 test.t2.a 1 +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "rows": 1, + "filtered": 100 + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.c desc, `sort-nest`.c desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + "duplicates_removal": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t3.b = `sort-nest`.b and t3.c <= `sort-nest`.c" + } + } + } +} +SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +a b c a b c +9 9 9 9 9 9 +8 8 8 8 8 8 +7 7 7 7 7 7 +6 6 6 6 6 6 +5 5 5 5 5 5 +set use_sort_nest=0; +SELECT * FROM t1, t2 +WHERE t1.a=t2.a AND +t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) +ORDER BY t2.c DESC, t1.c DESC +LIMIT 5; +a b c a b c +9 9 9 9 9 9 +8 8 8 8 8 8 +7 7 7 7 7 7 +6 6 6 6 6 6 +5 5 5 5 5 5 +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t0,t1,t2,t3; + +# NON-MERGED SEMI JOINS + +create table t0 (a int); +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +create table t1 (a int, b int); +insert into t1 SELECT a,a from t0 where a <5; +create table t2 as SELECT * from t1 where a < 5; +create table t3(a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 FROM seq_1_to_100; +<subquery2> outside the sort-nest +set use_sort_nest=1; +EXPLAIN SELECT * from t2,t1 +WHERE t2.b=t1.b +AND +t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b) +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 sort-nest.a 1 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 100 Using temporary +EXPLAIN FORMAT=JSON SELECT * from t2,t1 +WHERE t2.b=t1.b +AND +t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b) +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t1.b = t2.b" + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.a desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "4", + "used_key_parts": ["max(t3.a)"], + "ref": ["sort-nest.a"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 100, + "filtered": 100 + } + } + } + } + } + } +} +SELECT * from t2,t1 +WHERE t2.b=t1.b +AND +t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b) +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +a b a b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +set use_sort_nest=0; +SELECT * from t2,t1 +WHERE t2.b=t1.b +AND +t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b) +ORDER BY t2.a DESC,t1.a DESC +LIMIT 5; +a b a b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +<subquery2> inside the sort-nest +set use_sort_nest=1; +EXPLAIN SELECT * FROM t3,t2 +WHERE t3.b=t2.b AND +t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b) +ORDER BY t3.a DESC,t2.a DESC +LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 5 Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t3,t2 +WHERE t3.b=t2.b AND +t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b) +ORDER BY t3.a DESC,t2.a DESC +LIMIT 5; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "<subquery2>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "rows": 5, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + }, + "read_sorted_file": { + "filesort": { + "sort_key": "`sort-nest`.`max(t1.a)` desc, `sort-nest`.a desc", + "table": { + "table_name": "<sort-nest>", + "access_type": "ALL", + "rows": 1, + "filtered": 100 + } + } + }, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 100, + "filtered": 100, + "attached_condition": "t3.a = `sort-nest`.`max(t1.a)` and t3.b = `sort-nest`.b" + } + } +} +SELECT * FROM t3,t2 +WHERE t3.b=t2.b AND +t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b) +ORDER BY t3.a DESC,t2.a DESC +LIMIT 5; +a b a b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +set use_sort_nest=0; +SELECT * FROM t3,t2 +WHERE t3.b=t2.b AND +t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b) +ORDER BY t3.a DESC,t2.a DESC +LIMIT 5; +a b a b +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +DROP TABLE t1,t2,t3,t0; diff --git a/mysql-test/main/sort_nest_sj.test b/mysql-test/main/sort_nest_sj.test new file mode 100644 index 00000000000..2a06da7097c --- /dev/null +++ b/mysql-test/main/sort_nest_sj.test @@ -0,0 +1,201 @@ +--source include/have_sequence.inc + +--echo # +--echo # SORT-NEST WITH SEMI JOINS +--echo # + +--echo +--echo # MERGED SEMI-JOINS +--echo + +--echo # SEMI JOIN MATERIALIZATION SCAN with SORT-NEST + +CREATE TABLE t0(a int); +CREATE TABLE t1 (a int, b int, c int); +CREATE TABLE t2 (a int, b int, c int); +CREATE TABLE t3 (a int, b int, c int, key(a)); +CREATE TABLE t4 (a int, b int, c int, key(a)); + +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t2 SELECT a,a,a FROM t0; +INSERT INTO t3 SELECT a,a,a FROM t0; +INSERT INTO t4 SELECT a,a,a FROM t0; + +--disable_result_log +ANALYZE TABLE t0 PERSISTENT FOR ALL; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +--enable_result_log + +--echo # SJM scan inside the sort-nest +--echo # sort-nest includes (t2, <subquery2>) + +let $query= SELECT t1.a, t2.a, t1.b,t2.b + FROM t1, t2 + WHERE t1.a=t2.a AND + t1.b IN (SELECT t3.b FROM t3,t4 + WHERE t3.a < 3 AND t3.a=t4.a) + ORDER BY t1.b DESC ,t2.b DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +--echo # +--echo # SJM scan table is the first table inside the sort-nest +--echo # + +alter table t2 add key(b); +let $query= SELECT t1.a, t2.a, t1.b,t2.b + FROM t1, t2 + WHERE t1.a=t2.a AND t2.b < 5 AND + t1.b IN (SELECT t3.b FROM t3,t4 + WHERE t3.a < 3 AND t3.a=t4.a) + ORDER BY t2.b DESC, t1.b DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval $query; + +set use_sort_nest= 0; +eval $query; + +DROP TABLE t0, t1, t2, t3, t4; + +--echo # +--echo # SJM Lookup with sort-nest, where SJM lookup table is outside the +--echo # sort-nest +--echo # + +create table t1 (a int, b int, c int, key(a)); +create table t2 (a int, b int, c int, key(c)); +create table t3 (a int, b int, c int, key(a)); +create table t4 (a int, b int, c int); + +INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_10; +INSERT INTO t2 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; +INSERT INTO t3 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_1000; +INSERT INTO t4 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100; + +--disable_result_log +ANALYZE TABLE t1 PERSISTENT FOR ALL; +ANALYZE TABLE t2 PERSISTENT FOR ALL; +ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; +--enable_result_log + +let $query= SELECT t1.a, t2.a, t2.b + FROM t1, t2 + WHERE t2.a in (SELECT t3.b from t3) + AND t1.a= t2.b + AND t1.a < 5 + ORDER BY t1.b DESC, t2.a DESC + LIMIT 5; + +set use_sort_nest= 1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest= 0; +eval $query; + +DROP TABLE t1, t2, t3, t4; + +--echo # +--echo # Firstmatch strategy +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 SELECT a,a,a from t0; +create table t2 as SELECT * from t1; +create table t3 as SELECT * from t1; +let $query= SELECT * FROM t1, t2 + WHERE t1.a=t2.a AND + t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c) + ORDER BY t2.c DESC, t1.c DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +set optimizer_switch='firstmatch=off'; + +--echo # +--echo # Duplicate Weedout strategy +--echo # + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t0,t1,t2,t3; + +--echo +--echo # NON-MERGED SEMI JOINS +--echo + +create table t0 (a int); +INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10; +create table t1 (a int, b int); +insert into t1 SELECT a,a from t0 where a <5; +create table t2 as SELECT * from t1 where a < 5; +create table t3(a int, b int); +INSERT INTO t3 SELECT seq-1, seq-1 FROM seq_1_to_100; + +--echo <subquery2> outside the sort-nest + +let $query= SELECT * from t2,t1 + WHERE t2.b=t1.b + AND + t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b) + ORDER BY t2.a DESC,t1.a DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +--echo <subquery2> inside the sort-nest + +let $query= SELECT * FROM t3,t2 + WHERE t3.b=t2.b AND + t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b) + ORDER BY t3.a DESC,t2.a DESC + LIMIT 5; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +DROP TABLE t1,t2,t3,t0; diff --git a/mysql-test/main/sort_nest_subselect.test b/mysql-test/main/sort_nest_subselect.test new file mode 100644 index 00000000000..e34a4817499 --- /dev/null +++ b/mysql-test/main/sort_nest_subselect.test @@ -0,0 +1,99 @@ +--echo # +--echo # Testing SORT-NEST with non-flattened Subqueries +--echo # + +--echo # +--echo # Dependent subquery attached to table t3 outside the sort-nest(t1,t2) +--echo # + +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); +insert into t1 select a,a from t0 where a <5; # 5 rows +create table t2 as select * from t1 where a < 5; # 5 rows +create table t3(a int, b int, c int); +insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows + +create table t4(a int, b int, c int, key(b)); +insert into t4 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows + +--echo # ref access inside the dependent subquery should be with sort-nest.b instead of t1.b +--echo # subquery is attached to table t3 which is outside the sort-nest + +let $query= SELECT * FROM t1,t2,t3 + WHERE t1.b=t2.b and + EXISTS (select 1 from t4 where t4.b=t1.b and t4.b < 4 group by t4.c having t3.b=max(t4.a)) + ORDER BY t2.a desc,t1.a desc + LIMIT 5; + +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +--echo # same as above but exists to in transformation not allowed +--echo # subquery is attached to table t3 which is outside the sort-nest + +set optimizer_switch='exists_to_in=off'; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; +eval $query; + +set optimizer_switch=default; + +drop table t0,t1,t2,t3,t4; + + +--echo # DEPENDENT SUBQUERIES + +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); +insert into t1 select a,a from t0 where a <5; +create table t2 as select * from t1 where a < 5; +create table t3 as select (A.a + 10*B.a+C.a*100) as a, (A.a + 10*B.a+C.a*100) as b, + (A.a + 10*B.a+C.a*100) as c from t0 A, t0 B,t0 C; # 1000 rows + +set optimizer_switch='exists_to_in=off'; + +--echo # sort-nest(t2,t1) and subquery should be attached to table ot1 +let $query= select * from t2,t1,t3 + where exists (select max(t3.a) from t3 t4 where t4.b=t1.b group by t4.c having t3.a= max(t4.a)) + order by t2.a desc,t1.a desc limit 5; + +eval explain $query; +eval explain format=json $query; +eval $query; + +set optimizer_switch=default; +--echo # sort-nest(t2,t1) and subquery should be attached to table ot1 (same as above) +let $query= select * from t2,t1,t3 + where t3.a in (select max(t4.a) from t3 t4 where t4.b=t1.b group by t4.c) + order by t2.a desc,t1.a desc limit 5; + +eval explain $query; +eval explain format=json $query; +eval $query; + +--echo # sort-nest(t2,t1) and dependent subquery in the select list +let $query= select (select t4.a from t3 t4 where t4.a > t1.b limit 1) as x, t2.b, t1.b, t3.a from t1,t2,t3 + where t1.a = t2.a order by t2.b desc, t1.b desc limit 5; + +eval explain $query; +eval explain format=json $query; +eval $query; + +--echo # +--echo # sort-nest(t2,t1) and sort-nest fields substitution in the having clause of the subquery +--echo # after IN -> EXISTS transformation +--echo # + +let $query= select * from t2,t1,t3 ot1 + where t2.a+ot1.a in (select max(t3.a) from t3 where t3.b=t1.b group by t3.c) + order by t2.a desc,t1.a desc limit 5; + +eval explain $query; +eval explain format=json $query; +eval $query; + +drop table t0,t1,t2,t3; |