github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/secondary_index_master.sql (about) 1 -- 0. insert, update, delete 2 SET GLOBAL experimental_master_index = 1; 3 4 drop table if exists t1; 5 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 6 insert into t1 values("Congress","Lane", "1"); 7 insert into t1 values("Juniper","Way", "2"); 8 insert into t1 values("Nightingale","Lane", "3"); 9 select * from t1; 10 create index idx1 using master on t1(a,b); 11 insert into t1 values("Changing","Expanse", "4"); 12 update t1 set a = "Altering" where c = "4"; 13 delete from t1 where c = "2"; 14 select * from t1 where a = "Congress" and b="Lane"; 15 16 -- 1. failure on create index on non strings. 17 create table t2(a varchar(30), b bigint, c varchar(30) primary key); 18 insert into t2 values("Congress",1, "1"); 19 insert into t2 values("Juniper",2, "2"); 20 insert into t2 values("Nightingale",3, "3"); 21 create index idx2 using master on t2(a,b); 22 23 -- 2.1.a Insert Normal (from Test Document) 24 drop table if exists t1; 25 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 26 insert into t1 values("Congress","Lane", "1"); 27 insert into t1 values("Juniper","Way", "2"); 28 insert into t1 values("Nightingale","Lane", "3"); 29 create index idx1 using master on t1(a,b); 30 insert into t1 values("Alberta","Blvd", "4"); 31 select * from t1 where a = "Alberta" and b="Blvd"; 32 33 34 -- 2.1.b Insert Duplicates 35 insert into t1 values("Nightingale","Lane", "5"); 36 select * from t1 where a = "Nightingale" and b="Lane"; 37 38 -- 2.1.c Insert Nulls 39 insert into t1 values(NULL,"Lane", "6"); 40 select * from t1 where b="Lane"; 41 42 -- 2.1.d Insert Into Select * 43 drop table if exists t2; 44 create table t2(a varchar(30), b varchar(30), c varchar(30)); 45 insert into t2 values("arjun", "sk", "7"); 46 insert into t2 values("albin", "john", "8"); 47 insert into t1 select * from t2; 48 select * from t1 where b="Lane"; 49 50 -- 2.2.a Update a record to duplicate 51 update t1 set a="albin" ,b="john" where c="7"; 52 select * from t1 where a="albin"; 53 54 -- 2.2.b Update a record to NULL 55 update t1 set a=NULL ,b="john" where c="7"; 56 select * from t1 where b="john"; 57 58 -- 2.2.c Delete a record 59 delete from t1 where c="7"; 60 select * from t1 where a="john"; 61 62 -- 2.2.d truncate 63 truncate table t1; 64 select * from t1; 65 66 -- 2.2.e drop 67 show index from t1; 68 show create table t1; 69 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1"; 70 drop table t1; 71 show index from t1; 72 show create table t1; 73 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1"; 74 75 -- 2.3.a Create Index on a single column 76 drop table if exists t1; 77 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 78 insert into t1 values("Congress","Lane", "1"); 79 insert into t1 values("Juniper","Way", "2"); 80 insert into t1 values("Nightingale","Lane", "3"); 81 create index idx1 using master on t1(a); 82 insert into t1 values("Abi","Ma", "4"); 83 select * from t1 where a = "Abi"; 84 85 -- 2.3.b Create Index on multiple columns (>3) 86 drop table if exists t1; 87 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 88 insert into t1 values("Congress","Lane", "1"); 89 insert into t1 values("Juniper","Way", "2"); 90 insert into t1 values("Nightingale","Lane", "3"); 91 create index idx1 using master on t1(a,b,c); 92 insert into t1 values("Abel","John", "4"); 93 insert into t1 values("Amy","Brian", "5"); 94 select * from t1 where a = "Congress" and b="Lane" and c="1"; 95 -- TODO: Fix this 96 97 -- 2.3.c Create Index before table population 98 drop table if exists t1; 99 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 100 create index idx1 using master on t1(a,b); 101 insert into t1 values("Congress","Lane", "1"); 102 insert into t1 values("Juniper","Way", "2"); 103 insert into t1 values("Nightingale","Lane", "3"); 104 select * from t1 where a = "Congress" and b="Lane"; 105 106 -- 2.3.e Create Index using `create table syntax` 107 drop table if exists t1; 108 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key, index idx1 using master (a,b)); 109 insert into t1 values("Congress","Lane", "1"); 110 insert into t1 values("Juniper","Way", "2"); 111 insert into t1 values("Nightingale","Lane", "3"); 112 select * from t1 where a = "Congress" and b="Lane"; 113 114 -- 2.3.f Create Index using `alter table syntax` 115 drop table if exists t1; 116 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 117 insert into t1 values("Congress","Lane", "1"); 118 insert into t1 values("Juniper","Way", "2"); 119 insert into t1 values("Nightingale","Lane", "3"); 120 alter table t1 add index idx1 using master(a,b); 121 insert into t1 values("Congress","Lane", "4"); 122 select * from t1 where a = "Congress" and b="Lane"; 123 124 -- 2.4.a No PK 125 drop table if exists t1; 126 create table t1(a varchar(30), b varchar(30), c varchar(30)); 127 create index idx1 using master on t1(a,b); 128 insert into t1 values("Congress","Lane", "1"); 129 insert into t1 values("Juniper","Way", "2"); 130 insert into t1 values("Nightingale","Lane", "3"); 131 select * from t1 where a="Congress" and b="Lane"; 132 133 -- 2.4.c Composite PK 134 drop table if exists t1; 135 create table t1(a varchar(30), b varchar(30), c varchar(30), primary key(a,b)); 136 create index idx1 using master on t1(a,b); 137 insert into t1 values("Congress","Lane", "1"); 138 insert into t1 values("Juniper","Way", "2"); 139 insert into t1 values("Nightingale","Lane", "3"); 140 select * from t1 where a="Congress" and b="Lane"; 141 142 -- 2.5.b Drop column 143 drop table if exists t1; 144 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 145 create index idx1 using master on t1(a,b); 146 insert into t1 values("Congress","Lane", "1"); 147 insert into t1 values("Juniper","Way", "2"); 148 insert into t1 values("Nightingale","Lane", "3"); 149 alter table t1 drop column b; 150 insert into t1 values("Congress", "4"); 151 select * from t1 where a="Congress"; 152 153 -- 2.5.c Rename column 154 drop table if exists t1; 155 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 156 create index idx1 using master on t1(a); 157 insert into t1 values("Congress","Lane", "1"); 158 insert into t1 values("Juniper","Way", "2"); 159 insert into t1 values("Nightingale","Lane", "3"); 160 alter table t1 rename column a to a1; 161 insert into t1 values("Congress","Lane", "4"); 162 select * from t1 where a1="Congress"; 163 164 -- 2.5.d Change column type 165 drop table if exists t1; 166 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 167 create index idx1 using master on t1(a); 168 insert into t1 values("Congress","Lane", "1"); 169 insert into t1 values("Juniper","Way", "2"); 170 insert into t1 values("Nightingale","Lane", "3"); 171 alter table t1 modify column c int; 172 insert into t1 values("Congress","Lane", 4); 173 select * from t1 where a="Congress"; 174 175 -- 2.5.e Add PK 176 drop table if exists t1; 177 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 178 create index idx1 using master on t1(a); 179 insert into t1 values("Congress","Lane", "1"); 180 insert into t1 values("Juniper","Way", "2"); 181 insert into t1 values("Nightingale","Lane", "3"); 182 alter table t1 drop primary key; 183 alter table t1 add primary key (a,b); 184 insert into t1 values("Congress","Lane2", "4"); 185 select * from t1 where a="Congress"; 186 187 -- 2.5.f Drop PK 188 drop table if exists t1; 189 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 190 create index idx1 using master on t1(a); 191 insert into t1 values("Congress","Lane", "4"); 192 insert into t1 values("Juniper","Way", "5"); 193 insert into t1 values("Nightingale","Lane", "6"); 194 alter table t1 drop primary key; 195 insert into t1 values("Congress","Lane", "7"); 196 select * from t1 where a="Congress"; 197 198 -- 2.6.a Non Varchar column 199 drop table if exists t1; 200 create table t1(a varchar(30), b bigint, c varchar(30) primary key); 201 create index idx1 using master on t1(a,b); 202 203 204 205 -- 2.7.a Select with No PK 206 drop table if exists t1; 207 create table t1(a varchar(30), b varchar(30), c varchar(30)); 208 create index idx1 using master on t1(a,b); 209 insert into t1 values("Congress","Lane", "1"); 210 insert into t1 values("Juniper","Way", "2"); 211 insert into t1 values("Nightingale","Lane", "3"); 212 --explain select * from t1 where a="Congress" and b="Lane"; 213 --+---------------------------------------------------------------------------------------------+ 214 --| QUERY PLAN | 215 --+---------------------------------------------------------------------------------------------+ 216 --| Project | 217 --| -> Join | 218 --| Join Type: INDEX | 219 --| Join Cond: (t1.__mo_fake_pk_col = #[1,0]) | <-- Good 220 --| -> Table Scan on a.t1 | 221 --| Filter Cond: (t1.b = 'Lane'), (t1.a = 'Congress') | 222 --| -> Join | 223 --| Join Type: INNER | 224 --| Join Cond: (#[0,0] = #[1,0]) | 225 --| -> Table Scan on a.__mo_index_secondary_018df437-c576-7c78-8d68-eb29bf7cd598 | 226 --| Filter Cond: prefix_eq(#[0,0], 'Fa FCongress ') | 227 --| -> Table Scan on a.__mo_index_secondary_018df437-c576-7c78-8d68-eb29bf7cd598 | 228 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 229 --+---------------------------------------------------------------------------------------------+ 230 select * from t1 where a="Congress" and b="Lane"; 231 232 -- 2.7.b Select with Single PK 233 drop table if exists t1; 234 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 235 create index idx1 using master on t1(a,b); 236 insert into t1 values("Congress","Lane", "1"); 237 insert into t1 values("Juniper","Way", "2"); 238 insert into t1 values("Nightingale","Lane", "3"); 239 --mysql> explain select * from t1 where a="Nightingale" and b="Lane"; 240 --+---------------------------------------------------------------------------------------------+ 241 --| QUERY PLAN | 242 --+---------------------------------------------------------------------------------------------+ 243 --| Project | 244 --| -> Join | 245 --| Join Type: INDEX | 246 --| Join Cond: (t1.c = #[1,0]) |<-- Good 247 --| -> Table Scan on a.t1 | 248 --| Filter Cond: (t1.b = 'Lane'), (t1.a = 'Nightingale') | 249 --| -> Join | 250 --| Join Type: INNER | 251 --| Join Cond: (#[0,0] = #[1,0]) | 252 --| -> Table Scan on a.__mo_index_secondary_018df438-9530-7b1d-b252-b10d794ae2a4 | 253 --| Filter Cond: prefix_eq(#[0,0], 'Fa FNightingale ') | 254 --| -> Table Scan on a.__mo_index_secondary_018df438-9530-7b1d-b252-b10d794ae2a4 | 255 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 256 --+---------------------------------------------------------------------------------------------+ 257 select * from t1 where a="Nightingale" and b="Lane"; 258 259 260 -- 2.7.c Select with 2 or more PK 261 drop table if exists t1; 262 create table t1(a varchar(30), b0 varchar(30), b1 varchar(30), c varchar(30), d varchar(30), primary key( c, d)); 263 create index idx1 using master on t1(a,b0); 264 insert into t1 values("Congress","Lane", "ALane","1","0"); 265 insert into t1 values("Juniper","Way","AWay", "2","0"); 266 insert into t1 values("Nightingale","Lane","ALane", "3","0"); 267 --mysql> explain select * from t1 where a="Nightingale" and b0="Lane"; 268 --+---------------------------------------------------------------------------------------------+ 269 --| QUERY PLAN | 270 --+---------------------------------------------------------------------------------------------+ 271 --| Project | 272 --| -> Join | 273 --| Join Type: INDEX | 274 --| Join Cond: (t1.__mo_cpkey_col = #[1,0]) |<-- Good 275 --| -> Table Scan on a.t1 | 276 --| Filter Cond: (t1.b0 = 'Lane'), (t1.a = 'Nightingale') | 277 --| -> Join | 278 --| Join Type: INNER | 279 --| Join Cond: (#[0,0] = #[1,0]) | 280 --| -> Table Scan on a.__mo_index_secondary_018df43c-db6a-7afe-bb23-bdf898223435 | 281 --| Filter Cond: prefix_eq(#[0,0], 'Fa FNightingale ') | 282 --| -> Table Scan on a.__mo_index_secondary_018df43c-db6a-7afe-bb23-bdf898223435 | 283 --| Filter Cond: prefix_eq(#[0,0], 'Fb0 FLane ') | 284 --+---------------------------------------------------------------------------------------------+ 285 select * from t1 where a="Nightingale" and b0="Lane"; 286 287 -- 2.8.a Select with one Filter 288 drop table if exists t1; 289 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 290 create index idx1 using master on t1(a,b); 291 insert into t1 values("Congress","Lane", "1"); 292 insert into t1 values("Juniper","Way", "2"); 293 insert into t1 values("Nightingale","Lane", "3"); 294 --mysql> explain select * from t1 where b="Lane"; 295 --+---------------------------------------------------------------------------------------+ 296 --| QUERY PLAN | 297 --+---------------------------------------------------------------------------------------+ 298 --| Project | 299 --| -> Join | 300 --| Join Type: INDEX | 301 --| Join Cond: (t1.c = #[1,0]) |<-- Good 302 --| -> Table Scan on a.t1 | 303 --| Filter Cond: (t1.b = 'Lane') | 304 --| -> Table Scan on a.__mo_index_secondary_018df43d-47dd-75bd-a6c4-9c25c7a51c23 | 305 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 306 --+---------------------------------------------------------------------------------------+ 307 select * from t1 where b="Lane"; 308 309 -- 2.8.b Select with 2 Filters 310 --mysql> explain select * from t1 where a="Juniper" and b="Way"; 311 --+---------------------------------------------------------------------------------------------+ 312 --| QUERY PLAN | 313 --+---------------------------------------------------------------------------------------------+ 314 --| Project | 315 --| -> Join | 316 --| Join Type: INDEX | 317 --| Join Cond: (t1.c = #[1,0]) | 318 --| -> Table Scan on a.t1 | 319 --| Filter Cond: (t1.b = 'Way'), (t1.a = 'Juniper') | 320 --| -> Join | 321 --| Join Type: INNER |<-- Good 322 --| Join Cond: (#[0,0] = #[1,0]) | 323 --| -> Table Scan on a.__mo_index_secondary_018df43d-47dd-75bd-a6c4-9c25c7a51c23 | 324 --| Filter Cond: prefix_eq(#[0,0], 'Fa FJuniper ') | 325 --| -> Table Scan on a.__mo_index_secondary_018df43d-47dd-75bd-a6c4-9c25c7a51c23 | 326 --| Filter Cond: prefix_eq(#[0,0], 'Fb FWay ') | 327 --+---------------------------------------------------------------------------------------------+ 328 select * from t1 where a="Juniper" and b="Way"; 329 330 -- 2.8.c Select with 3 or more Filters 331 drop table if exists t1; 332 create table t1(a varchar(30), b varchar(30), c varchar(30)); 333 create index idx1 using master on t1(a,b,c); 334 insert into t1 values("Congress","Lane", "1"); 335 insert into t1 values("Juniper","Way", "2"); 336 insert into t1 values("Nightingale","Lane", "3"); 337 --mysql> explain select * from t1 where a="Congress" and b="Lane" and c="1"; 338 --+---------------------------------------------------------------------------------------------------+ 339 --| QUERY PLAN | 340 --+---------------------------------------------------------------------------------------------------+ 341 --| Project | 342 --| -> Join | 343 --| Join Type: INDEX | 344 --| Join Cond: (t1.__mo_fake_pk_col = #[1,0]) | 345 --| -> Table Scan on a.t1 | 346 --| Filter Cond: (t1.c = '1'), (t1.b = 'Lane'), (t1.a = 'Congress') | 347 --| -> Join | 348 --| Join Type: INNER |<-- Good 349 --| Join Cond: (#[0,0] = #[1,0]) | 350 --| -> Table Scan on a.__mo_index_secondary_018df43e-105b-70d8-a9c1-88c03b26d8ee | 351 --| Filter Cond: prefix_eq(#[0,0], 'Fa FCongress ') | 352 --| -> Join | 353 --| Join Type: INNER |<-- Good 354 --| Join Cond: (#[0,0] = #[1,0]) | 355 --| -> Table Scan on a.__mo_index_secondary_018df43e-105b-70d8-a9c1-88c03b26d8ee | 356 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 357 --| -> Table Scan on a.__mo_index_secondary_018df43e-105b-70d8-a9c1-88c03b26d8ee | 358 --| Filter Cond: prefix_eq(#[0,0], 'Fc F1 ') | 359 --+---------------------------------------------------------------------------------------------------+ 360 select * from t1 where a="Congress" and b="Lane" and c="1"; 361 362 363 -- 2.8.d Select with = and between 364 --mysql> explain select * from t1 where a="Nightingale" and c between "2" and "3"; 365 --+---------------------------------------------------------------------------------------------+ 366 --| QUERY PLAN | 367 --+---------------------------------------------------------------------------------------------+ 368 --| Project | 369 --| -> Join | 370 --| Join Type: INDEX | 371 --| Join Cond: (t1.__mo_fake_pk_col = #[1,0]) | 372 --| -> Table Scan on a.t1 | 373 --| Filter Cond: (t1.a = 'Nightingale'), t1.c BETWEEN '2' AND '3' | 374 --| -> Join | 375 --| Join Type: INNER |<-- Good 376 --| Join Cond: (#[0,0] = #[1,0]) | 377 --| -> Table Scan on a.__mo_index_secondary_018df43e-105b-70d8-a9c1-88c03b26d8ee | 378 --| Filter Cond: prefix_between(#[0,0], 'Fc F2 ', 'Fc F3 ') |<-- Good 379 --| -> Table Scan on a.__mo_index_secondary_018df43e-105b-70d8-a9c1-88c03b26d8ee | 380 --| Filter Cond: prefix_eq(#[0,0], 'Fa FNightingale ') |<-- Good 381 --+---------------------------------------------------------------------------------------------+ 382 select * from t1 where a="Nightingale" and c between "2" and "3"; 383 384 -- 2.8.e Select with = and in 385 drop table if exists t1; 386 create table t1(a varchar(30), b varchar(30), c varchar(30)); 387 create index idx1 using master on t1(a,b,c); 388 insert into t1 values("Congress","Lane", "1"); 389 insert into t1 values("Juniper","Way", "2"); 390 insert into t1 values("Nightingale","Lane", "3"); 391 --mysql> explain analyze select * from t1 where a in ("Congress","Nightingale") and b="Lane" and c in("1","2","3"); 392 --+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 393 --| QUERY PLAN | 394 --+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 395 --| Project | 396 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=144bytes OutputSize=144bytes MemorySize=144bytes | 397 --| -> Join | 398 --| Analyze: timeConsumed=0ms waitTime=7ms inputRows=4 outputRows=2 InputSize=176bytes OutputSize=144bytes MemorySize=16bytes | 399 --| Join Type: INDEX | 400 --| Join Cond: (t1.__mo_fake_pk_col = #[1,0]) | 401 --| Runtime Filter Build: #[-1,0] | 402 --| -> Table Scan on a.t1 | 403 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=2 InputSize=240bytes OutputSize=160bytes MemorySize=409bytes | 404 --| Filter Cond: (t1.b = 'Lane'), t1.c in ([1 2 3]), t1.a in ([Congress Nightingale]) | 405 --| Block Filter Cond: t1.__mo_fake_pk_col in ([1 3]) | 406 --| Runtime Filter Probe: t1.__mo_fake_pk_col | 407 --| -> Join [GOOD] | 408 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=16bytes OutputSize=16bytes MemorySize=32898bytes | 409 --| Join Type: INNER | 410 --| Join Cond: (#[0,0] = #[1,0]) | 411 --| -> Project | 412 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=16bytes OutputSize=16bytes MemorySize=16bytes | 413 --| -> Table Scan on a.__mo_index_secondary_018e1cf0-f06c-7d3a-9000-bb0adee77acc | 414 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=2 InputSize=288bytes OutputSize=16bytes MemorySize=313bytes | 415 --| Filter Cond: prefix_in(#[0,0], [Fa FCongress Fa FNightingale ]) | 416 --| -> Join | 417 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=16bytes OutputSize=16bytes MemorySize=32898bytes | 418 --| Join Type: INNER | 419 --| Join Cond: (#[0,0] = #[1,0]) | 420 --| -> Project [GOOD] | 421 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=3 outputRows=3 InputSize=24bytes OutputSize=24bytes MemorySize=24bytes | 422 --| -> Table Scan on a.__mo_index_secondary_018e1cf0-f06c-7d3a-9000-bb0adee77acc | 423 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=3 InputSize=288bytes OutputSize=24bytes MemorySize=321bytes | 424 --| Filter Cond: prefix_in(#[0,0], [Fc F1 Fc F2 Fc F3 ]) [GOOD] | 425 --| -> Project | 426 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=2 outputRows=2 InputSize=16bytes OutputSize=16bytes MemorySize=16bytes | 427 --| -> Table Scan on a.__mo_index_secondary_018e1cf0-f06c-7d3a-9000-bb0adee77acc | 428 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=2 InputSize=288bytes OutputSize=16bytes MemorySize=313bytes | 429 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 430 --+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 431 --35 rows in set (0.01 sec) 432 select * from t1 where a in ("Congress","Nightingale") and b="Lane" and c in("1","2","3"); 433 434 -- 2.8.f SELECT with LIMIT 435 drop table if exists t1; 436 create table t1(a varchar(30), b varchar(30), c varchar(30)); 437 create index idx1 using master on t1(a,b,c); 438 insert into t1 values("Congress","Lane", "1"); 439 insert into t1 values("Juniper","Way", "2"); 440 insert into t1 values("Nightingale","Lane", "3"); 441 select * from t1 where a between "Congress" and "Nightingale" and b="Lane" and c between "1" and "3"; 442 select * from t1 where a between "Congress" and "Nightingale" and b="Lane" and c between "1" and "3" limit 1; 443 --mysql> explain analyze select * from t1 where a between "Congress" and "Nightingale" and b="Lane" and c between "1" and "3" limit 1; 444 --+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 445 --| QUERY PLAN | 446 --+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 447 --| Project | 448 --| Analyze: timeConsumed=0ms waitTime=1ms inputRows=1 outputRows=1 InputSize=72bytes OutputSize=72bytes MemorySize=72bytes | 449 --| -> Join | 450 --| Analyze: timeConsumed=0ms waitTime=2ms inputRows=1 outputRows=1 InputSize=8bytes OutputSize=72bytes MemorySize=8bytes | 451 --| Join Type: INDEX | 452 --| Join Cond: (t1.__mo_fake_pk_col = #[1,0]) | 453 --| Runtime Filter Build: #[-1,0] | 454 --| -> Table Scan on a.t1 | 455 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=80bytes OutputSize=80bytes MemorySize=164bytes | 456 --| Filter Cond: (t1.b = 'Lane'), t1.c BETWEEN '1' AND '3', t1.a BETWEEN 'Congress' AND 'Nightingale' | 457 --| Block Filter Cond: t1.__mo_fake_pk_col in (1) | 458 --| Runtime Filter Probe: t1.__mo_fake_pk_col | 459 --| -> Join | 460 --| Analyze: timeConsumed=0ms probe_time=[total=0ms,min=0ms,max=0ms,dop=10] build_time=[0ms] waitTime=8ms inputRows=2 outputRows=1 InputSize=16bytes OutputSize=8bytes MemorySize=180859bytes | 461 --| Join Type: INNER | 462 --| Join Cond: (#[0,0] = #[1,0]) | 463 --| -> Project [GOOD] | 464 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=8bytes OutputSize=8bytes MemorySize=8bytes | 465 --| -> Table Scan on a.__mo_index_secondary_018e1ced-b355-7509-a021-b417bd3bd535 | 466 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=1 InputSize=288bytes OutputSize=8bytes MemorySize=321bytes | 467 --| Filter Cond: prefix_between(#[0,0], 'Fa FCongress ', 'Fa FNightingale ') | 468 --| Limit: 1 | 469 --| -> Join | 470 --| Analyze: timeConsumed=0ms probe_time=[total=0ms,min=0ms,max=0ms,dop=10] build_time=[0ms] waitTime=4ms inputRows=2 outputRows=1 InputSize=16bytes OutputSize=8bytes MemorySize=180859bytes | 471 --| Join Type: INNER | 472 --| Join Cond: (#[0,0] = #[1,0]) | 473 --| -> Project [GOOD] | 474 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=8bytes OutputSize=8bytes MemorySize=8bytes | 475 --| -> Table Scan on a.__mo_index_secondary_018e1ced-b355-7509-a021-b417bd3bd535 | 476 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=1 InputSize=288bytes OutputSize=8bytes MemorySize=321bytes | 477 --| Filter Cond: prefix_between(#[0,0], 'Fc F1 ', 'Fc F3 ') | 478 --| Limit: 1 [GOOD] | 479 --| -> Project [GOOD] | 480 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=8bytes OutputSize=8bytes MemorySize=8bytes | 481 --| -> Table Scan on a.__mo_index_secondary_018e1ced-b355-7509-a021-b417bd3bd535 | 482 --| Analyze: timeConsumed=0ms waitTime=0ms inputRows=9 outputRows=1 InputSize=288bytes OutputSize=8bytes MemorySize=313bytes | 483 --| Filter Cond: prefix_eq(#[0,0], 'Fb FLane ') | 484 --| Limit: 1 [GOOD] | 485 --+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 486 --38 rows in set (0.01 sec) 487 488 SET GLOBAL experimental_master_index = 0;