diff options
| author | Monty <monty@mariadb.org> | 2020-03-13 15:28:42 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2020-03-17 02:16:48 +0200 |
| commit | 1242eb3d32f2863f847aa96a10e2ab983a1a643b (patch) | |
| tree | b7e28488f2f6fa75a6a83e3b18c95ef8b53b4e8c /mysql-test | |
| parent | 96b472c0ae798da43ca9f4735dfafe35b2f38fda (diff) | |
| download | mariadb-git-1242eb3d32f2863f847aa96a10e2ab983a1a643b.tar.gz | |
Removed double records_in_range calls from multi_range_read_info_const
This was to remove a performance regression between 10.3 and 10.4
In 10.5 we will have a better implementation of records_in_range
that will enable us to get more statistics.
This change was not done in 10.4 because the 10.5 will be part of
a larger change that is not suitable for the GA 10.4 version
Other things:
- Changed default handler block_size to 8192 to fix things statistics
for engines that doesn't set the block size.
- Fixed a bug in spider when using multiple part const ranges
(Patch from Kentoku)
Diffstat (limited to 'mysql-test')
36 files changed, 249 insertions, 228 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index 1ff34a936c6..aa09b0025d4 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -693,6 +693,7 @@ INSERT INTO t1 VALUES ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); SET SESSION optimizer_switch='index_condition_pushdown=off'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' @@ -702,6 +703,7 @@ SELECT * FROM t1 ORDER BY a; SET SESSION optimizer_switch='index_condition_pushdown=on'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index 79f5f555681..9a3c1dba045 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -717,11 +717,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c_timestamp NULL NULL NULL 20 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c_timestamp NULL NULL NULL 20 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index d65acd5a48d..949bc9a00d8 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -658,7 +658,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -670,7 +670,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index cb9cdfa0214..290aa492a98 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -2968,6 +2968,7 @@ CREATE INDEX t3_quality ON t3(quality); INSERT INTO t3 VALUES (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); +insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3); CREATE TABLE t4( formatid int NOT NULL , name varchar(60) NOT NULL default '', @@ -3068,10 +3069,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ref PRIMARY,t5_formattypeid t5_formattypeid 4 const 1 1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) 1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index edee05cb6a5..ce84ccb56fc 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -1117,6 +1117,7 @@ CREATE INDEX t3_quality ON t3(quality); INSERT INTO t3 VALUES (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); +insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3); CREATE TABLE t4( formatid int NOT NULL , diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index f341c4be2c6..12f6c36edd8 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -216,7 +216,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index explain select 1 from t1 where id =2 or id=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index explain select name from t1 where id =2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index 36c75ad4a5d..322a2be6a4e 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -440,25 +440,25 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 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 NULL NULL 2097152 1024 4 # 0 24 0 26 6 +default NULL NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t2 where a='zzzz'; 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 NULL NULL 2097152 1024 4 # 0 32 0 32 9 +default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; 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 NULL NULL 2097152 1024 4 # 0 32 0 32 9 +default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 set global key_cache_segments=2; select @@key_cache_segments; @@ -488,7 +488,7 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 @@ -497,13 +497,13 @@ variable_value < @key_blocks_unused 1 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 2097152 1024 4 # 0 24 0 26 6 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; 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 2097152 1024 4 # 0 24 0 26 6 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 set global key_cache_segments=1; select @@key_cache_segments; @@ -533,7 +533,7 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 @@ -542,13 +542,13 @@ variable_value = @key_blocks_unused 1 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 1 NULL 2097152 1024 4 # 0 24 0 26 6 +default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; 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 1 NULL 2097152 1024 4 # 0 24 0 26 6 +default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 flush tables; flush status; @@ -586,7 +586,7 @@ update t1 set p=3 where p=1; update t2 set i=2 where i=1; 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 4 # 0 24 0 26 6 +default 2 NULL 32768 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 0 0 0 0 insert into t1(a) select a from t1; insert into t1(a) select a from t1; @@ -606,7 +606,7 @@ insert into t2(i,a) select i,a from t2; insert into t2(i,a) select i,a from t2; 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 6735 # 3684 103 +default 2 NULL 32768 1024 # # 0 6733 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 select * from t1 where p between 1010 and 1020 ; p a @@ -625,7 +625,7 @@ p i a 1020 3 zzzz 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 6756 # 3684 103 +default 2 NULL 32768 1024 # # 0 6750 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 flush tables; flush status; @@ -633,7 +633,7 @@ update t1 set a='zzzz' where a='qqqq'; update t2 set i=1 where i=2; 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 3078 18 1552 18 +default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=256*1024; select @@keycache1.key_buffer_size; @@ -645,7 +645,7 @@ select @@keycache1.key_cache_segments; 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 3078 18 1552 18 +default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 0 0 0 0 select * from information_schema.key_caches where key_cache_name like "key%" @@ -685,13 +685,13 @@ count(*) 256 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 3178 24 1552 18 +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 18 3 0 0 +keycache1 7 NULL 262143 2048 # # 0 14 3 0 0 select * from information_schema.key_caches where key_cache_name like "key%" and 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 -keycache1 7 NULL 262143 2048 3 # 0 18 3 0 0 +keycache1 7 NULL 262143 2048 3 # 0 14 3 0 0 cache index t2 in keycache1; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK @@ -699,7 +699,7 @@ update t2 set p=p+3000, i=2 where a='qqqq'; select * from information_schema.key_caches where key_cache_name like "key%" and 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 -keycache1 7 NULL 262143 2048 25 # 0 2088 25 1071 19 +keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 set global keycache2.key_buffer_size=1024*1024; cache index t2 in keycache2; Table Op Msg_type Msg_text @@ -712,7 +712,7 @@ keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 select * from information_schema.key_caches where key_cache_name like "key%" and 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 -keycache1 7 NULL 262143 2048 25 # 0 2088 25 1071 19 +keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 cache index t2 in keycache1; Table Op Msg_type Msg_text @@ -751,80 +751,80 @@ a yyyy 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 3178 24 1552 18 +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 3231 43 1594 30 +keycache1 7 NULL 262143 2048 # # 0 3201 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'); 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 3178 24 1552 18 +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 6 6 3 3 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 262143 8192 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 insert into t2 values (8000, 3, 'yyyy'); 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 262143 8192 # # 0 6 5 3 3 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=64*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 65535 2048 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=0; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=0; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=128*1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 1 NULL 131072 8192 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=1024; 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 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 7 NULL 131068 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index e11438be48d..7f24cb4896d 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -752,12 +752,12 @@ 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 DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 1 Using where; Using filesort +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using filesort +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -781,8 +781,6 @@ Handler_read_rnd_next 1 # Status of testing query execution: Variable_name Value Handler_read_key 3 -Handler_read_rnd_next 1 -Sort_scan 1 INSERT INTO t1 VALUES (1), (2), (3), (-1), (-2), (-3); # diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 0fdc3f11627..7f34b274764 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter +1 SIMPLE t1 range PRIMARY,k1 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort DROP TABLE t1; # # @@ -651,7 +651,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -663,7 +663,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/main/negation_elimination.result b/mysql-test/main/negation_elimination.result index 6e40074c317..f9be5b55e46 100644 --- a/mysql-test/main/negation_elimination.result +++ b/mysql-test/main/negation_elimination.result @@ -192,7 +192,7 @@ a 15 explain select * from t1 where not(a < 15 and a > 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where not(a < 15 and a > 5); a 0 @@ -208,7 +208,7 @@ a 19 explain select * from t1 where a = 2 or not(a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where a = 2 or not(a < 10); a 2 @@ -255,7 +255,7 @@ a 19 explain select * from t1 where a = 2 or not(a < 5 or a > 15); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where a = 2 or not(a < 5 or a > 15); a 2 @@ -289,7 +289,7 @@ a 19 explain select * from t1 where NULL or not(a < 15 and a > 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where NULL or not(a < 15 and a > 5); a 0 diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index 8eca97d5e70..55b2060475f 100644 --- a/mysql-test/main/null_key.result +++ b/mysql-test/main/null_key.result @@ -20,7 +20,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index explain select * from t1 where (a is null or a > 0 and a < 2) and b < 5 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 9 NULL 2 Using where; Using index +1 SIMPLE t1 index a,b a 9 NULL 12 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index @@ -32,7 +32,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index +1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 72cee2a428f..110ce60edfd 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2044,7 +2044,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": 231.72, + "cost": 229.72, "chosen": true }, { @@ -2226,7 +2226,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": 231.72, + "cost": 229.72, "chosen": true } ], @@ -2243,7 +2243,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": 231.72, + "cost_for_plan": 229.72, "chosen": true } } @@ -7568,7 +7568,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 10.955, + "cost": 8.9549, "chosen": true } ], @@ -7605,7 +7605,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1282.2, + "cost": 1273.2, "chosen": true } ], @@ -8009,7 +8009,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1282.2, + "cost": 1273.2, "chosen": true } ], diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 7f1bd5163f3..40d75d549ec 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -323,7 +323,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2862.1, + "cost": 2844.1, "chosen": true }, @@ -337,7 +337,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2862.1, + "cost": 2844.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": 2862.1, + "cost": 2844.1, "chosen": false, "cause": "cost" } @@ -501,7 +501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": true }, @@ -515,13 +515,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": false, "cause": "cost" } ], "index_to_merge": "key1", - "cumulated_cost": 170.53 + "cumulated_cost": 152.53 }, { @@ -538,7 +538,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": true }, @@ -552,16 +552,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": false, "cause": "cost" } ], "index_to_merge": "key3", - "cumulated_cost": 341.05 + "cumulated_cost": 305.05 } ], - "cost_of_reading_ranges": 341.05, + "cost_of_reading_ranges": 305.05, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index afcf0b03dae..4b047315bf5 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -121,9 +121,8 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 206.39, - "chosen": false, - "cause": "cost" + "cost": 203.39, + "chosen": true }, { "index": "key1", diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 268cac4daac..00b131a3252 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -714,7 +714,7 @@ FieldKey LongVal StringVal 3 3 3 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using index condition +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result index 5a7795394ec..ea12158862e 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -551,7 +551,7 @@ INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13), INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range c1 c1 5 NULL 4 Using where; Using index +1 SIMPLE t1 NULL index c1 c1 5 NULL 20 Using where; Using index FLUSH STATUS; SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 @@ -561,10 +561,10 @@ c1 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value -Handler_read_first 0 -Handler_read_key 2 +Handler_read_first 1 +Handler_read_key 0 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 20 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -572,7 +572,7 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 a range c1 c1 5 NULL 4 Using where; Using index +1 SIMPLE t2 a index c1 c1 5 NULL 20 Using where; Using index FLUSH STATUS; SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 @@ -582,10 +582,10 @@ c1 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value -Handler_read_first 0 -Handler_read_key 2 +Handler_read_first 1 +Handler_read_key 0 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 20 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index e2b409fa4ae..ccef491242b 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index 2efb10de694..4aad8134866 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index 4123ae575e0..7e060b35c53 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null @@ -287,13 +287,13 @@ set join_cache_level=6, optimizer_switch='mrr=on'; explain select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan # This will use "Using index condition(BKA)" explain select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan set join_cache_level=@tmp1, optimizer_switch=@tmp2; drop table t0,t1,t2; diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 261a05e7023..6eb7147b461 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -11,7 +11,7 @@ PARTITION BY HASH (a) PARTITIONS 1; INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020); EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by DROP TABLE t1; create table t1 (a DATETIME) partition by range (TO_DAYS(a)) diff --git a/mysql-test/main/preload.result b/mysql-test/main/preload.result index 529c7ac2690..7ed0c62f33a 100644 --- a/mysql-test/main/preload.result +++ b/mysql-test/main/preload.result @@ -55,14 +55,14 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 297 +Key_read_requests 294 Key_reads 60 select count(*) from t1 where b = 'test1'; count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 594 +Key_read_requests 588 Key_reads 60 flush tables; flush status; @@ -81,7 +81,7 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 1071 +Key_read_requests 1068 Key_reads 774 flush tables; flush status; @@ -105,7 +105,7 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 314 +Key_read_requests 311 Key_reads 75 flush tables; flush status; @@ -133,7 +133,7 @@ count(*) 2584 show status like "key_read%"; Variable_name Value -Key_read_requests 1272 +Key_read_requests 1266 Key_reads 821 flush tables; flush status; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index bb111f25a07..54ac0de0c0f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1183,6 +1183,7 @@ a varchar(32), index (a) ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; a B @@ -1268,6 +1269,7 @@ INSERT INTO t3 VALUES (16, 0), (17, 0), (18, 0), (19, 0), (20, 0); INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +insert into t1 select -a,b from t1; SELECT * FROM t1 WHERE 23 <= a AND a < 25 OR 25 < a AND b = 23 OR @@ -1425,7 +1427,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index +1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -3103,12 +3105,12 @@ insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + # expected type=range, rows=1487 , reason=using index dives analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 1487 1199.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 74.35 59.95 Using where; Using index insert into t2 values (200),(201); # expected type=range, rows=201 , reason=using index statistics analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index drop table t1,ten,t2; # # End of 10.2 tests diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index a52443990be..93305e72b22 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -960,6 +960,7 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; @@ -1077,7 +1078,7 @@ INSERT INTO t3 VALUES # To make range scan compelling to the optimizer INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; - +insert into t1 select -a,b from t1; # # Problem#1 Test queries. Will give missing results unless Problem#1 is fixed. diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index c286c49cac0..5ca1b6603c9 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1186,6 +1186,7 @@ a varchar(32), index (a) ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; a B @@ -1271,6 +1272,7 @@ INSERT INTO t3 VALUES (16, 0), (17, 0), (18, 0), (19, 0), (20, 0); INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +insert into t1 select -a,b from t1; SELECT * FROM t1 WHERE 23 <= a AND a < 25 OR 25 < a AND b = 23 OR @@ -1428,7 +1430,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index +1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -3100,12 +3102,12 @@ insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + # expected type=range, rows=1487 , reason=using index dives analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 1487 1199.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 74.35 59.95 Using where; Using index insert into t2 values (200),(201); # expected type=range, rows=201 , reason=using index statistics analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index drop table t1,ten,t2; # # End of 10.2 tests diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 5c3e5441b8b..f0bf2224c92 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -705,9 +705,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='FIN'; +AND Country='EST'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 2 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition +1 SIMPLE City index_merge Country,CountryPopulation,CountryName,CityName CountryName,CityName 38,35 NULL 28 Using sort_union(CountryName,CityName); Using where SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1109,33 +1109,33 @@ SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); Name Country Population -Toronto CAN 688275 -Vancouver CAN 514008 -Basel CHE 166700 -Peking CHN 7472000 -Praha CZE 1181126 -Dresden DEU 476668 Addis Abeba ETH 2495000 -Paris FRA 2125246 -Jakarta IDN 9604900 +Ankara TUR 3038159 Bangalore IND 2660088 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 Delhi IND 7206704 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Seoul KOR 9981619 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 Manila PHL 1581082 +Paris FRA 2125246 +Peking CHN 7472000 +Praha CZE 1181126 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 -Dakar SEN 785071 -Ankara TUR 3038159 -Lugansk UKR 469000 Seattle USA 563374 -Caracas VEN 1975294 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch='index_merge=off'; EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR @@ -1196,33 +1196,33 @@ SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); Name Country Population -Toronto CAN 688275 -Vancouver CAN 514008 -Basel CHE 166700 -Peking CHN 7472000 -Praha CZE 1181126 -Dresden DEU 476668 Addis Abeba ETH 2495000 -Paris FRA 2125246 -Jakarta IDN 9604900 +Ankara TUR 3038159 Bangalore IND 2660088 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 Delhi IND 7206704 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Seoul KOR 9981619 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 Manila PHL 1581082 +Paris FRA 2125246 +Peking CHN 7472000 +Praha CZE 1181126 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 -Dakar SEN 785071 -Ankara TUR 3038159 -Lugansk UKR 469000 Seattle USA 563374 -Caracas VEN 1975294 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch=@save_optimizer_switch; # # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n @@ -1674,7 +1674,9 @@ CREATE TABLE t1 ( f1 int, f2 int, f3 int, f4 int, PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1685,24 +1687,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 306 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 INSERT INTO t1 VALUES @@ -1718,24 +1720,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 16 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 320 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch=DEFAULT; diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index f4d9024fe08..5ed5f621ab6 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -1,5 +1,6 @@ --source include/default_optimizer_switch.inc --source include/default_charset.inc +--source include/have_sequence.inc --disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; @@ -387,7 +388,7 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') - AND Country='FIN'; + AND Country='EST'; # The following 4 queries check that the plans # for the previous 2 plans are valid @@ -628,7 +629,7 @@ SELECT * FROM City # -# LP bug #954262: index merge oover long disjunction in WHERE +# LP bug #954262: index merge over long disjunction in WHERE # set @save_optimizer_switch=@@optimizer_switch; @@ -667,6 +668,7 @@ let $cond = eval EXPLAIN SELECT Name, Country, Population FROM City WHERE $cond; +--sorted_result eval SELECT Name, Country, Population FROM City WHERE $cond; @@ -676,6 +678,7 @@ set optimizer_switch='index_merge=off'; eval EXPLAIN SELECT Name, Country, Population FROM City WHERE $cond; +--sorted_result eval SELECT Name, Country, Population FROM City WHERE $cond; @@ -1087,7 +1090,9 @@ CREATE TABLE t1 ( PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; @@ -1099,22 +1104,22 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); INSERT INTO t1 VALUES @@ -1130,22 +1135,22 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch=DEFAULT; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index e2fd8020049..b82b6942c5b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -711,9 +711,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='FIN'; +AND Country='EST'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 2 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -1116,32 +1116,32 @@ SELECT Name, Country, Population FROM City WHERE (Name='Seattle' AND Country='USA'); Name Country Population Addis Abeba ETH 2495000 -Manila PHL 1581082 -Jakarta IDN 9604900 -Delhi IND 7206704 +Ankara TUR 3038159 Bangalore IND 2660088 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Toronto CAN 688275 -Vancouver CAN 514008 -Peking CHN 7472000 -Seoul KOR 9981619 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 +Delhi IND 7206704 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 +Manila PHL 1581082 Paris FRA 2125246 -Dresden DEU 476668 -Dakar SEN 785071 -Basel CHE 166700 +Peking CHN 7472000 Praha CZE 1181126 -Ankara TUR 3038159 -Lugansk UKR 469000 -Caracas VEN 1975294 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 Seattle USA 563374 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch='index_merge=off'; EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR @@ -1172,7 +1172,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1680,7 +1680,9 @@ CREATE TABLE t1 ( f1 int, f2 int, f3 int, f4 int, PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1691,24 +1693,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 306 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 2 Using union(PRIMARY,f3); Using where +1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 5 Using union(PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 INSERT INTO t1 VALUES @@ -1724,24 +1726,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 16 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 320 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 2 Using union(PRIMARY,f3); Using where +1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 5 Using union(PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch=DEFAULT; @@ -1914,23 +1916,23 @@ Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); ID Name Country Population 175 Antwerpen BEL 446525 -176 Gent BEL 224180 +2808 Bergen NOR 230948 3068 Berlin DEU 3386667 3087 Bonn DEU 301048 +2918 Braga PRT 90535 +176 Gent BEL 224180 3242 Lahti FIN 96921 -2974 Paris FRA 2125246 +3580 Moscow RUS 8389200 1466 Napoli ITA 1002619 -1474 Venezia ITA 277305 -2808 Bergen NOR 230948 2807 Oslo NOR 508726 -2928 Warszawa POL 1615369 -2931 Wroclaw POL 636765 -2918 Braga PRT 90535 +2974 Paris FRA 2125246 2915 Porto PRT 273060 -3580 Moscow RUS 8389200 3581 St Petersburg RUS 4694000 3048 Stockholm SWE 750348 3051 Uppsala SWE 189569 +1474 Venezia ITA 277305 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 explain select * from City where Country='FIN' AND Name IN ('Lahti','Imatra') OR @@ -1944,7 +1946,7 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition +1 SIMPLE City range CountryName,Name Name 35 NULL 20 Using index condition; Using where DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; set global innodb_stats_persistent= @innodb_stats_persistent_save; 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/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 589144f1238..e2c9adbfc37 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1753,6 +1753,7 @@ insert into t1(`id`,`local_name`) values (11,'Rollover - Internet Payday'), (12,'AL Monthly Installment'), (13,'AL Semi-Monthly Installment'); +insert into t1 select seq,"ignore" from seq_1000_to_1100; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1766,8 +1767,8 @@ OR (t.id IN (0,4,12,13,1,10,3,11)) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index -2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 +1 PRIMARY t index PRIMARY PRIMARY 4 NULL 114 Using where; Using index +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 114 2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where SELECT SQL_NO_CACHE t.id diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test index 96798ca0e48..4b768652670 100644 --- a/mysql-test/main/subselect_sj2_mat.test +++ b/mysql-test/main/subselect_sj2_mat.test @@ -2,6 +2,7 @@ # Run subselect_sj2.test with subquery materialization. # --source include/default_optimizer_switch.inc +--source include/have_sequence.inc set optimizer_switch='materialization=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; @@ -338,7 +339,7 @@ insert into t1(`id`,`local_name`) values (11,'Rollover - Internet Payday'), (12,'AL Monthly Installment'), (13,'AL Semi-Monthly Installment'); - +insert into t1 select seq,"ignore" from seq_1000_to_1100; ANALYZE TABLE t1; explain diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result index bc03e1b251d..f054df036e9 100644 --- a/mysql-test/main/table_elim.result +++ b/mysql-test/main/table_elim.result @@ -419,7 +419,7 @@ select t1.* from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where drop table t1, t2; # # LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result index cfb47f7b850..967fb37b230 100644 --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -625,6 +625,9 @@ id txt 3 NULL explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL txt_index NULL NULL NULL 6 Using where +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 3 Using where select * from t1 where txt='Chevy '; id txt diff --git a/mysql-test/main/type_blob.test b/mysql-test/main/type_blob.test index df565b187b4..7801280a8d2 100644 --- a/mysql-test/main/type_blob.test +++ b/mysql-test/main/type_blob.test @@ -362,6 +362,7 @@ insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL), ('Honda'), ('Subaru'), ('Honda'); select * from t1 where txt='Chevy' or txt is NULL; explain select * from t1 where txt='Chevy' or txt is NULL; +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; select * from t1 where txt='Chevy '; select * from t1 where txt='Chevy ' or txt='Chevy'; select * from t1 where txt='Chevy' or txt='Chevy '; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index b83dba5345e..acd66549d42 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -3034,7 +3034,7 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx1,idx2 PRIMARY 4 NULL 18 Using where +1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 0a7105fcfad..96793beae8a 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -409,7 +409,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,k1 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where DROP TABLE t1; # # @@ -653,7 +653,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -665,7 +665,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 7a4283c4cc5..913bf4efbdf 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -400,7 +400,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 5 Using index condition +1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 4 NULL 5 Using index condition |