diff options
Diffstat (limited to 'mysql-test/main/fast_prefix_index_fetch_innodb.result')
| -rw-r--r-- | mysql-test/main/fast_prefix_index_fetch_innodb.result | 401 |
1 files changed, 401 insertions, 0 deletions
diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.result b/mysql-test/main/fast_prefix_index_fetch_innodb.result new file mode 100644 index 00000000000..c6d96389b08 --- /dev/null +++ b/mysql-test/main/fast_prefix_index_fetch_innodb.result @@ -0,0 +1,401 @@ +drop table if exists prefixinno; +set global innodb_prefix_index_cluster_optimization = ON; +show variables like 'innodb_prefix_index_cluster_optimization'; +Variable_name Value +innodb_prefix_index_cluster_optimization ON +# Create a table with a large varchar field that we index the prefix +# of and ensure we only trigger cluster lookups when we expect it. +create table prefixinno ( +id int not null, +fake_id int not null, +bigfield varchar(4096), +primary key(id), +index bigfield_idx (bigfield(32)), +index fake_id_bigfield_prefix (fake_id, bigfield(32)) +) engine=innodb; +insert into prefixinno values (1, 1001, repeat('a', 1)), +(8, 1008, repeat('b', 8)), +(24, 1024, repeat('c', 24)), +(31, 1031, repeat('d', 31)), +(32, 1032, repeat('x', 32)), +(33, 1033, repeat('y', 33)), +(128, 1128, repeat('z', 128)); +select * from prefixinno; +id fake_id bigfield +1 1001 a +8 1008 bbbbbbbb +24 1024 cccccccccccccccccccccccc +31 1031 ddddddddddddddddddddddddddddddd +32 1032 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +33 1033 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +128 1128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +# Baseline sanity check: 0, 0. +select "no-op query"; +no-op query +no-op query +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible for optimization. +select id, bigfield from prefixinno where bigfield = repeat('d', 31); +id bigfield +31 ddddddddddddddddddddddddddddddd +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Eligible for optimization, access via fake_id only. +select id, bigfield from prefixinno where fake_id = 1031; +id bigfield +31 ddddddddddddddddddddddddddddddd +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible for optimization, access via fake_id of big row. +select id, bigfield from prefixinno where fake_id = 1033; +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible for optimization. +select id, bigfield from prefixinno where bigfield = repeat('x', 32); +id bigfield +32 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible for optimization. +select id, bigfield from prefixinno where bigfield = repeat('y', 33); +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible, should not increment lookup counter. +select id, bigfield from prefixinno where bigfield = repeat('b', 8); +id bigfield +8 bbbbbbbb +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Eligible, should not increment lookup counter. +select id, bigfield from prefixinno where bigfield = repeat('c', 24); +id bigfield +24 cccccccccccccccccccccccc +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Should increment lookup counter. +select id, bigfield from prefixinno where bigfield = repeat('z', 128); +id bigfield +128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Disable optimization, confirm we still increment counter. +set global innodb_prefix_index_cluster_optimization = OFF; +select id, bigfield from prefixinno where fake_id = 1033; +id bigfield +33 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +drop table prefixinno; +# Multi-byte handling case +set global innodb_prefix_index_cluster_optimization = ON; +SET NAMES utf8mb4; +CREATE TABLE t1( +f1 varchar(10) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_BIN, +INDEX (f1(3)))ENGINE=INNODB; +INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢'); +INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑'); +INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢'); +# Eligible - record length is shorter than prefix +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a'; +f1 +a +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%'; +f1 +cccc +cčc +select @cluster_lookups; +@cluster_lookups +3 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až'; +f1 +až +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்'; +f1 +தமிழ் +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%'; +f1 +ggᵷg +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%'; +f1 +😊me +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢'; +f1 +ls¢ +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%'; +f1 +¢¢ +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%'; +f1 +🐱🌑 +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%'; +f1 +🌑 +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +2 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%'; +f1 +🌒 +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +2 +DROP TABLE t1; +# Multi-byte with minimum character length > 1 bytes +CREATE TABLE t1( +f1 varchar(10) CHARACTER SET UTF16 COLLATE UTF16_BIN, +INDEX (f1(3)))ENGINE=INNODB; +INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢'); +INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑'); +INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢'); +# Eligible - record length is shorter than prefix +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a'; +f1 +a +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%'; +f1 +cccc +cčc +select @cluster_lookups; +@cluster_lookups +3 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až'; +f1 +až +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்'; +f1 +தமிழ் +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%'; +f1 +ggᵷg +select @cluster_lookups; +@cluster_lookups +2 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%'; +f1 +😊me +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Not eligible - record length longer than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢'; +f1 +ls¢ +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%'; +f1 +¢¢ +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Eligible - record length shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%'; +f1 +🐱🌑 +select @cluster_lookups; +@cluster_lookups +2 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +# Eligible - record length is shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%'; +f1 +🌑 +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +2 +# Eligible - record length is shorter than prefix length +SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%'; +f1 +🌒 +select @cluster_lookups; +@cluster_lookups +1 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +DROP TABLE t1; +CREATE TABLE t1( +col1 INT, +col2 BLOB DEFAULT NULL, +INDEX `idx1`(col2(4), col1))ENGINE=INNODB; +INSERT INTO t1 VALUES (2, 'test'), (3, repeat('test1', 2000)); +INSERT INTO t1(col1) VALUES(1); +# Eligible - record length is shorter than prefix length +SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL; +col1 +1 +select @cluster_lookups; +@cluster_lookups +0 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +1 +# Not eligible - record length longer than prefix index +SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%'; +col1 +3 +select @cluster_lookups; +@cluster_lookups +2 +select @cluster_lookups_avoided; +@cluster_lookups_avoided +0 +DROP TABLE t1; +set global innodb_prefix_index_cluster_optimization = OFF; |