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;