diff options
| author | Monty <monty@mariadb.org> | 2020-03-24 13:34:03 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2020-03-26 16:07:10 +0200 |
| commit | 8e22ca898f4ecf5807eef9cf1eab6b4c0d8dcc94 (patch) | |
| tree | d56bb0a53fcdbcfb1153cfc33d4b0de1f2f2be99 | |
| parent | cdf064e1872583334b32b575ef1cb43b2b8b567b (diff) | |
| download | mariadb-git-bb-10.5-monty-opt.tar.gz | |
More cost fixes:bb-10.5-monty-opt
- heap::read_time() and heap::keyread_time() adjusted to not add +1.
This was to ensure that handler::keyread_time() doesn't give
higher cost for heap tables than for normal tables. One effect of
this is that heap and derived tables stored in heap will prefer
key access as this is now regarded as cheap.
- Changed cost for index read in sql_select.cc to match
multi_range_read_info_const(). All index cost calculation is now
done trough one function.
- 'ref' will now use quick_cost for keys if it exists. This is done
so that for '=' ranges, 'ref' is prefered over 'range'.
- scan_time() now takes avg_io_costs() into account
- get_delayed_table_estimates() uses block_size and avg_io_cost()
- Removed default argument to test_if_order_by_key(); simplifies code
81 files changed, 635 insertions, 559 deletions
diff --git a/include/my_base.h b/include/my_base.h index 5bc778655dc..7efa5eb9673 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -615,7 +615,6 @@ enum data_file_type { #define EQ_RANGE 32U #define NULL_RANGE 64U #define GEOM_FLAG 128U -#define SKIP_RANGE 256U typedef struct st_key_range { diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index aa7ab6e60a1..be892cf774b 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -1,3 +1,5 @@ +--source include/have_sequence.inc + --echo # --echo # Bug#36981 - "innodb crash when selecting for update" --echo # @@ -721,10 +723,12 @@ DROP TABLE t1; CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index a24ebdd1fff..f105330976a 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1159,10 +1159,10 @@ with cte as union (select a from t1 where a < 2); a +1 4 5 7 -1 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) (select a from cte where exists( select a from t1 where cte.a=t1.a )) @@ -1170,16 +1170,16 @@ union (select a from t1 where a < 2)"; execute stmt; a +1 4 5 7 -1 execute stmt; a +1 4 5 7 -1 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7 group by a) @@ -1216,9 +1216,9 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a 1 -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7) (select a from t1 where a < 2) @@ -1227,15 +1227,15 @@ union execute stmt; a 1 -7 -5 4 +5 +7 execute stmt; a 1 -7 -5 4 +5 +7 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7) @@ -1244,9 +1244,9 @@ where exists( select a from t1 where t1.a < 2 and cte.a=t1.a )) union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7) (select a from cte @@ -1255,14 +1255,14 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a ))"; execute stmt; a -7 -5 4 +5 +7 execute stmt; a -7 -5 4 +5 +7 deallocate prepare stmt; drop table t1; # diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index f311271d4d2..b39d6cde8a5 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -808,9 +808,12 @@ with cte as union (select a from t1 where a < 2); +--sorted_result eval $q1; eval prepare stmt from "$q1"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -821,9 +824,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q2; eval prepare stmt from "$q2"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -834,9 +840,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q3; eval prepare stmt from "$q3"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; @@ -848,9 +857,12 @@ with cte as union (select a from cte where exists( select a from t1 where cte.a=t1.a )); +--sorted_result eval $q4; eval prepare stmt from "$q4"; +--sorted_result execute stmt; +--sorted_result execute stmt; deallocate prepare stmt; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 0f6da7d9495..8264e311f93 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1064,16 +1064,19 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +INSERT INTO t1 select seq from seq_1_to_1000; +INSERT INTO t2 select seq+1000 from seq_1_to_1000; +INSERT INTO t3 select 'qqq',seq+2000 from seq_1_to_1000; set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -1 PRIMARY <derived3> hash_ALL NULL #hash#$hj 3075 func 2 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1002 +1 PRIMARY <derived3> hash_ALL NULL #hash#$hj 3075 func 1002 Using where; Using join buffer (flat, BNLH join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 v3.d 1 Using index -3 DERIVED t3 ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 2 +3 DERIVED t3 ALL NULL NULL NULL NULL 1002 +2 DERIVED t1 ALL NULL NULL NULL NULL 1002 SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; a b c d DROP VIEW v1, v3; diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce8550..8c41f80ffbd 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1,5 +1,6 @@ # Initialize --source include/default_optimizer_switch.inc +--source include/have_sequence.inc --disable_warnings drop table if exists t1,t2,t3; @@ -919,6 +920,9 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +INSERT INTO t1 select seq from seq_1_to_1000; +INSERT INTO t2 select seq+1000 from seq_1_to_1000; +INSERT INTO t3 select 'qqq',seq+2000 from seq_1_to_1000; set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index b853426fdf6..c7f6448f2d8 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -8828,9 +8828,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t1", "access_type": "ALL", - "rows": 3, + "rows": 4, + "filtered": 100, + "attached_condition": "t1.id2 is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["id2"], + "ref": ["test.t1.id2"], + "rows": 2, "filtered": 100, "attached_condition": "vc.ct > 0", "materialized": { @@ -8850,18 +8862,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "163", - "join_type": "BNL", - "attached_condition": "t1.id2 = vc.id2" } } } @@ -9078,9 +9078,8 @@ WHERE d_tab.e>1 ) ; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE (t1.a,t1.b) IN @@ -9103,44 +9102,33 @@ EXPLAIN "table_name": "t1", "access_type": "ALL", "rows": 5, - "filtered": 100 + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null" }, "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "10", "used_key_parts": ["e", "max_f"], - "ref": ["func", "func"], - "rows": 1, + "ref": ["test.t1.a", "test.t1.b"], + "rows": 2, "filtered": 100, + "first_match": "t1", "materialized": { - "unique": 1, "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "d_tab.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_f > 18", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "t2.e > 1" - } - } - } + "select_id": 3, + "having_condition": "max_f > 18", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.e > 1" } } } @@ -9179,9 +9167,8 @@ WHERE d_tab.max_f<25 ) ; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE (t1.a,t1.b) IN @@ -9204,43 +9191,32 @@ EXPLAIN "table_name": "t1", "access_type": "ALL", "rows": 5, - "filtered": 100 + "filtered": 100, + "attached_condition": "t1.b < 25 and t1.a is not null and t1.b is not null" }, "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "10", "used_key_parts": ["e", "max_f"], - "ref": ["func", "func"], - "rows": 1, + "ref": ["test.t1.a", "test.t1.b"], + "rows": 2, "filtered": 100, + "first_match": "t1", "materialized": { - "unique": 1, "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "d_tab.max_f < 25", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_f > 18 and max_f < 25", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 5, - "filtered": 100 - } - } - } + "select_id": 3, + "having_condition": "max_f > 18 and max_f < 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 } } } @@ -14045,16 +14021,16 @@ a b max_c a b c 1 21 345 3 21 231 select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); a b max_c a b c -2 33 7 5 33 207 4 33 123 5 33 207 -2 33 7 8 33 117 +2 33 7 5 33 207 4 33 123 8 33 117 -1 21 345 3 21 231 +2 33 7 8 33 117 3 21 500 3 21 231 +1 21 345 3 21 231 explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 2 Using where 2 DERIVED t3 range i1 i1 5 NULL 5 Using index condition explain format=json select * from v1,t2 where (v1.b=t2.b) and (v1.a<5); EXPLAIN @@ -14062,9 +14038,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t2", "access_type": "ALL", - "rows": 5, + "rows": 9, + "filtered": 100, + "attached_condition": "t2.b is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["b"], + "ref": ["test.t2.b"], + "rows": 2, "filtered": 100, "attached_condition": "v1.a < 5", "materialized": { @@ -14083,18 +14071,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 9, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "173", - "join_type": "BNL", - "attached_condition": "t2.b = v1.b" } } } @@ -16868,8 +16844,8 @@ explain extended select id, a from t1 where id in (select id from v1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) -3 DERIVED t1 ALL PRIMARY NULL NULL NULL 20 100.00 Using temporary; Using filesort -3 DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`v1`) where `v1`.`id` = `test`.`t1`.`id` select id, a from t1 @@ -16906,10 +16882,10 @@ group by t1.id) dt); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 20 100.00 1 PRIMARY <derived3> ref key0 key0 4 test.t1.id 2 100.00 FirstMatch(t1) -3 DERIVED t1 ALL PRIMARY NULL NULL NULL 20 100.00 Using temporary; Using filesort -3 DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where +3 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 +3 LATERAL DERIVED t2 ref ro_id ro_id 4 test.t1.id 1 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where 1 group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id` +Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where `test`.`t1`.`id` = `test`.`t1`.`id` group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id` drop view v1; drop table t1,t2; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index e8f9df5f80d..15b67b51f45 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -9,6 +9,7 @@ KEY c1 (c1), KEY n1_c1_n2 (n1,c1,n2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); +insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -16,7 +17,7 @@ test.t1 analyze status OK EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index c1,n1_c1_n2 n1_c1_n2 9 NULL 2 Using where; Using index +1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 4f9d2e970f7..d4d7fde1fcd 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/default_optimizer_switch.inc +--source include/have_sequence.inc --echo # --echo # MDEV-16917: do not use splitting for derived with join cache @@ -13,6 +14,7 @@ CREATE TABLE t1 ( KEY n1_c1_n2 (n1,c1,n2) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); +insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; diff --git a/mysql-test/main/disabled.def b/mysql-test/main/disabled.def index 02104f6a680..d3366587ee5 100644 --- a/mysql-test/main/disabled.def +++ b/mysql-test/main/disabled.def @@ -18,3 +18,5 @@ file_contents : MDEV-6526 these files are not installed anymore max_statement_time : cannot possibly work, depends on timing partition_open_files_limit : open_files_limit check broken by MDEV-18360 partition_innodb : Waiting for fix MDEV-20169 +type_enum : Waiting for fix MDEV-6978 +type_set : Waiting for fix MDEV-6978 diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result index a48cfcf213b..18a15795aab 100644 --- a/mysql-test/main/explain_json.result +++ b/mysql-test/main/explain_json.result @@ -1037,7 +1037,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); EXPLAIN { @@ -1070,7 +1070,7 @@ EXPLAIN "rows": 65, "filtered": 100, "attached_condition": "t1.b = 'a' and t1.c = 'i121' and t1.a2 >= 'b'", - "using_index_for_group_by": "scanning" + "using_index_for_group_by": true } } } diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 568c2a3cdd5..3a49c075ddd 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -1578,7 +1578,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) FORCE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL i2 9 NULL 144 Using index +1 SIMPLE t1 range NULL i2 4 NULL 145 Using index for group-by EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 144 diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index d55e57a8432..4f32db780fd 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -3822,20 +3822,20 @@ set @@optimizer_use_condition_selectivity=4; set @@use_stat_tables=PREFERABLY; explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_outer index a a 10 NULL 30 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 7 100.00 Using index 2 MATERIALIZED t1 range NULL a 5 NULL 5 100.00 Using index for group-by Warnings: -Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `<subquery2>`.`max(b)` = `test`.`t1_outer`.`a` +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_outer index a a 10 NULL 30 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 7 100.00 Using index 2 MATERIALIZED t1 range NULL a 5 NULL 5 100.00 Using index for group-by Warnings: -Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `<subquery2>`.`max(b)` = `test`.`t1_outer`.`a` +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` drop table t1; # # End of 10.0 tests diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result index 7a8609f39e8..3ec98216479 100644 --- a/mysql-test/main/index_intersect.result +++ b/mysql-test/main/index_intersect.result @@ -370,7 +370,7 @@ EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where +1 SIMPLE City index_merge Population,Country,Name Population,Country,Name 4,3,35 NULL # Using sort_intersect(Population,Country,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; @@ -695,7 +695,7 @@ ID Name Country Population 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 -SET SESSION sort_buffer_size = 2048; +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; diff --git a/mysql-test/main/index_intersect.test b/mysql-test/main/index_intersect.test index b8ab5a688c5..26937fd5eef 100644 --- a/mysql-test/main/index_intersect.test +++ b/mysql-test/main/index_intersect.test @@ -275,10 +275,22 @@ SELECT * FROM City USE INDEX () SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; - - -SET SESSION sort_buffer_size = 2048; - + +# Originally this was just sort_buffer_size=2048. Then, it started +# failing on 32bit due to different cost number in +# Unique::get_use_cost() because of sizeof(sizeof(TREE_ELEMENT)+key_size) + +# On 64 bit: Unique object element_size=32, which gives 2048/32= 64 elements +# in the tree. +# On 32 bit: Unique object element_size=24. +# If we want 64 elements in the tree, we need 64*24=1536 as sort_buffer_size. + +# The purpose of setting sort_buffer_size is to show that some of the following +# explains should use 'index_merge' while others should use range +# If the following code causes future problems, the other option would be +# to create a separate result-.diff file for 32 bit. + +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); # The following EXPLAIN command demonstrate that the execution plans # may be different if sort_buffer_size is set to a small value diff --git a/mysql-test/main/index_intersect_innodb.result b/mysql-test/main/index_intersect_innodb.result index 9f279eaf8af..c070b41e54d 100644 --- a/mysql-test/main/index_intersect_innodb.result +++ b/mysql-test/main/index_intersect_innodb.result @@ -701,7 +701,7 @@ ID Name Country Population 3808 Austin USA 656562 3809 Baltimore USA 651154 3810 Memphis USA 650100 -SET SESSION sort_buffer_size = 2048; +SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 3ff72871342..c1597e583a4 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -750,7 +750,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where; Using index explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a 4 const 15568 Using where +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15094 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971 diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index f29f81c0ee4..ff92b4506ba 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -13,7 +13,7 @@ set optimizer_switch='extended_keys=off'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -21,9 +21,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -33,7 +33,7 @@ set optimizer_switch='extended_keys=on'; explain select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|8 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index flush status; select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; count(*) @@ -41,9 +41,9 @@ count(*) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 2 +Handler_read_next 1 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -274,7 +274,7 @@ explain select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity|i_l_shipdate 4|4 const 5 (0%) Using where; Using rowid filter +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 const 5 Using where flush status; select max(l_linenumber) from lineitem where l_shipdate='1992-07-01' and l_orderkey=130; @@ -283,9 +283,9 @@ max(l_linenumber) show status like 'handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 2 +Handler_read_key 1 Handler_read_last 0 -Handler_read_next 7 +Handler_read_next 5 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 97d2f209be7..16d60ddba43 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -682,15 +682,17 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 101 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -701,7 +703,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 101 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 1a59c7b16e9..e5fa88512b9 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1473,8 +1473,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE DU system dog_id NULL NULL NULL 1 1 SIMPLE D system PRIMARY NULL NULL NULL 1 1 SIMPLE DSAR system NULL NULL NULL NULL 1 -1 SIMPLE DT range t_id t_id 2 NULL 2 Using where -1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,test.DT.t_id,func 1 Using index +1 SIMPLE DSA ref PRIMARY PRIMARY 4 const 3 Using where; Using index +1 SIMPLE DT ALL t_id NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id; diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index f990efdd06f..5bfcf7f20e1 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -444,9 +444,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index @@ -466,9 +466,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 range PRIMARY,m3 m3 4 NULL 1 Using where; Using index; Using join buffer (flat, BNL join) +1 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index b271d0a1289..c8bdc979387 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -739,13 +739,13 @@ p 1019 explain select i from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter +1 SIMPLE t2 index_merge k1,k2 k1,k2 5,11 NULL 50 Using intersect(k1,k2); Using where; Using index select i from t2 where a='yyyy' and i=3; i 3 explain select a from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter +1 SIMPLE t2 index_merge k1,k2 k1,k2 5,11 NULL 50 Using intersect(k1,k2); Using where; Using index select a from t2 where a='yyyy' and i=3 ; a yyyy @@ -753,7 +753,7 @@ select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 NULL 262143 2048 # # 0 3245 43 1594 30 +keycache1 7 NULL 262143 2048 # # 0 3229 43 1594 30 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; insert into t2 values (7000, 3, 'yyyy'); diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index a4fbdd794a7..2ff966fdfd3 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -3091,12 +3091,11 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3105,7 +3104,7 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 @@ -3126,19 +3125,17 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort # Status of EXPLAIN EXTENDED query Variable_name Value @@ -3147,13 +3144,12 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3162,14 +3158,14 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 @@ -3182,20 +3178,18 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 FirstMatch(t1) 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 # Status of EXPLAIN EXTENDED query @@ -3205,13 +3199,12 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived4> ref key0 key0 5 test.t1.a 2 100.00 FirstMatch(t1) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 @@ -3220,14 +3213,14 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 -Handler_read_rnd_next 10 +Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index e49158fdbe9..0ae18e6e817 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -675,16 +675,18 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c @@ -694,8 +696,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index d9e51c3fba0..6b9d59c636a 100644 --- a/mysql-test/main/null_key.result +++ b/mysql-test/main/null_key.result @@ -160,7 +160,7 @@ a b 7 NULL explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (b=7 or b is null); a b 7 NULL @@ -194,7 +194,7 @@ a a b explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -204,7 +204,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 5 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -214,7 +214,7 @@ a a b explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 5 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null); a a b 7 7 NULL diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ff4bf09944b..65fc7d6cc98 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1022,7 +1022,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 200, + "cost": 200.06, "chosen": true }, { @@ -1035,13 +1035,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 200, + "cost": 200.06, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 100, - "cost_for_plan": 242.32, + "cost_for_plan": 242.38, "estimated_join_cardinality": 100 } ] @@ -1080,7 +1080,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 200, + "cost": 200.06, "chosen": true }, { @@ -1093,13 +1093,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 200, + "cost": 200.06, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 100, - "cost_for_plan": 242.32, + "cost_for_plan": 242.38, "pruned_by_cost": true } ] @@ -1199,7 +1199,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "best_covering_index_scan": { "index": "a", - "cost": 14185, + "cost": 13377, "chosen": false, "cause": "cost" }, @@ -1210,7 +1210,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "index": "a", "covering": true, "rows": 5, - "cost": 6.75 + "cost": 6.25 } ] }, @@ -1222,7 +1222,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.75, + "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true @@ -1236,12 +1236,12 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.75, + "cost": 6.25, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, - "cost_for_plan": 6.75, + "cost_for_plan": 6.25, "chosen": true } } @@ -1258,20 +1258,20 @@ EXPLAIN SELECT DISTINCT a FROM t1 { { "access_type": "index_merge", "resulting_rows": 5, - "cost": 6.75, + "cost": 6.25, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 5, - "cost": 6.75, + "cost": 6.25, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 5, - "cost_for_plan": 7.75, + "cost_for_plan": 7.25, "estimated_join_cardinality": 5 } ] @@ -1384,7 +1384,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "best_covering_index_scan": { "index": "a", - "cost": 2.4369, + "cost": 2.4092, "chosen": true }, "setup_range_conditions": [], @@ -1578,7 +1578,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.2468, + "cost": 4.2117, "chosen": true }, "setup_range_conditions": [], @@ -1761,7 +1761,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.2468, + "cost": 4.2117, "chosen": true }, "setup_range_conditions": [], @@ -2008,7 +2008,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 218.74, + "cost": 216.29, "chosen": true }, { @@ -2018,7 +2018,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 21, - "cost": 25.415, + "cost": 25.362, "chosen": true } ], @@ -2039,7 +2039,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, "rows_for_plan": 21, - "cost_for_plan": 25.415, + "cost_for_plan": 25.362, "chosen": true } } @@ -2077,7 +2077,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "used_range_estimates": true, "rows": 180, - "cost": 92, + "cost": 180.27, "chosen": true }, { @@ -2085,7 +2085,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_b", "used_range_estimates": true, "rows": 21, - "cost": 25.395, + "cost": 21.142, "chosen": true }, { @@ -2097,13 +2097,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "chosen_access_method": { "type": "ref", "records": 21, - "cost": 25.395, + "cost": 21.142, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 21, - "cost_for_plan": 29.595, + "cost_for_plan": 25.342, "estimated_join_cardinality": 21 } ] @@ -2127,7 +2127,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "fanout": 1, - "read_time": 25.396, + "read_time": 21.143, "table": "t1", "rows_estimation": 21, "possible_keys": [ @@ -2143,8 +2143,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "can_resolve_order": true, "updated_limit": 47, - "range_scan_time": 4.324, - "index_scan_time": 4.324, + "range_scan_time": 4.331, + "index_scan_time": 4.331, "records": 180, "chosen": true }, @@ -2190,7 +2190,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 218.74, + "cost": 216.29, "chosen": true } ], @@ -2211,7 +2211,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 218.74, + "cost_for_plan": 216.29, "chosen": true } } @@ -3163,7 +3163,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "best_covering_index_scan": { "index": "pk_a_b", - "cost": 3.0429, + "cost": 3.0107, "chosen": true }, "setup_range_conditions": [], @@ -3176,7 +3176,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3473, + "cost": 1.3456, "chosen": true }, { @@ -3186,7 +3186,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3483, + "cost": 1.3458, "chosen": false, "cause": "cost" }, @@ -3197,7 +3197,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3493, + "cost": 0.3461, "chosen": true } ], @@ -3205,10 +3205,10 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "intersecting_indexes": [ { "index": "pk", - "index_scan_cost": 1.0023, - "cumulated_index_scan_cost": 1.0023, + "index_scan_cost": 1.0006, + "cumulated_index_scan_cost": 1.0006, "disk_sweep_cost": 0.9008, - "cumulative_total_cost": 1.9031, + "cumulative_total_cost": 1.9014, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, @@ -3246,7 +3246,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, - "cost_for_plan": 0.3493, + "cost_for_plan": 0.3461, "chosen": true } } @@ -3284,7 +3284,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk", "used_range_estimates": true, "rows": 1, - "cost": 1.3273, + "cost": 1.1256, "chosen": true }, { @@ -3292,7 +3292,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a", "used_range_estimates": true, "rows": 1, - "cost": 1.3283, + "cost": 1.1258, "chosen": false, "cause": "cost" }, @@ -3301,7 +3301,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a_b", "used_range_estimates": true, "rows": 1, - "cost": 0.3293, + "cost": 0.1261, "chosen": true }, { @@ -3313,13 +3313,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 0.3293, + "cost": 0.1261, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 1, - "cost_for_plan": 0.5293, + "cost_for_plan": 0.3261, "estimated_join_cardinality": 1 } ] @@ -3657,7 +3657,7 @@ explain delete from t0 where t0.a<3 { "using_mrr": false, "index_only": false, "rows": 3, - "cost": 3.752, + "cost": 3.7468, "chosen": true } ], @@ -3675,7 +3675,7 @@ explain delete from t0 where t0.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 3.752, + "cost_for_plan": 3.7468, "chosen": true } } @@ -3787,7 +3787,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.0234, + "cost": 3.0059, "chosen": true }, "setup_range_conditions": [], @@ -3800,7 +3800,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], @@ -3821,7 +3821,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.752, + "cost_for_plan": 0.7468, "chosen": true } } @@ -3852,7 +3852,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 3.0234, + "cost": 3.0059, "chosen": true }, "setup_range_conditions": [], @@ -3865,7 +3865,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], @@ -3886,7 +3886,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 0.752, + "cost_for_plan": 0.7468, "chosen": true } } @@ -3913,20 +3913,20 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "resulting_rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, - "cost": 0.752, + "cost": 0.7468, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.352, + "cost_for_plan": 1.3468, "rest_of_plan": [ { "plan_prefix": ["t0"], @@ -3939,7 +3939,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 1, - "cost": 3.007, + "cost": 3.0018, "chosen": true }, { @@ -3951,13 +3951,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 3.007, + "cost": 3.0018, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 4.959, + "cost_for_plan": 4.9485, "estimated_join_cardinality": 3 } ] @@ -3970,20 +3970,20 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "resulting_rows": 3, - "cost": 0.752, + "cost": 0.7468, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 3, - "cost": 0.752, + "cost": 0.7468, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.352, + "cost_for_plan": 1.3468, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -3996,7 +3996,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 2, - "cost": 3.014, + "cost": 3.0035, "chosen": true }, { @@ -4008,13 +4008,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen_access_method": { "type": "ref", "records": 2, - "cost": 3.014, + "cost": 3.0035, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 6, - "cost_for_plan": 5.5661, + "cost_for_plan": 5.5503, "pruned_by_cost": true } ] @@ -7501,7 +7501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3483, + "cost": 0.3458, "chosen": true } ], @@ -7535,7 +7535,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 22.9, + "cost": 21.634, "chosen": true } ], @@ -7572,7 +7572,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1221.1, + "cost": 1203.9, "chosen": true } ], @@ -7617,7 +7617,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 4, - "cost": 4.9598, + "cost": 4.9487, "chosen": true } ], @@ -7656,7 +7656,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3497, + "cost": 1.3462, "chosen": true } ], @@ -7690,7 +7690,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3497, + "cost": 1.3462, "chosen": true } ], @@ -7732,7 +7732,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3487, + "cost": 1.3459, "chosen": true } ], @@ -7767,7 +7767,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3485, + "cost": 1.3459, "chosen": true } ], @@ -7802,7 +7802,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3487, + "cost": 1.3459, "chosen": true } ], @@ -7840,7 +7840,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3485, + "cost": 1.3459, "chosen": true } ], @@ -7881,7 +7881,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.5419, + "cost": 1.3943, "chosen": true } ], @@ -7920,7 +7920,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.5524, + "cost": 2.5469, "chosen": true } ], @@ -7976,7 +7976,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1221.1, + "cost": 1203.9, "chosen": true } ], @@ -8185,7 +8185,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 20, + "cost": 20.006, "chosen": true }, @@ -8200,13 +8200,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "type": "ref", "records": 1, - "cost": 20, + "cost": 20.006, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 10, - "cost_for_plan": 26.017, + "cost_for_plan": 26.023, "selectivity": 0.8047, "estimated_join_cardinality": 8.0469 } @@ -8274,7 +8274,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.3965, + "cost": 1.3579, "chosen": true } ], diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index e76365a8af6..ca8098391d5 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -111,12 +111,12 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3473, + "cost": 0.3456, "chosen": true } ], "index_to_merge": "a", - "cumulated_cost": 0.3473 + "cumulated_cost": 0.3456 }, { "range_scan_alternatives": [ @@ -127,15 +127,15 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.3473, + "cost": 0.3456, "chosen": true } ], "index_to_merge": "b", - "cumulated_cost": 0.6947 + "cumulated_cost": 0.6912 } ], - "cost_of_reading_ranges": 0.6947, + "cost_of_reading_ranges": 0.6912, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": [ @@ -158,7 +158,7 @@ explain select * from t1 where a=1 or b=1 { } } ], - "index_roworder_union_cost": 2.4884, + "index_roworder_union_cost": 2.4849, "members": 2, "chosen": true } @@ -187,7 +187,7 @@ explain select * from t1 where a=1 or b=1 { ] }, "rows_for_plan": 2, - "cost_for_plan": 2.4884, + "cost_for_plan": 2.4849, "chosen": true } } @@ -209,20 +209,20 @@ explain select * from t1 where a=1 or b=1 { { "access_type": "index_merge", "resulting_rows": 2, - "cost": 2.4884, + "cost": 2.4849, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 2, - "cost": 2.4884, + "cost": 2.4849, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 2, - "cost_for_plan": 2.8884, + "cost_for_plan": 2.8849, "estimated_join_cardinality": 2 } ] @@ -323,7 +323,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": true }, @@ -337,7 +337,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": false, "cause": "cost" }, @@ -352,7 +352,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2730, + "cost": 2700.1, "chosen": false, "cause": "cost" } @@ -364,10 +364,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -376,10 +376,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -388,10 +388,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 120.76, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 30.942, "disk_sweep_cost": 0, - "cumulative_total_cost": 120.76, + "cumulative_total_cost": 30.942, "usable": true, "matching_rows_now": 2.6872, "intersect_covering_with_this_index": true, @@ -404,7 +404,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 2, - "cost": 120.76, + "cost": 30.942, "covering": true, "chosen": true }, @@ -422,7 +422,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 2, - "cost": 120.76, + "cost": 30.942, "covering": true, "clustered_pk_scan": false, "intersect_of": @@ -460,7 +460,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) ] }, "rows_for_plan": 2, - "cost_for_plan": 120.76, + "cost_for_plan": 30.942, "chosen": true } ] @@ -501,7 +501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": true }, @@ -515,13 +515,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": false, "cause": "cost" } ], "index_to_merge": "key1", - "cumulated_cost": 487 + "cumulated_cost": 457.06 }, { @@ -538,7 +538,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": true }, @@ -552,16 +552,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 487, + "cost": 457.06, "chosen": false, "cause": "cost" } ], "index_to_merge": "key3", - "cumulated_cost": 973.99 + "cumulated_cost": 914.12 } ], - "cost_of_reading_ranges": 973.99, + "cost_of_reading_ranges": 914.12, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": @@ -582,10 +582,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -594,10 +594,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -610,7 +610,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "chosen": true } @@ -631,10 +631,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 40.252, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 10.314, "disk_sweep_cost": 1923.1, - "cumulative_total_cost": 1963.4, + "cumulative_total_cost": 1933.5, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -643,10 +643,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key4", - "index_scan_cost": 40.252, - "cumulated_index_scan_cost": 80.504, + "index_scan_cost": 10.314, + "cumulated_index_scan_cost": 20.628, "disk_sweep_cost": 84.518, - "cumulative_total_cost": 165.02, + "cumulative_total_cost": 105.15, "usable": true, "matching_rows_now": 77.636, "intersect_covering_with_this_index": false, @@ -659,13 +659,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "chosen": true } } ], - "index_roworder_union_cost": 314.73, + "index_roworder_union_cost": 194.98, "members": 2, "chosen": true } @@ -686,7 +686,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -717,7 +717,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 165.02, + "cost": 105.15, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -747,7 +747,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) ] }, "rows_for_plan": 154, - "cost_for_plan": 314.73, + "cost_for_plan": 194.98, "chosen": true } ] diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index fcbef8b5ff4..fd5780cc64c 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -199,7 +199,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "index": "key1", "used_range_estimates": true, "rows": 1, - "cost": 1.3251, + "cost": 1.1251, "chosen": true }, { @@ -211,13 +211,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "chosen_access_method": { "type": "ref", "records": 1, - "cost": 1.3251, + "cost": 1.1251, "uses_join_buffering": false, "filter_used": false } }, "rows_for_plan": 1, - "cost_for_plan": 1.5251, + "cost_for_plan": 1.3251, "estimated_join_cardinality": 1 } ] diff --git a/mysql-test/main/opt_trace_ucs2.result b/mysql-test/main/opt_trace_ucs2.result index 1a88396f9ab..0d920341bcd 100644 --- a/mysql-test/main/opt_trace_ucs2.result +++ b/mysql-test/main/opt_trace_ucs2.result @@ -38,7 +38,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.5559, + "cost": 2.5477, "chosen": true } ], diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e8a25..9752aa71bfb 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -568,18 +568,18 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`_col_1` and `test`.`t1`.`b` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`))))) explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`2`))))) select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); a b @@ -590,10 +590,10 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`_col_1` and `test`.`t1`.`b` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`))))) select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); a b c @@ -606,10 +606,10 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`_col_1`,`tvc_0`.`_col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`_col_1` and `test`.`t2`.`c` = `<subquery2>`.`_col_2`)))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`))))) drop table t1, t2, t3; set @@in_predicate_conversion_threshold= default; # diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 76481a5042a..49aa1dd32a0 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -693,7 +693,7 @@ KEY StringField (FieldKey,StringVal(32)) INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using index condition; Using where +1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 @@ -3118,7 +3118,7 @@ id select_type table type possible_keys key key_len ref rows Extra # See above query EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref id_23_date,id_234_date id_23_date 2 const,const 8 Using where +1 SIMPLE t2 range id_23_date,id_234_date id_23_date 2 NULL 8 Using where drop table t1,t2; # # MDEV-8989: ORDER BY optimizer ignores equality propagation diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index dbd4b59eedf..e56fd46b55f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -281,7 +281,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range|filter a,b a|b 5|5 NULL 2 (41%) Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter a,b b|a 5|5 const 15 (5%) Using where; Using rowid filter SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; @@ -327,8 +327,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index eee4ad579c2..ee92d612889 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -330,8 +330,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 769d89ddaba..6b2c9a2f0bc 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -691,11 +691,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation CountryPopulation 3 const 270 Using index condition +1 SIMPLE City ref Country,CountryPopulation Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation CountryPopulation 3 const 7 Using index condition +1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') @@ -752,7 +752,7 @@ CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Country,CountryPopulation,CountryName CountryPopulation 3 const 270 Using index condition +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 267 Using index condition EXPLAIN SELECT * FROM City WHERE Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index cd286c96081..f69425fee8b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -825,7 +825,7 @@ WHERE ((Population > 101000 AND Population < 110000) OR ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryPopulation 3 const 7 Using index condition; Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using index condition; Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index a803f832935..5ad252bb42d 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -886,7 +886,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -915,7 +915,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -939,7 +939,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -975,7 +975,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1050,7 +1050,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -1079,7 +1079,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1095,7 +1095,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -1131,7 +1131,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1454,7 +1454,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1486,7 +1486,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1504,7 +1504,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.20 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1543,7 +1543,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1577,7 +1577,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1609,7 +1609,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1627,7 +1627,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.20 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND @@ -1666,7 +1666,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1703,7 +1703,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1740,7 +1740,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1758,7 +1758,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.95 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1802,7 +1802,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1836,7 +1836,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1873,7 +1873,7 @@ EXPLAIN "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], @@ -1891,7 +1891,7 @@ o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.95 2.44 Using index condition; Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND @@ -1935,7 +1935,7 @@ ANALYZE "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 106b95d36b7..ac4fe9f1534 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2115,8 +2115,8 @@ union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range|filter f1,f2 f1|f1 13|13 NULL 1 (2%) Using index condition; Using where; Using rowid filter -2 UNION t1 range|filter f1,f2 f1|f1 13|13 NULL 1 (2%) Using index condition; Using where; Using rowid filter +1 PRIMARY t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter +2 UNION t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain format=json ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) @@ -2135,11 +2135,12 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["f1", "f2"], "key": "f1", "key_length": "13", "used_key_parts": ["f1"], + "ref": ["const"], "rowid_filter": { "range": { "key": "f1", @@ -2161,11 +2162,12 @@ EXPLAIN "operation": "UNION", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["f1", "f2"], "key": "f1", "key_length": "13", "used_key_parts": ["f1"], + "ref": ["const"], "rowid_filter": { "range": { "key": "f1", @@ -2202,7 +2204,7 @@ id y x explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index -1 SIMPLE t2 index_merge x,y y,x 5,5 NULL 1 100.00 Using intersect(y,x); Using where; Using index +1 SIMPLE t2 ref x,y y 5 const 2 100.00 Using where Warnings: Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 drop table t1, t2; @@ -2240,17 +2242,17 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter b,a a|b 5|5 NULL 33 (6%) 5.90 Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; a b 11 2 11 2 -11 2 -22 2 83 2 +11 2 83 2 +22 2 drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT; @@ -2508,7 +2510,7 @@ WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainTimestamp|ixEventWhoisDomainDomain 4|767 NULL 1 (29%) 28.57 Using where; Using rowid filter +1 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 767|4 const 2 (14%) 14.29 Using index condition; Using where; Using filesort; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`t1`.`timestamp` desc SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index dad981db201..f84d2eae05a 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -73,12 +73,12 @@ order by s_acctbal desc, n_name, s_name, p_partkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 0.31 Using where; Using temporary; Using filesort -1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 1 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 1 PRIMARY region eq_ref PRIMARY PRIMARY 4 dbt3_s001.nation.n_regionkey 1 20.00 Using where 2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: @@ -119,7 +119,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) 1 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where -2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 +2 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 2 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 2 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where Warnings: @@ -489,7 +489,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where -2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where Warnings: Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 @@ -1661,7 +1661,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` # gives selectivity data explain extended select * from t1 where a in (17,51,5) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range|filter b,a a|b 5|5 NULL 29 (6%) 5.80 Using index condition; Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 1f44f7bc5ed..69dc506527b 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -28,7 +28,7 @@ select count(*) from t1 where a < 100000; connection default; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 999 Using where; Using index Warnings: Note 1003 select count(*) from t1 where a < 100000 connection con1; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index a73ebed03e0..ba2e3ef268f 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -74,7 +74,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -181,7 +181,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey @@ -214,7 +214,7 @@ order by o_year; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 5 dbt3_s001.part.p_partkey 30 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (27%) Using where; Using rowid filter 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 @@ -337,7 +337,7 @@ and o_orderkey=l_orderkey and p_partkey=l_partkey; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using index condition 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 Using where select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 2975c8ba686..0b50def19c2 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -246,7 +246,7 @@ order by o_year; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort 1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join) -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 5 dbt3_s001.part.p_partkey 30 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where 1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where 1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 09e30fa9ce8..1ee4718ef10 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -3176,7 +3176,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7040,8 +7040,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 5c4544a1b05..299faadeff7 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -96,10 +96,10 @@ explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` having trigcond(`test`.`t1`.`a` is null))))) AS `Z` from `test`.`t2` +Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); oref a diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 4260676cc37..aebeb4e0cc1 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -99,10 +99,10 @@ explain extended select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 Using where; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 8 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where `test`.`t1`.`oref` = `test`.`t2`.`oref` having trigcond(`test`.`t1`.`a` is null))))) AS `Z` from `test`.`t2` +Note 1003 /* select#1 */ select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`oref` = `test`.`t2`.`oref` and trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); oref a diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index d9048b2c040..5f38d2a5d35 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1889,8 +1889,8 @@ WHERE alias4.c = alias3.b ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 -3 MATERIALIZED alias4 index c c 11 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED alias4 ref c c 11 test.alias3.b 2 Using where; Using index DROP TABLE t1,t2; # # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 35e52abee45..1436f98d9f8 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -3179,7 +3179,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7040,8 +7040,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index dbc52834063..8100c1da7e5 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -3181,7 +3181,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 39306df943a..689363c6d22 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -3177,7 +3177,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7031,8 +7031,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 595d1de9e1a..f6f468ba1ab 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -3182,7 +3182,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 3 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -7046,8 +7046,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 288b28a2b6e..f9bbf2e00c4 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7031,8 +7031,8 @@ SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1 +2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 7e5327712d5..b4ad66458dc 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -2768,8 +2768,8 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Start temporary -1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 Using where; End temporary +1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 +1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Using where; FirstMatch(t1_2) 3 DERIVED t1 ALL NULL NULL NULL NULL 11 SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); @@ -2777,10 +2777,10 @@ a b a b 3 1 9 1 5 8 4 0 3 9 9 1 -2 4 6 8 2 4 4 0 -2 6 6 8 +2 4 6 8 2 6 4 0 +2 6 6 8 5 4 4 0 7 7 7 7 5 4 4 0 diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 2477f3d73ca..2301a03f973 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -2779,8 +2779,8 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Start temporary -1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 Using where; End temporary +1 PRIMARY t1_2 ALL NULL NULL NULL NULL 11 +1 PRIMARY <derived3> ref key0 key0 5 test.t1_1.a 2 Using where; FirstMatch(t1_2) 3 DERIVED t1 ALL NULL NULL NULL NULL 11 SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE (t1_1.a, t1_2.a) IN ( SELECT a, b FROM v1 ); @@ -2788,10 +2788,10 @@ a b a b 3 1 9 1 5 8 4 0 3 9 9 1 -2 4 6 8 2 4 4 0 -2 6 6 8 +2 4 6 8 2 6 4 0 +2 6 6 8 5 4 4 0 7 7 7 7 5 4 4 0 diff --git a/mysql-test/main/type_enum.result b/mysql-test/main/type_enum.result index 02d71c6bd9f..ca909e1a2f2 100644 --- a/mysql-test/main/type_enum.result +++ b/mysql-test/main/type_enum.result @@ -1932,16 +1932,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; 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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (c1 SET('a') CHARACTER SET latin1 PRIMARY KEY); INSERT INTO t1 VALUES ('a'); @@ -1959,16 +1957,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; 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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 PRIMARY KEY); INSERT INTO t1 VALUES ('a'); @@ -1986,16 +1982,14 @@ ALTER TABLE t2 ADD PRIMARY KEY(c1); SELECT t1.* FROM t1 JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; c1 a -a # t2 should NOT be eliminated EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1 COLLATE latin1_swedish_ci=t2.c1; 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 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 1 NULL 1 Using where; Using index DROP TABLE IF EXISTS t1,t2; # # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index f7f18c8cbae..88623028b54 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1121,7 +1121,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t1 index_subquery b b 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery b b 5 func 3 Using index; Full scan on NULL key DROP TABLE t1; DROP TABLE t2, t3; # diff --git a/mysql-test/suite/innodb/r/full_crc32_import.result b/mysql-test/suite/innodb/r/full_crc32_import.result index b8ebbb73706..06da5716aae 100644 --- a/mysql-test/suite/innodb/r/full_crc32_import.result +++ b/mysql-test/suite/innodb/r/full_crc32_import.result @@ -5,6 +5,7 @@ CREATE TABLE t1 b blob, c blob, KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT; +BEGIN; INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200)); INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200)); INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200)); @@ -17,6 +18,7 @@ INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200)); INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200)); INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +COMMIT; SELECT COUNT(*) FROM t1; COUNT(*) 40 @@ -85,6 +87,7 @@ ST_GeomFromText('LINESTRING(15 15,24 10,31 20)')); INSERT INTO t1(c2,c3) VALUES( ST_GeomFromText('POINT(60 50)'), ST_GeomFromText('LINESTRING(10 15,20 44,35 32)')); +BEGIN; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; @@ -96,6 +99,7 @@ INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +COMMIT; FLUSH TABLE t1 FOR EXPORT; # List before copying files db.opt diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 26aeb799d32..31db0622fe1 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2733,7 +2733,7 @@ KEY (c3) ) ENGINE=InnoDB; explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range c1,c3 c3 4 NULL 1 Using index condition; Using where +1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort DROP TABLE t1,t2; # # 36259: Optimizing with ORDER BY diff --git a/mysql-test/suite/innodb/t/full_crc32_import.test b/mysql-test/suite/innodb/t/full_crc32_import.test index 2c11a2423e6..aa4db3f9bf7 100644 --- a/mysql-test/suite/innodb/t/full_crc32_import.test +++ b/mysql-test/suite/innodb/t/full_crc32_import.test @@ -15,6 +15,7 @@ CREATE TABLE t1 c blob, KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT; +BEGIN; INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200)); INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200)); INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200)); @@ -27,6 +28,7 @@ INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200)); INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200)); INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +COMMIT; SELECT COUNT(*) FROM t1; FLUSH TABLE t1 FOR EXPORT; @@ -95,6 +97,7 @@ INSERT INTO t1(c2,c3) VALUES( ST_GeomFromText('POINT(60 50)'), ST_GeomFromText('LINESTRING(10 15,20 44,35 32)')); +BEGIN; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; @@ -106,6 +109,7 @@ INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +COMMIT; FLUSH TABLE t1 FOR EXPORT; --echo # List before copying files diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index f196840ad55..ad8b3b01dfe 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -677,16 +677,18 @@ DROP TABLE t1; # CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); +insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c @@ -696,8 +698,8 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 -1 SIMPLE t2 ref a a 515 const 1 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 101 Using where; Using filesort +1 SIMPLE t2 ref a a 515 test.t1.a 10 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; b c diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 30003146da7..7194b9abcf6 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -138,14 +138,13 @@ where e.mgr = a.emp_id select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 -4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.emp.emp_id 2 100.00 FirstMatch(emp) 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 2 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `ancestors`.`emp_id` = `test`.`emp`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' with recursive ancestors as diff --git a/sql/filesort_utils.cc b/sql/filesort_utils.cc index 5bd552f76be..27f37d1d507 100644 --- a/sql/filesort_utils.cc +++ b/sql/filesort_utils.cc @@ -52,9 +52,9 @@ double get_merge_many_buffs_cost_fast(ha_rows num_rows, // Calculate CPU cost of sorting buffers. total_cost= - ( num_buffers * num_keys_per_buffer * log(1.0 + num_keys_per_buffer) + - last_n_elems * log(1.0 + last_n_elems) ) - / TIME_FOR_COMPARE_ROWID; + ((num_buffers * num_keys_per_buffer * log(1.0 + num_keys_per_buffer) + + last_n_elems * log(1.0 + last_n_elems)) / + TIME_FOR_COMPARE_ROWID); // Simulate behavior of merge_many_buff(). while (num_buffers >= MERGEBUFF2) diff --git a/sql/handler.cc b/sql/handler.cc index 0d2603ac4aa..c44e61ade87 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2819,7 +2819,7 @@ double handler::keyread_time(uint index, uint ranges, ha_rows rows) if (ranges) { uint keys_per_block= (uint) (stats.block_size*3/4/len+1); - ulonglong blocks= !rows ? 0 : (rows-1) / keys_per_block + 1; + ulonglong blocks= (rows+ keys_per_block- 1)/keys_per_block; cost+= blocks; } return cost; @@ -2972,6 +2972,8 @@ int handler::ha_close(void) /* Detach from ANALYZE tracker */ tracker= NULL; + /* We use ref as way to check that open succeded */ + ref= 0; DBUG_ASSERT(m_lock_type == F_UNLCK); DBUG_ASSERT(inited == NONE); diff --git a/sql/handler.h b/sql/handler.h index edd8cc99f66..c3de2cb4752 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3258,6 +3258,11 @@ public: DBUG_ASSERT(m_lock_type == F_UNLCK); DBUG_ASSERT(inited == NONE); } + /* To check if table has been properely opened */ + bool is_open() + { + return ref != 0; + } virtual handler *clone(const char *name, MEM_ROOT *mem_root); bool clone_handler_for_update(); void delete_update_handler(); @@ -3461,7 +3466,10 @@ public: reset_statistics(); } virtual double scan_time() - { return ulonglong2double(stats.data_file_length) / IO_SIZE + 2; } + { + return ((ulonglong2double(stats.data_file_length) / stats.block_size + 2) * + avg_io_cost()); + } virtual double key_scan_time(uint index) { @@ -4918,6 +4926,7 @@ public: ha_share= arg_ha_share; return false; } + void set_table(TABLE* table_arg) { table= table_arg; } int get_lock_type() const { return m_lock_type; } public: /* XXX to be removed, see ha_partition::partition_ht() */ diff --git a/sql/item_func.cc b/sql/item_func.cc index b75bae596e8..ead72007518 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -5901,7 +5901,7 @@ bool Item_func_match::init_search(THD *thd, bool no_order) { DBUG_ENTER("Item_func_match::init_search"); - if (!table->file->get_table()) // the handler isn't opened yet + if (!table->file->is_open()) DBUG_RETURN(0); /* Check if init_search() has been called before */ diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 8d60b565d77..a2d15ff424f 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -112,7 +112,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, if (table->file->is_clustering_key(keyno)) len= table->s->stored_rec_length; /* Assume block is 75 % full */ - uint avg_block_records= ((uint) (table->file->stats.block_size*3/4))/len + 1; + uint avg_block_records= ((uint) (stats.block_size*3/4))/len + 1; uint limit= thd->variables.eq_range_index_dive_limit; bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, seq_init_param, @@ -218,7 +218,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, */ prev_range_last_block_records+= rows; DBUG_ASSERT(prev_range_last_block_records < - table->file->stats.block_size); + stats.block_size); } else { @@ -323,7 +323,7 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, uint limited_ranges= (uint) MY_MIN((ulonglong) n_ranges, io_blocks); cost->cpu_cost= read_time(keyno, limited_ranges, total_rows); } - cost->cpu_cost+= ((double) total_rows / TIME_FOR_COMPARE + + cost->cpu_cost+= (rows2double(total_rows) / TIME_FOR_COMPARE + MULTI_RANGE_READ_SETUP_COST); } DBUG_PRINT("statistics", @@ -403,7 +403,7 @@ ha_rows handler::multi_range_read_info(uint keyno, uint n_ranges, uint n_rows, { cost->cpu_cost= read_time(keyno, n_ranges, (uint)n_rows); } - cost->cpu_cost+= (double) n_rows / TIME_FOR_COMPARE; + cost->cpu_cost+= rows2double(n_rows) / TIME_FOR_COMPARE; return 0; } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e5d813dc301..7390345ed29 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2671,7 +2671,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, scan_time= read_time= DBL_MAX; else { - scan_time= ((double) records) / TIME_FOR_COMPARE; + scan_time= rows2double(records) / TIME_FOR_COMPARE; /* The 2 is there to prefer range scans to full table scans. This is mainly to make the test suite happy as many tests has @@ -2822,7 +2822,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { int key_for_use= find_shortest_key(head, &head->covering_keys); double key_read_time= (head->file->key_scan_time(key_for_use) + - (double) records / TIME_FOR_COMPARE); + rows2double(records) / TIME_FOR_COMPARE); DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, " "read time %g", key_for_use, key_read_time)); @@ -5164,8 +5164,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, Add one ROWID comparison for each row retrieved on non-CPK scan. (it is done in QUICK_RANGE_SELECT::row_in_ranges) */ - double rid_comp_cost= static_cast<double>(non_cpk_scan_records) / - TIME_FOR_COMPARE_ROWID; + double rid_comp_cost= (rows2double(non_cpk_scan_records) / + TIME_FOR_COMPARE_ROWID); imerge_cost+= rid_comp_cost; trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan", rid_comp_cost); @@ -5471,7 +5471,7 @@ typedef struct st_common_index_intersect_info { PARAM *param; /* context info for range optimizations */ uint key_size; /* size of a ROWID element stored in Unique object */ - uint compare_factor; /* 1/compare - cost to compare two ROWIDs */ + double compare_factor; /* 1/compare - cost to compare two ROWIDs */ size_t max_memory_size; /* maximum space allowed for Unique objects */ ha_rows table_cardinality; /* estimate of the number of records in table */ double cutoff_cost; /* discard index intersects with greater costs */ @@ -6166,7 +6166,7 @@ bool check_index_intersect_extension(PARTIAL_INDEX_INTERSECT_INFO *curr, { uint *buff_elems= common_info->buff_elems; uint key_size= common_info->key_size; - uint compare_factor= common_info->compare_factor; + double compare_factor= common_info->compare_factor; size_t max_memory_size= common_info->max_memory_size; records_sent_to_unique+= ext_index_scan_records; @@ -14305,20 +14305,21 @@ void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, b-tree the number of comparisons will be larger. TODO: This cost should be provided by the storage engine. */ - const double tree_traversal_cost= + const double tree_traversal_cost= ceil(log(static_cast<double>(table_records))/ log(static_cast<double>(keys_per_block))) * - 1/double(2*TIME_FOR_COMPARE); + 1/(2*TIME_FOR_COMPARE); const double cpu_cost= num_groups * - (tree_traversal_cost + 1/double(TIME_FOR_COMPARE_IDX)); + (tree_traversal_cost + 1/TIME_FOR_COMPARE_IDX); *read_cost= io_cost + cpu_cost; *records= num_groups; DBUG_PRINT("info", - ("table rows: %lu keys/block: %u keys/group: %lu result rows: %lu blocks: %lu", - (ulong)table_records, keys_per_block, (ulong) keys_per_group, + ("table rows: %lu keys/block: %u keys/group: %lu " + "result rows: %lu blocks: %lu", + (ulong) table_records, keys_per_block, (ulong) keys_per_group, (ulong) *records, (ulong) num_blocks)); DBUG_VOID_RETURN; } diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 607d9a2d95a..38779619b82 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1420,9 +1420,10 @@ void get_delayed_table_estimates(TABLE *table, /* Calculate cost of scanning the temptable */ double data_size= COST_MULT(item->jtbm_record_count, hash_sj_engine->tmp_table->s->reclength); - /* Do like in handler::read_time */ - *scan_time= data_size/IO_SIZE + 2; -} + /* Do like in handler::scan_time() */ + *scan_time= ((data_size/table->file->stats.block_size+2) * + table->file->avg_io_cost()); +} /** @@ -3144,7 +3145,7 @@ bool Sj_materialization_picker::check_qep(JOIN *join, prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); prefix_cost= COST_ADD(prefix_cost, - prefix_rec_count / (double) TIME_FOR_COMPARE); + prefix_rec_count / TIME_FOR_COMPARE); //TODO: take into account join condition selectivity here } diff --git a/sql/sql_const.h b/sql/sql_const.h index c605f3d0bae..3f053a1606d 100644 --- a/sql/sql_const.h +++ b/sql/sql_const.h @@ -204,8 +204,8 @@ instead of reading with keys. The number says how many evaluation of the WHERE clause is comparable to reading one extra row from a table. */ -#define TIME_FOR_COMPARE 5 // 5 compares == one read -#define TIME_FOR_COMPARE_IDX 20 +#define TIME_FOR_COMPARE 5.0 // 5 WHERE compares == one read +#define TIME_FOR_COMPARE_IDX 20.0 #define IDX_BLOCK_COPY_COST ((double) 1 / TIME_FOR_COMPARE) #define IDX_LOOKUP_COST ((double) 1 / 8) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index d5120d95420..e546f3cfeca 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -4255,7 +4255,7 @@ void select_insert::abort_result_set() table will be assigned with view table structure, but that table will not be opened really (it is dummy to check fields types & Co). */ - if (table && table->file->get_table()) + if (table && table->file->is_open()) { bool changed, transactional_table; /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 750883581b4..f7660054b74 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -232,7 +232,7 @@ static bool test_if_cheaper_ordering(const JOIN_TAB *tab, uint *saved_best_key_parts= NULL); static int test_if_order_by_key(JOIN *join, ORDER *order, TABLE *table, uint idx, - uint *used_key_parts= NULL); + uint *used_key_parts); static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order, ha_rows select_limit, bool no_changes, const key_map *map); @@ -7196,7 +7196,7 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, { TABLE *table= s->table; double sel= table->cond_selectivity; - double table_records= (double) s->records; + double table_records= rows2double(s->records); dbl_records= table_records * sel; return dbl_records; } @@ -7227,6 +7227,61 @@ double matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint, } +/* + Calculate the cost of reading a set of rows trough an index + + Logically this is identical to the code in multi_range_read_info_const() + excepts the function also takes into account io_blocks and multiple + ranges. + + One main difference between the functions is that + multi_range_read_info_const() adds a very small cost per range + (IDX_LOOKUP_COST) and also MULTI_RANGE_READ_SETUP_COST, to ensure that + 'ref' is preferred slightly over ranges. +*/ + +double cost_for_index_read(const THD *thd, const TABLE *table, uint key, + ha_rows records, ha_rows worst_seeks) +{ + DBUG_ENTER("cost_for_index_read"); + double cost; + handler *file= table->file; + + set_if_smaller(records, (ha_rows) thd->variables.max_seeks_for_key); + if (file->is_clustering_key(key)) + cost= file->read_time(key, 1, records); + else + if (table->covering_keys.is_set(key)) + cost= file->keyread_time(key, 1, records); + else + cost= ((file->keyread_time(key, 0, records) + + file->read_time(key, 1, MY_MIN(records, worst_seeks)))); + + DBUG_PRINT("statistics", ("cost: %.3f", cost)); + DBUG_RETURN(cost); +} + + +/* + Adjust cost from table->quick_costs calculated by + multi_range_read_info_const() to be comparable with cost_for_index_read() + + This functions is needed because best_access_patch doesn't add + TIME_FOR_COMPARE to it's costs until very late. + Preferably we should fix so that all costs are comparably. + (All compared costs should include TIME_FOR_COMPARE for all found + rows). +*/ + +double adjust_quick_cost(double quick_cost, ha_rows records) +{ + double cost= (quick_cost - MULTI_RANGE_READ_SETUP_COST - + rows2double(records)/TIME_FOR_COMPARE); + DBUG_ASSERT(cost > 0.0); + return cost; +} + + /** Find the best access path for an extension of a partial execution plan and add this path to the plan. @@ -7448,12 +7503,13 @@ best_access_path(JOIN *join, (!(key_flags & HA_NULL_PART_KEY) || // (2) all_key_parts == notnull_part)) // (3) { + + /* TODO: Adjust cost for covering and clustering key */ type= JT_EQ_REF; trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); if (!found_ref && table->quick_keys.is_set(key)) - tmp= ((double) table->quick_costs[key] - - MULTI_RANGE_READ_SETUP_COST); + tmp= adjust_quick_cost(table->quick_costs[key], 1); else tmp= table->file->avg_io_cost(); tmp*= prev_record_reads(join_positions, idx, found_ref); @@ -7487,12 +7543,8 @@ best_access_path(JOIN *join, { records= (double) table->quick_rows[key]; trace_access_idx.add("used_range_estimates", true); - /* - Use calculated cost, but ensure we prefer ref before - range - */ - tmp= ((double) table->quick_costs[key] - - MULTI_RANGE_READ_SETUP_COST); + tmp= adjust_quick_cost(table->quick_costs[key], + table->quick_rows[key]); goto got_cost; } else @@ -7527,12 +7579,11 @@ best_access_path(JOIN *join, */ if (table->quick_keys.is_set(key) && (const_part & - (((key_part_map)1 << table->quick_key_parts[key])-1)) == + (((key_part_map)1 << table->quick_key_parts[key])-1)) == (((key_part_map)1 << table->quick_key_parts[key])-1) && table->quick_n_ranges[key] == 1 && records > (double) table->quick_rows[key]) { - records= (double) table->quick_rows[key]; trace_access_idx.add("used_range_estimates", true); } @@ -7552,13 +7603,8 @@ best_access_path(JOIN *join, } } /* Limit the number of matched rows */ - tmp= records; - set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(key)) - tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(key, 1, - (ha_rows) MY_MIN(tmp,s->worst_seeks)); + tmp= cost_for_index_read(thd, table, key, (ha_rows) records, + (ha_rows) s->worst_seeks); got_cost: tmp= COST_MULT(tmp, record_count); } @@ -7621,8 +7667,11 @@ best_access_path(JOIN *join, table->quick_key_parts[key] == max_key_part && //(C2) table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part)) //(C3) { - tmp= records= (double) table->quick_rows[key]; + records= (double) table->quick_rows[key]; + tmp= adjust_quick_cost(table->quick_costs[key], + table->quick_rows[key]); trace_access_idx.add("used_range_estimates", true); + goto got_cost2; } else { @@ -7645,24 +7694,23 @@ best_access_path(JOIN *join, cheaper in some cases ? TODO: figure this out and adjust the plan choice if needed. */ - if (!found_ref && table->quick_keys.is_set(key) && // (1) - table->quick_key_parts[key] > max_key_part && // (2) - records < (double)table->quick_rows[key]) // (3) - { - trace_access_idx.add("used_range_estimates", true); - records= (double)table->quick_rows[key]; - } - else + if (table->quick_keys.is_set(key)) { - if (table->quick_keys.is_set(key) && - table->quick_key_parts[key] < max_key_part) + if (table->quick_key_parts[key] >= max_key_part) // (2) { - trace_access_idx.add("chosen", false); - cause= "range uses more keyparts"; + if (!found_ref && // (1) + records < (double) table->quick_rows[key]) // (3) + { + trace_access_idx.add("used_range_estimates", true); + records= (double) table->quick_rows[key]; + } + } + else /* (table->quick_key_parts[key] < max_key_part) */ + { + trace_access_idx.add("chosen", true); + cause= "range uses less keyparts"; } } - - tmp= records; } else { @@ -7686,27 +7734,25 @@ best_access_path(JOIN *join, rec_per_key=(double) s->records/rec+1; if (!s->records) - tmp = 0; + records= 0; else if (rec_per_key/(double) s->records >= 0.01) - tmp = rec_per_key; + records= rec_per_key; else { double a=s->records*0.01; if (keyinfo->user_defined_key_parts > 1) - tmp= (max_key_part * (rec_per_key - a) + + records= (max_key_part * (rec_per_key - a) + a*keyinfo->user_defined_key_parts - rec_per_key)/ (keyinfo->user_defined_key_parts-1); else - tmp= a; - set_if_bigger(tmp,1.0); + records= a; + set_if_bigger(records, 1.0); } - records = (ulong) tmp; } if (ref_or_null_part) { - /* We need to do two key searches to find key */ - tmp *= 2.0; + /* We need to do two key searches to find row */ records *= 2.0; } @@ -7725,22 +7771,21 @@ best_access_path(JOIN *join, if (table->quick_keys.is_set(key) && table->quick_key_parts[key] <= max_key_part && const_part & - ((key_part_map)1 << table->quick_key_parts[key]) && + ((key_part_map)1 << table->quick_key_parts[key]) && table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part & const_part) && records > (double) table->quick_rows[key]) { - tmp= records= (double) table->quick_rows[key]; + records= (double) table->quick_rows[key]; } } /* Limit the number of matched rows */ + tmp= records; set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(key)) - tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(key, 1, - (ha_rows) MY_MIN(tmp,s->worst_seeks)); + tmp= cost_for_index_read(thd, table, key, (ha_rows) tmp, + (ha_rows) s->worst_seeks); + got_cost2: tmp= COST_MULT(tmp, record_count); } else @@ -7773,10 +7818,10 @@ best_access_path(JOIN *join, } trace_access_idx.add("rows", records).add("cost", tmp); - if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) + if (tmp + 0.0001 < best_time - records/TIME_FOR_COMPARE) { trace_access_idx.add("chosen", true); - best_time= COST_ADD(tmp, records/(double) TIME_FOR_COMPARE); + best_time= COST_ADD(tmp, records/TIME_FOR_COMPARE); best= tmp; best_records= records; best_key= start_key; @@ -7819,7 +7864,7 @@ best_access_path(JOIN *join, use_cond_selectivity); tmp= s->quick ? s->quick->read_time : s->scan_time(); - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_ADD(tmp, cmp_time); /* We read the table as many times as join buffer becomes full. */ @@ -7910,7 +7955,7 @@ best_access_path(JOIN *join, access (see first else-branch below), but we don't take it into account here for range/index_merge access. Find out why this is so. */ - double cmp_time= (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->found_records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_MULT(record_count, COST_ADD(s->quick->read_time, cmp_time)); @@ -7957,7 +8002,7 @@ best_access_path(JOIN *join, - read the whole table record - skip rows which does not satisfy join condition */ - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_MULT(record_count, COST_ADD(tmp,cmp_time)); } else @@ -7973,7 +8018,7 @@ best_access_path(JOIN *join, we read the table (see flush_cached_records for details). Here we take into account cost to read and skip these records. */ - double cmp_time= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + double cmp_time= (s->records - rnd_records)/TIME_FOR_COMPARE; tmp= COST_ADD(tmp, cmp_time); } } @@ -8000,10 +8045,10 @@ best_access_path(JOIN *join, trace_access_scan.add("cost", tmp); if (best == DBL_MAX || - COST_ADD(tmp, record_count/(double) TIME_FOR_COMPARE*rnd_records) < + COST_ADD(tmp, record_count/TIME_FOR_COMPARE*rnd_records) < (best_key->is_for_hash_join() ? best_time : COST_ADD(best - best_filter_cmp_gain, - record_count/(double) TIME_FOR_COMPARE*records))) + record_count/TIME_FOR_COMPARE*records))) { /* If the table has a range (s->quick is set) make_join_select() @@ -8555,7 +8600,7 @@ optimize_straight_join(JOIN *join, table_map join_tables) : 0; read_time+= COST_ADD(read_time - filter_cmp_gain, COST_ADD(position->read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); advance_sj_state(join, join_tables, idx, &record_count, &read_time, &loose_scan_pos); @@ -8748,7 +8793,7 @@ greedy_search(JOIN *join, record_count= COST_MULT(record_count, join->positions[idx].records_read); read_time= COST_ADD(read_time, COST_ADD(join->positions[idx].read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); remaining_tables&= ~(best_table->table->map); --size_remain; @@ -8858,7 +8903,7 @@ void JOIN::get_partial_cost_and_fanout(int end_tab_idx, record_count= COST_MULT(record_count, tab->records_read); read_time= COST_ADD(read_time, COST_ADD(tab->read_time, - record_count / (double) TIME_FOR_COMPARE)); + record_count / TIME_FOR_COMPARE)); if (tab->emb_sj_nest) sj_inner_fanout= COST_MULT(sj_inner_fanout, tab->records_read); } @@ -9497,7 +9542,7 @@ best_extension_by_limited_search(JOIN *join, COST_ADD(position->read_time - filter_cmp_gain, current_record_count / - (double) TIME_FOR_COMPARE)); + TIME_FOR_COMPARE)); if (unlikely(thd->trace_started())) { @@ -16902,7 +16947,7 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, reopt_remaining_tables &= ~rs->table->map; rec_count= COST_MULT(rec_count, pos.records_read); cost= COST_ADD(cost, pos.read_time); - cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE); + cost= COST_ADD(cost, rec_count / TIME_FOR_COMPARE); //TODO: take into account join condition selectivity here double pushdown_cond_selectivity= 1.0; table_map real_table_bit= rs->table->map; @@ -18619,6 +18664,7 @@ bool Create_tmp_table::finalize(THD *thd, delete table->file; goto err; } + table->file->set_table(table); if (!m_using_unique_constraint) share->reclength+= m_group_null_items; // null flag is stored separately @@ -18643,6 +18689,7 @@ bool Create_tmp_table::finalize(THD *thd, share->reclength+= whole_null_pack_length; if (!share->reclength) share->reclength= 1; // Dummy select + share->stored_rec_length= share->reclength; /* Use packed rows if there is blobs or a lot of space to gain */ if (share->blob_fields || (string_total_length() >= STRING_TOTAL_LENGTH_TO_PACK_ROWS && @@ -22877,8 +22924,7 @@ static int test_if_order_by_key(JOIN *join, } ok: - if (used_key_parts != NULL) - *used_key_parts= key_parts; + *used_key_parts= key_parts; DBUG_RETURN(reverse); } @@ -22972,12 +23018,13 @@ test_if_subkey(ORDER *order, TABLE *table, uint ref, uint ref_key_parts, */ for (nr= 0 ; nr < table->s->keys ; nr++) { + uint not_used; if (usable_keys->is_set(nr) && table->key_info[nr].key_length < min_length && table->key_info[nr].user_defined_key_parts >= ref_key_parts && is_subkey(table->key_info[nr].key_part, ref_key_part, ref_key_part_end) && - test_if_order_by_key(NULL, order, table, nr)) + test_if_order_by_key(NULL, order, table, nr, ¬_used)) { min_length= table->key_info[nr].key_length; best= nr; @@ -28048,14 +28095,9 @@ static bool get_range_limit_read_cost(const JOIN_TAB *tab, if (ref_rows > 0) { - double tmp= (double)ref_rows; - /* Reuse the cost formula from best_access_path: */ - set_if_smaller(tmp, (double) tab->join->thd->variables.max_seeks_for_key); - if (table->covering_keys.is_set(keynr)) - tmp= table->file->keyread_time(keynr, 1, (ha_rows) tmp); - else - tmp= table->file->read_time(keynr, 1, - (ha_rows) MY_MIN(tmp,tab->worst_seeks)); + double tmp= cost_for_index_read(tab->join->thd, table, keynr, + ref_rows, + (ha_rows) tab->worst_seeks); if (tmp < best_cost) { best_cost= tmp; diff --git a/sql/table.cc b/sql/table.cc index 811ea28b49c..6d3adc4d67c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9185,8 +9185,8 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) /* we call it only when table is opened and it is "leaf" table*/ DBUG_ASSERT(table); lock_type= lock; - /* table->file->get_table() can be 0 for derived tables */ - if (table->file && table->file->get_table()) + /* If not derived tables */ + if (table->file && table->file->is_open()) table->file->set_lock_type(lock); if (is_merged_derived()) { diff --git a/sql/uniques.cc b/sql/uniques.cc index 4e327a4cc09..60918ea534e 100644 --- a/sql/uniques.cc +++ b/sql/uniques.cc @@ -161,7 +161,7 @@ inline double log2_n_fact(double x) static double get_merge_buffers_cost(uint *buff_elems, uint elem_size, uint *first, uint *last, - uint compare_factor) + double compare_factor) { uint total_buf_elems= 0; for (uint *pbuf= first; pbuf <= last; pbuf++) @@ -206,7 +206,7 @@ static double get_merge_buffers_cost(uint *buff_elems, uint elem_size, static double get_merge_many_buffs_cost(uint *buffer, uint maxbuffer, uint max_n_elems, uint last_n_elems, int elem_size, - uint compare_factor) + double compare_factor) { int i; double total_cost= 0.0; @@ -306,7 +306,7 @@ static double get_merge_many_buffs_cost(uint *buffer, double Unique::get_use_cost(uint *buffer, size_t nkeys, uint key_size, size_t max_in_memory_size, - uint compare_factor, + double compare_factor, bool intersect_fl, bool *in_memory) { size_t max_elements_in_tree; diff --git a/sql/uniques.h b/sql/uniques.h index 663d5c1682a..a78e626907f 100644 --- a/sql/uniques.h +++ b/sql/uniques.h @@ -72,13 +72,14 @@ public: bool get(TABLE *table); /* Cost of searching for an element in the tree */ - inline static double get_search_cost(ulonglong tree_elems, uint compare_factor) + inline static double get_search_cost(ulonglong tree_elems, + double compare_factor) { return log((double) tree_elems) / (compare_factor * M_LN2); } static double get_use_cost(uint *buffer, size_t nkeys, uint key_size, - size_t max_in_memory_size, uint compare_factor, + size_t max_in_memory_size, double compare_factor, bool intersect_fl, bool *in_memory); inline static int get_cost_calc_buff_size(size_t nkeys, uint key_size, size_t max_in_memory_size) diff --git a/storage/heap/ha_heap.h b/storage/heap/ha_heap.h index 319775d7222..50d3c0afb6c 100644 --- a/storage/heap/ha_heap.h +++ b/storage/heap/ha_heap.h @@ -65,9 +65,9 @@ public: double scan_time() { return (double) (stats.records+stats.deleted) / 20.0+10; } double read_time(uint index, uint ranges, ha_rows rows) - { return (double) rows / 20.0+1; } + { return (double) (rows +1)/ 20.0; } double keyread_time(uint index, uint ranges, ha_rows rows) - { return (double) rows / 20.0+1; } + { return (double) (rows + ranges) / 20.0 ; } double avg_io_cost() { return 0.05; } /* 1/20 */ int open(const char *name, int mode, uint test_if_locked); diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index e6137fac482..6519a2301e1 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -2089,7 +2089,8 @@ int ha_myisam::info(uint flag) ref_length= misam_info.reflength; share->db_options_in_use= misam_info.options; - stats.block_size= myisam_block_size; /* record block size */ + /* record block size. We adjust with IO_SIZE to not make it too small */ + stats.block_size= MY_MAX(myisam_block_size, IO_SIZE); if (table_share->tmp_table == NO_TMP_TABLE) mysql_mutex_lock(&table_share->LOCK_share); diff --git a/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result b/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result index efc6ccde500..4f285134c00 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/handler_basic.result @@ -34,6 +34,7 @@ Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 FLUSH STATUS; +SET GLOBAL rocksdb_force_flush_memtable_and_lzero_now=1; SELECT * FROM t1 WHERE b=6; id a b 6 NULL 6 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result index ce3d7d9147e..feeabd7ee79 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/records_in_range.result @@ -67,7 +67,7 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a,b from t1 where a = 700; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref ka ka 5 const 15000 100.00 +1 SIMPLE t1 ALL ka NULL NULL NULL 20000 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a from t1 where a in (700, 800); diff --git a/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test b/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test index 7b1652c759b..22b5d69780d 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/handler_basic.test @@ -28,6 +28,7 @@ SELECT * FROM t1 WHERE id=8; SHOW SESSION STATUS LIKE 'Handler_read%'; FLUSH STATUS; +SET GLOBAL rocksdb_force_flush_memtable_and_lzero_now=1; SELECT * FROM t1 WHERE b=6; SHOW SESSION STATUS LIKE 'Handler_read%'; diff --git a/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result index e646bc9bf38..194a6b31cba 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_join_nullable.result @@ -87,7 +87,7 @@ a b c a connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t0 left join `auto_test_remote`.`ta_r_auto_inc` t1 on ((t1.`a` = t0.`a`) and (t0.`a` is not null)) left join `auto_test_remote`.`ta_r_3` t2 on (t2.`c` = t1.`c`) left join `auto_test_remote`.`ta_r` t3 on ((t3.`b` = t2.`b`) and (t2.`b` is not null)) where 1 order by t0.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t0 left join `auto_test_remote`.`ta_r_auto_inc` t1 on (t1.`a` = t0.`a`) left join `auto_test_remote`.`ta_r_3` t2 on (t2.`c` = t1.`c`) left join `auto_test_remote`.`ta_r` t3 on (t3.`b` = t2.`b`) where 1 order by t0.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result index f3c6e189444..e6720c1113f 100644 --- a/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL NULL NULL 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null) and (t3.`b` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 join `auto_test_remote`.`ta_r` t0) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result index a05781cb6d6..a0b44c95cee 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join `auto_test_remote`.`ta_r_auto_inc` t2 on ((t2.`b` = t3.`b`) and (t3.`b` is not null)) left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join `auto_test_remote`.`ta_r_auto_inc` t2 on (t2.`b` = t3.`b`) left join `auto_test_remote`.`ta_r_3` t1 on (t1.`c` = t2.`c`) left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') diff --git a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result index 48cd9c2c75f..0ee74cae7a3 100644 --- a/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result +++ b/storage/spider/mysql-test/spider/r/direct_right_left_right_join_nullable.result @@ -87,7 +87,7 @@ NULL c 2000-01-03 00:00:00 3 connection child2_1; SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'; argument -select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`) and (t3.`b` is not null)) where 1 order by t3.`a` desc +select t0.`a` `a`,t2.`b` `b`,t2.`c` `c`,t3.`a` `a` from `auto_test_remote`.`ta_r_no_idx` t3 left join (`auto_test_remote`.`ta_r_auto_inc` t2 join `auto_test_remote`.`ta_r_3` t1 left join `auto_test_remote`.`ta_r` t0 on ((t0.`a` = t1.`a`) and (t1.`a` is not null))) on ((t2.`b` = t3.`b`) and (t2.`c` = t1.`c`)) where 1 order by t3.`a` desc SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %' SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_r ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') |