1 files changed, 668 insertions, 0 deletions
diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.test b/mysql-test/main/fast_prefix_index_fetch_innodb.test
new file mode 100644
index 00000000000..c3b3440d82d
--- /dev/null
+++ b/mysql-test/main/fast_prefix_index_fetch_innodb.test
@@ -0,0 +1,668 @@
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists prefixinno;
+--enable_warnings
+
+set global innodb_prefix_index_cluster_optimization = ON;
+show variables like 'innodb_prefix_index_cluster_optimization';
+
+--echo # Create a table with a large varchar field that we index the prefix
+--echo # 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;
+
+let $show_count_statement = show status like 'innodb_secondary_index_triggered_cluster_reads';
+let $show_opt_statement = show status like 'innodb_secondary_index_triggered_cluster_reads_avoided';
+
+--echo # Baseline sanity check: 0, 0.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select "no-op query";
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible for optimization.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('d', 31);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible for optimization, access via fake_id only.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where fake_id = 1031;
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible for optimization, access via fake_id of big row.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where fake_id = 1033;
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible for optimization.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('x', 32);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible for optimization.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('y', 33);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible, should not increment lookup counter.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('b', 8);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible, should not increment lookup counter.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('c', 24);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Should increment lookup counter.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+select id, bigfield from prefixinno where bigfield = repeat('z', 128);
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Disable optimization, confirm we still increment counter.
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+set global innodb_prefix_index_cluster_optimization = OFF;
+select id, bigfield from prefixinno where fake_id = 1033;
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval select $count - $base_count into @cluster_lookups;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+drop table prefixinno;
+
+--echo # 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¢');
+
+--echo # Eligible - record length is shorter than prefix
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+DROP TABLE t1;
+
+--echo # 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¢');
+
+--echo # Eligible - record length is shorter than prefix
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+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);
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL;
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix index
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+DROP TABLE t1;
+set global innodb_prefix_index_cluster_optimization = OFF;