1SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization; 2set global innodb_prefix_index_cluster_optimization = ON; 3show variables like 'innodb_prefix_index_cluster_optimization'; 4Variable_name Value 5innodb_prefix_index_cluster_optimization ON 6# Create a table with a large varchar field that we index the prefix 7# of and ensure we only trigger cluster lookups when we expect it. 8create table prefixinno ( 9id int not null, 10fake_id int not null, 11bigfield varchar(4096), 12primary key(id), 13index bigfield_idx (bigfield(32)), 14index fake_id_bigfield_prefix (fake_id, bigfield(32)) 15) engine=innodb; 16insert into prefixinno values (1, 1001, repeat('a', 1)), 17(8, 1008, repeat('b', 8)), 18(24, 1024, repeat('c', 24)), 19(31, 1031, repeat('d', 31)), 20(32, 1032, repeat('x', 32)), 21(33, 1033, repeat('y', 33)), 22(128, 1128, repeat('z', 128)); 23select * from prefixinno; 24id fake_id bigfield 251 1001 a 268 1008 bbbbbbbb 2724 1024 cccccccccccccccccccccccc 2831 1031 ddddddddddddddddddddddddddddddd 2932 1032 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 3033 1033 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 31128 1128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 32# Baseline sanity check: 0, 0. 33select "no-op query"; 34no-op query 35no-op query 36select @cluster_lookups; 37@cluster_lookups 380 39select @cluster_lookups_avoided; 40@cluster_lookups_avoided 410 42# Eligible for optimization. 43select id, bigfield from prefixinno where bigfield = repeat('d', 31); 44id bigfield 4531 ddddddddddddddddddddddddddddddd 46select @cluster_lookups; 47@cluster_lookups 480 49select @cluster_lookups_avoided; 50@cluster_lookups_avoided 511 52# Eligible for optimization, access via fake_id only. 53select id, bigfield from prefixinno where fake_id = 1031; 54id bigfield 5531 ddddddddddddddddddddddddddddddd 56select @cluster_lookups; 57@cluster_lookups 580 59select @cluster_lookups_avoided; 60@cluster_lookups_avoided 611 62# Not eligible for optimization, access via fake_id of big row. 63select id, bigfield from prefixinno where fake_id = 1033; 64id bigfield 6533 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 66select @cluster_lookups; 67@cluster_lookups 681 69select @cluster_lookups_avoided; 70@cluster_lookups_avoided 710 72# Not eligible for optimization. 73select id, bigfield from prefixinno where bigfield = repeat('x', 32); 74id bigfield 7532 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 76select @cluster_lookups; 77@cluster_lookups 781 79select @cluster_lookups_avoided; 80@cluster_lookups_avoided 810 82# Not eligible for optimization. 83select id, bigfield from prefixinno where bigfield = repeat('y', 33); 84id bigfield 8533 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 86select @cluster_lookups; 87@cluster_lookups 881 89select @cluster_lookups_avoided; 90@cluster_lookups_avoided 910 92# Eligible, should not increment lookup counter. 93select id, bigfield from prefixinno where bigfield = repeat('b', 8); 94id bigfield 958 bbbbbbbb 96select @cluster_lookups; 97@cluster_lookups 980 99select @cluster_lookups_avoided; 100@cluster_lookups_avoided 1011 102# Eligible, should not increment lookup counter. 103select id, bigfield from prefixinno where bigfield = repeat('c', 24); 104id bigfield 10524 cccccccccccccccccccccccc 106select @cluster_lookups; 107@cluster_lookups 1080 109select @cluster_lookups_avoided; 110@cluster_lookups_avoided 1111 112# Should increment lookup counter. 113select id, bigfield from prefixinno where bigfield = repeat('z', 128); 114id bigfield 115128 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 116select @cluster_lookups; 117@cluster_lookups 1181 119select @cluster_lookups_avoided; 120@cluster_lookups_avoided 1210 122# Disable optimization, confirm we still increment counter. 123set global innodb_prefix_index_cluster_optimization = OFF; 124select id, bigfield from prefixinno where fake_id = 1033; 125id bigfield 12633 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 127select @cluster_lookups; 128@cluster_lookups 1291 130select @cluster_lookups_avoided; 131@cluster_lookups_avoided 1320 133drop table prefixinno; 134# Multi-byte handling case 135set global innodb_prefix_index_cluster_optimization = ON; 136SET NAMES utf8mb4; 137CREATE TABLE t1( 138f1 varchar(10) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_BIN, 139INDEX (f1(3)))ENGINE=INNODB; 140INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢'); 141INSERT INTO t1 VALUES('தமிழ்'), (''), (''), (''); 142INSERT INTO t1 VALUES('me'), ('eu€'), ('ls¢'); 143# Eligible - record length is shorter than prefix 144SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a'; 145f1 146a 147select @cluster_lookups; 148@cluster_lookups 1490 150select @cluster_lookups_avoided; 151@cluster_lookups_avoided 1521 153# Not eligible - record length longer than prefix length 154SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%'; 155f1 156cccc 157cčc 158select @cluster_lookups; 159@cluster_lookups 1603 161select @cluster_lookups_avoided; 162@cluster_lookups_avoided 1630 164# Eligible - record length shorter than prefix length 165SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až'; 166f1 167až 168select @cluster_lookups; 169@cluster_lookups 1700 171select @cluster_lookups_avoided; 172@cluster_lookups_avoided 1731 174# Not eligible - record length longer than prefix length 175SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்'; 176f1 177தமிழ் 178select @cluster_lookups; 179@cluster_lookups 1801 181select @cluster_lookups_avoided; 182@cluster_lookups_avoided 1830 184# Not eligible - record length longer than prefix length 185SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%'; 186f1 187ggᵷg 188select @cluster_lookups; 189@cluster_lookups 1901 191select @cluster_lookups_avoided; 192@cluster_lookups_avoided 1930 194# Not eligible - record length longer than prefix length 195SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 196f1 197me 198select @cluster_lookups; 199@cluster_lookups 2001 201select @cluster_lookups_avoided; 202@cluster_lookups_avoided 2030 204# Not eligible - record length longer than prefix length 205SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢'; 206f1 207ls¢ 208select @cluster_lookups; 209@cluster_lookups 2101 211select @cluster_lookups_avoided; 212@cluster_lookups_avoided 2130 214# Eligible - record length shorter than prefix length 215SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%'; 216f1 217¢¢ 218select @cluster_lookups; 219@cluster_lookups 2201 221select @cluster_lookups_avoided; 222@cluster_lookups_avoided 2231 224# Eligible - record length shorter than prefix length 225SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 226f1 227 228select @cluster_lookups; 229@cluster_lookups 2301 231select @cluster_lookups_avoided; 232@cluster_lookups_avoided 2331 234# Not eligible - record length longer than prefix length 235SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 236f1 237 238select @cluster_lookups; 239@cluster_lookups 2400 241select @cluster_lookups_avoided; 242@cluster_lookups_avoided 2432 244# Not eligible - record length longer than prefix length 245SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 246f1 247 248select @cluster_lookups; 249@cluster_lookups 2500 251select @cluster_lookups_avoided; 252@cluster_lookups_avoided 2532 254DROP TABLE t1; 255# Multi-byte with minimum character length > 1 bytes 256CREATE TABLE t1( 257f1 varchar(10) CHARACTER SET UTF16 COLLATE UTF16_BIN, 258INDEX (f1(3)))ENGINE=INNODB; 259INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢'); 260INSERT INTO t1 VALUES('தமிழ்'), (''), (''), (''); 261INSERT INTO t1 VALUES('me'), ('eu€'), ('ls¢'); 262# Eligible - record length is shorter than prefix 263SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a'; 264f1 265a 266select @cluster_lookups; 267@cluster_lookups 2680 269select @cluster_lookups_avoided; 270@cluster_lookups_avoided 2711 272# Not eligible - record length longer than prefix length 273SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%'; 274f1 275cccc 276cčc 277select @cluster_lookups; 278@cluster_lookups 2793 280select @cluster_lookups_avoided; 281@cluster_lookups_avoided 2820 283# Eligible - record length shorter than prefix length 284SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až'; 285f1 286až 287select @cluster_lookups; 288@cluster_lookups 2890 290select @cluster_lookups_avoided; 291@cluster_lookups_avoided 2921 293# Not eligible - record length longer than prefix length 294SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்'; 295f1 296தமிழ் 297select @cluster_lookups; 298@cluster_lookups 2991 300select @cluster_lookups_avoided; 301@cluster_lookups_avoided 3020 303# Not eligible - record length longer than prefix length 304SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%'; 305f1 306ggᵷg 307select @cluster_lookups; 308@cluster_lookups 3092 310select @cluster_lookups_avoided; 311@cluster_lookups_avoided 3120 313# Not eligible - record length longer than prefix length 314SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 315f1 316me 317select @cluster_lookups; 318@cluster_lookups 3191 320select @cluster_lookups_avoided; 321@cluster_lookups_avoided 3220 323# Not eligible - record length longer than prefix length 324SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢'; 325f1 326ls¢ 327select @cluster_lookups; 328@cluster_lookups 3291 330select @cluster_lookups_avoided; 331@cluster_lookups_avoided 3320 333# Eligible - record length shorter than prefix length 334SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%'; 335f1 336¢¢ 337select @cluster_lookups; 338@cluster_lookups 3391 340select @cluster_lookups_avoided; 341@cluster_lookups_avoided 3421 343# Eligible - record length shorter than prefix length 344SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 345f1 346 347select @cluster_lookups; 348@cluster_lookups 3491 350select @cluster_lookups_avoided; 351@cluster_lookups_avoided 3521 353# Eligible - record length is shorter than prefix length 354SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 355f1 356 357select @cluster_lookups; 358@cluster_lookups 3590 360select @cluster_lookups_avoided; 361@cluster_lookups_avoided 3622 363# Eligible - record length is shorter than prefix length 364SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '%'; 365f1 366 367select @cluster_lookups; 368@cluster_lookups 3690 370select @cluster_lookups_avoided; 371@cluster_lookups_avoided 3722 373DROP TABLE t1; 374CREATE TABLE t1( 375col1 INT, 376col2 BLOB DEFAULT NULL, 377INDEX `idx1`(col2(4), col1))ENGINE=INNODB; 378INSERT INTO t1 VALUES (2, 'test'), (3, repeat('test1', 2000)); 379INSERT INTO t1(col1) VALUES(1); 380# Eligible - record length is shorter than prefix length 381SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL; 382col1 3831 384select @cluster_lookups; 385@cluster_lookups 3860 387select @cluster_lookups_avoided; 388@cluster_lookups_avoided 3891 390# Not eligible - record length longer than prefix index 391SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%'; 392col1 3933 394select @cluster_lookups; 395@cluster_lookups 3962 397select @cluster_lookups_avoided; 398@cluster_lookups_avoided 3990 400DROP TABLE t1; 401# 402# MDEV-20464 Division by 0 in row_search_with_covering_prefix() 403# 404CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1), 405KEY (f1,f2,f3)) ENGINE=InnoDB; 406INSERT INTO t1 (f1) VALUES (NULL),(0); 407SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1; 408f1 MAX(f3) COUNT(f4) 409NULL NULL 0 4100 0 1 411DROP TABLE t1; 412# 413# MDEV-23600 Division by 0 in row_search_with_covering_prefix() 414# 415CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB; 416INSERT t SET c=POINT(1,1); 417SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t); 418c 419DROP TABLE t; 420# 421# MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization 422# 423CREATE TABLE wp_blogs ( 424blog_id bigint(20) NOT NULL auto_increment, 425site_id bigint(20) NOT NULL default '0', 426domain varchar(200) NOT NULL default '', 427path varchar(100) NOT NULL default '', 428registered datetime NOT NULL default '0000-00-00 00:00:00', 429last_updated datetime NOT NULL default '0000-00-00 00:00:00', 430public tinyint(2) NOT NULL default '1', 431archived tinyint(2) NOT NULL default '0', 432mature tinyint(2) NOT NULL default '0', 433spam tinyint(2) NOT NULL default '0', 434deleted tinyint(2) NOT NULL default '0', 435lang_id int(11) NOT NULL default '0', 436PRIMARY KEY (blog_id), 437KEY domain (domain(50),path(5)), 438KEY lang_id (lang_id) 439) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 440INSERT INTO wp_blogs (domain, path) VALUES 441('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'), 442('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'), 443('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar'); 444SET GLOBAL innodb_prefix_index_cluster_optimization=off; 445SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') 446AND path IN ( '/fondsinvesteringer/', '/' ); 447blog_id 4482 4491 450SET GLOBAL innodb_prefix_index_cluster_optimization=on; 451SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') 452AND path IN ( '/fondsinvesteringer/', '/' ); 453blog_id 4542 4551 456DROP TABLE wp_blogs; 457SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt; 458