diff options
Diffstat (limited to 'mysql-test/main/func_in.result')
| -rw-r--r-- | mysql-test/main/func_in.result | 78 |
1 files changed, 78 insertions, 0 deletions
diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index b3865bab..1ddb5257 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -971,6 +971,84 @@ c1 9223372036854775808 drop table `a`; # +# MDEV-18319 BIGINT UNSIGNED Performance issue +# +CREATE OR REPLACE TABLE t1 ( +id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY +); +FOR i IN 0..255 +DO +INSERT INTO t1 VALUES (); +END FOR +$$ +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; +MIN(id) MAX(id) COUNT(*) +1 256 256 +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806, 9223372036854775807); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807, 9223372036854775808); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 8 NULL 2 Using where; Using index +DROP TABLE t1; +# +# MDEV-18898 SELECT using wrong index when using operator IN with mixed types +# +CREATE TEMPORARY TABLE t1 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, +PRIMARY KEY (`id`), +UNIQUE KEY `name` (`name`) +); +FOR i IN 1..255 +DO +INSERT INTO t1 VALUES (i, MD5(i)); +END FOR +$$ +# +# Constants alone +# +ANALYZE SELECT id, name FROM t1 WHERE id = 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 100.00 NULL +ANALYZE SELECT id, name FROM t1 WHERE id = '2'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 100.00 NULL +# +# Two constants using IN +# +ANALYZE SELECT id, name FROM t1 WHERE id IN (1, 2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id IN ('1', 2) /* Used a wrong index */; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id IN (1, '2') /* Used a wrong index */; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id IN ('1', '2'); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +# +# Two constants using OR +# +ANALYZE SELECT id, name FROM t1 WHERE id = 1 OR id = 2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id = '1' OR id = '2'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id = 1 OR id = '2'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +ANALYZE SELECT id, name FROM t1 WHERE id = '1' OR id = 2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 2.00 100.00 100.00 Using index condition +DROP TABLE t1; +# # End of 10.5 tests # # |