diff options
Diffstat (limited to 'mysql-test/main/order_by.result')
| -rw-r--r-- | mysql-test/main/order_by.result | 30 |
1 files changed, 19 insertions, 11 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 153cccf822e..70f21cacc98 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1193,7 +1193,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 111 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index k2 k3 5 NULL 22318 Using where +1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where @@ -1222,6 +1222,10 @@ id c3 176 14 186 14 196 14 +ALTER TABLE t2 DROP INDEX k3, ADD INDEX k3 (c3,c2); +EXPLAIN SELECT c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index k2 k3 10 NULL 22318 Using where; Using index DROP TABLE t1,t2; CREATE TABLE t1 ( a INT, @@ -1552,11 +1556,17 @@ INSERT INTO t1 VALUES (1, 10), (2, NULL); EXPLAIN SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 2 Using where; Using index; Using filesort +1 SIMPLE t1 range a_c,a a_c 10 NULL 2 Using where; Using index # Must return 1 row SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; col 1 +# With more rows "range" changes to "ref_or_null" +INSERT INTO t1 select seq,seq from seq_1_to_10; +EXPLAIN +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 2 Using where; Using index; Using filesort # Must use ref-or-null on the a_c index EXPLAIN SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC; @@ -3408,10 +3418,9 @@ ANALYZE "r_loops": 1, "r_total_time_ms": "REPLACED", "r_limit": 5, - "r_used_priority_queue": false, - "r_output_rows": 100, - "r_buffer_size": "REPLACED", - "r_sort_mode": "sort_key,packed_addon_fields", + "r_used_priority_queue": true, + "r_output_rows": 6, + "r_sort_mode": "sort_key,rowid", "table": { "table_name": "t1", "access_type": "ALL", @@ -3447,7 +3456,7 @@ CREATE TABLE t2 SELECT * FROM t1; EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t1 index PRIMARY b 5 NULL 1 Using where +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.t2.b 1 Using where SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2; c 1 @@ -3495,11 +3504,10 @@ WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using filesort -1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 -2 MATERIALIZED books ref library_idx library_idx 4 const 2 100.00 Using where +1 PRIMARY wings ALL PRIMARY NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY books ALL library_idx NULL NULL NULL 2 100.00 Using where; FirstMatch(wings); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` +Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`books`.`wings_id` = `test`.`wings`.`id` order by `test`.`wings`.`id` set optimizer_switch= @save_optimizer_switch; DROP TABLE books, wings; # |