1--source include/default_optimizer_switch.inc 2--source include/default_charset.inc 3 4--disable_warnings 5DROP TABLE IF EXISTS t1,t2,t3,t4; 6DROP DATABASE IF EXISTS world; 7--enable_warnings 8 9set names utf8; 10 11CREATE DATABASE world; 12 13use world; 14 15--source include/world_schema.inc 16 17--disable_query_log 18--disable_result_log 19--disable_warnings 20--source include/world.inc 21--enable_warnings 22--enable_result_log 23--enable_query_log 24 25SELECT COUNT(*) FROM Country; 26SELECT COUNT(*) FROM City; 27SELECT COUNT(*) FROM CountryLanguage; 28 29CREATE INDEX Name ON City(Name); 30 31--disable_query_log 32--disable_result_log 33--disable_warnings 34ANALYZE TABLE City; 35--enable_warnings 36--enable_result_log 37--enable_query_log 38 39SET SESSION optimizer_switch='rowid_filter=off'; 40SET SESSION optimizer_switch='index_merge_sort_intersection=on'; 41 42SELECT COUNT(*) FROM City; 43 44# The output of the next 6 queries tells us about selectivities 45# of the conditions utilized in 4 queries following after them 46 47SELECT COUNT(*) FROM City WHERE Name LIKE 'C%'; 48SELECT COUNT(*) FROM City WHERE Name LIKE 'M%'; 49SELECT COUNT(*) FROM City WHERE Population > 1000000; 50SELECT COUNT(*) FROM City WHERE Population > 1500000; 51SELECT COUNT(*) FROM City WHERE Population > 300000; 52SELECT COUNT(*) FROM City WHERE Population > 7000000; 53 54# The pattern of the WHERE condition used in the following 4 queries is 55# range(key1) AND range(key2) 56# Varying values of the constants in the conjuncts of the condition 57# we can get either an index intersection retrieval over key1 and key2 58# or a range index scan for one of these indexes 59 60--replace_column 9 # 61EXPLAIN 62SELECT * FROM City WHERE 63 Name LIKE 'C%' AND Population > 1000000; 64 65--replace_column 9 # 66EXPLAIN 67SELECT * FROM City WHERE 68 Name LIKE 'M%' AND Population > 1500000; 69 70--replace_column 9 # 71EXPLAIN 72SELECT * FROM City 73 WHERE Name LIKE 'M%' AND Population > 300000; 74 75--replace_column 9 # 76EXPLAIN 77SELECT * FROM City 78 WHERE Name LIKE 'M%' AND Population > 7000000; 79 80 81# The following 8 queries check that 82# the previous 4 plans are valid and return 83# the correct results when executed 84 85--sorted_result 86SELECT * FROM City USE INDEX () 87 WHERE Name LIKE 'C%' AND Population > 1000000; 88--sorted_result 89SELECT * FROM City 90 WHERE Name LIKE 'C%' AND Population > 1000000; 91 92--sorted_result 93SELECT * FROM City USE INDEX () 94 WHERE Name LIKE 'M%' AND Population > 1500000; 95--sorted_result 96SELECT * FROM City 97 WHERE Name LIKE 'M%' AND Population > 1500000; 98 99--sorted_result 100SELECT * FROM City USE INDEX () 101 WHERE Name LIKE 'M%' AND Population > 300000; 102--sorted_result 103SELECT * FROM City 104 WHERE Name LIKE 'M%' AND Population > 300000; 105 106 107SELECT * FROM City USE INDEX () 108 WHERE Name LIKE 'M%' AND Population > 7000000; 109 110SELECT * FROM City 111 WHERE Name LIKE 'M%' AND Population > 7000000; 112 113 114# The output of the next 7 queries tells us about selectivities 115# of the conditions utilized in 3 queries following after them 116 117SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N'; 118SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J'; 119SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'K'; 120SELECT COUNT(*) FROM City WHERE Population > 1000000; 121SELECT COUNT(*) FROM City WHERE Population > 500000; 122SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; 123SELECT COUNT(*) FROM City WHERE Country LIKE 'B%'; 124SELECT COUNT(*) FROM City WHERE Country LIKE 'J%'; 125 126 127# The pattern of the WHERE condition used in the following 3 queries is 128# range(key1) AND range(key2) AND range(key3) 129# Varying values of the constants in the conjuncts of the condition 130# we can get index intersection over different pairs of keys: 131# over(key1,key2), over(key1,key3) and over(key2,key3) 132 133 134--replace_column 9 # 135EXPLAIN 136SELECT * FROM City 137 WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 138 139--replace_column 9 # 140EXPLAIN 141SELECT * FROM City 142 WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 143 144--replace_column 7 # 9 # 145--replace_result Population,Country,Name Population,Name,Country 146EXPLAIN 147SELECT * FROM City 148 WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 149 150 151# The following 6 queries check that 152# the previous 3 plans are valid and return 153# the correct results when executed 154 155 156SELECT * FROM City USE INDEX () 157 WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 158 159SELECT * FROM City 160 WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; 161 162 163SELECT * FROM City USE INDEX () 164 WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 165 166--sorted_result 167SELECT * FROM City 168 WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 169 170 171SELECT * FROM City USE INDEX () 172 WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 173 174--sorted_result 175SELECT * FROM City 176 WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; 177 178 179# The output of the next 12 queries tells us about selectivities 180# of the conditions utilized in 5 queries following after them 181 182SELECT COUNT(*) FROM City WHERE ID BETWEEN 501 AND 1000; 183SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 500; 184SELECT COUNT(*) FROM City WHERE ID BETWEEN 2001 AND 2500; 185SELECT COUNT(*) FROM City WHERE ID BETWEEN 3701 AND 4000; 186SELECT COUNT(*) FROM City WHERE Population > 700000; 187SELECT COUNT(*) FROM City WHERE Population > 1000000; 188SELECT COUNT(*) FROM City WHERE Population > 300000; 189SELECT COUNT(*) FROM City WHERE Population > 600000; 190SELECT COUNT(*) FROM City WHERE Country LIKE 'C%'; 191SELECT COUNT(*) FROM City WHERE Country LIKE 'A%'; 192SELECT COUNT(*) FROM City WHERE Country LIKE 'H%'; 193SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z'; 194 195 196# The pattern of the WHERE condition used in the following 5 queries is 197# range(key1) AND range(key2) AND range(key3) 198# with key1 happens to be a primary key (it matters only for InnoDB) 199# Varying values of the constants in the conjuncts of the condition 200# we can get index intersection either over all three keys, or over 201# different pairs, or a range scan over one of these keys. 202# Bear in mind that the condition (Country LIKE 'A%') is actually 203# equivalent to the condition (Country BETWEEN 'A' AND 'B') for the 204# tested instance the table City. 205 206 207--replace_column 9 # 208EXPLAIN 209SELECT * FROM City 210 WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 211 212--replace_column 9 # 213EXPLAIN 214SELECT * FROM City 215 WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 216 217--replace_column 9 # 218EXPLAIN 219SELECT * FROM City 220 WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 221 222--replace_column 9 # 223EXPLAIN 224SELECT * FROM City 225 WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 226 AND Country BETWEEN 'S' AND 'Z'; 227 228--replace_column 9 # 229--replace_result PRIMARY,Country,Population PRIMARY,Population,Country 4,7,4 4,4,7 230EXPLAIN 231SELECT * FROM City 232 WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 233 AND Country BETWEEN 'S' AND 'Z' ; 234 235 236# The following 10 queries check that 237# the previous 5 plans are valid and return 238# the correct results when executed 239 240 241SELECT * FROM City USE INDEX () 242 WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 243 244SELECT * FROM City 245 WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; 246 247--sorted_result 248SELECT * FROM City USE INDEX () 249 WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 250--sorted_result 251SELECT * FROM City 252 WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 253 254 255SELECT * FROM City USE INDEX () 256 WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 257 258SELECT * FROM City 259 WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; 260 261--sorted_result 262SELECT * FROM City USE INDEX () 263 WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 264 AND Country BETWEEN 'S' AND 'Z'; 265--sorted_result 266SELECT * FROM City 267 WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000 268 AND Country BETWEEN 'S' AND 'Z'; 269 270--sorted_result 271SELECT * FROM City USE INDEX () 272 WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 273 AND Country BETWEEN 'S' AND 'Z' ; 274--sorted_result 275SELECT * FROM City 276 WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 277 AND Country BETWEEN 'S' AND 'Z' ; 278 279# Originally this was just sort_buffer_size=2048. Then, it started 280# failing on 32bit due to different cost number in 281# Unique::get_use_cost() because of sizeof(sizeof(TREE_ELEMENT)+key_size) 282 283# On 64 bit: Unique object element_size=32, which gives 2048/32= 64 elements 284# in the tree. 285# On 32 bit: Unique object element_size=24. 286# If we want 64 elements in the tree, we need 64*24=1536 as sort_buffer_size. 287 288# The purpose of setting sort_buffer_size is to show that some of the following 289# explains should use 'index_merge' while others should use range 290# If the following code causes future problems, the other option would be 291# to create a separate result-.diff file for 32 bit. 292 293SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536); 294 295# The following EXPLAIN command demonstrate that the execution plans 296# may be different if sort_buffer_size is set to a small value 297 298 299--replace_column 9 # 300EXPLAIN 301SELECT * FROM City WHERE 302 Name LIKE 'C%' AND Population > 1000000; 303 304--replace_column 9 # 305EXPLAIN 306SELECT * FROM City WHERE 307 Name LIKE 'M%' AND Population > 1500000; 308 309 310--replace_column 9 # 311EXPLAIN 312SELECT * FROM City 313 WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%'; 314 315--replace_column 9 # 316EXPLAIN 317SELECT * FROM City 318 WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; 319 320 321--replace_column 9 # 322EXPLAIN 323SELECT * FROM City 324 WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 325 326--replace_column 9 # 327--replace_result PRIMARY,Country,Population PRIMARY,Population,Country 4,7,4 4,4,7 328EXPLAIN 329SELECT * FROM City 330 WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 331 AND Country BETWEEN 'S' AND 'Z'; 332 333 334#Yet the query themselves return the correct results in this case as well 335 336--sorted_result 337SELECT * FROM City WHERE 338 Name LIKE 'C%' AND Population > 1000000; 339 340--sorted_result 341SELECT * FROM City WHERE 342 Name LIKE 'M%' AND Population > 1500000; 343 344--sorted_result 345SELECT * FROM City 346 WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'J%'; 347 348--sorted_result 349SELECT * FROM City 350 WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; 351 352 353SELECT * FROM City 354 WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%'; 355--sorted_result 356SELECT * FROM City 357 WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 358 AND Country BETWEEN 'S' AND 'Z'; 359 360 361SET SESSION sort_buffer_size = default; 362 363# Instead of the index on the column Country create two compound indexes 364# including this column as the first component 365 366DROP INDEX Country ON City; 367 368CREATE INDEX CountryID ON City(Country,ID); 369CREATE INDEX CountryName ON City(Country,Name); 370 371--disable_query_log 372--disable_result_log 373--disable_warnings 374ANALYZE TABLE City; 375--enable_warnings 376--enable_result_log 377--enable_query_log 378 379# Check that the first component of a compound index can be used for 380# index intersection, even in the cases when we have a ref access 381# for this component 382 383--replace_column 9 # 384EXPLAIN 385SELECT * FROM City 386 WHERE Country LIKE 'M%' AND Population > 1000000; 387 388--replace_column 9 # 389EXPLAIN 390SELECT * FROM City 391 WHERE Country='USA' AND Population > 1000000; 392 393--replace_column 9 # 394EXPLAIN 395SELECT * FROM City 396 WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 397 398 399# Check that the previous 3 plans return the right results when executed 400 401--sorted_result 402SELECT * FROM City USE INDEX () 403 WHERE Country LIKE 'M%' AND Population > 1000000; 404--sorted_result 405SELECT * FROM City 406 WHERE Country LIKE 'M%' AND Population > 1000000; 407 408--sorted_result 409SELECT * FROM City USE INDEX () 410 WHERE Country='USA' AND Population > 1000000; 411--sorted_result 412SELECT * FROM City 413 WHERE Country='USA' AND Population > 1000000; 414 415 416SELECT * FROM City USE INDEX () 417 WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 418 419--sorted_result 420SELECT * FROM City 421 WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%'; 422 423 424# 425# Bug #754521: wrong cost of index intersection leading 426# to the choice of a suboptimal execution plan 427# 428 429--replace_column 9 # 430EXPLAIN 431SELECT * FROM City, Country 432 WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND 433 Country.Code=City.Country; 434 435DROP DATABASE world; 436 437use test; 438 439# 440# Bug #684086: crash with EXPLAIN in InnoDB for index intersection 441# of two indexes one of which is primary 442# 443 444CREATE TABLE t1 ( 445 f1 int, 446 f4 varchar(32), 447 f5 int, 448 PRIMARY KEY (f1), 449 KEY (f4) 450); 451 452INSERT INTO t1 VALUES 453 (5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6), 454 (530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1), 455 (535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2), 456 (540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0), 457 (956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0), 458 (961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL), 459 (966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0), 460 (971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0), 461 (976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7), 462 (981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1), 463 (986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7), 464 (991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4), 465 (996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2); 466 467--replace_column 9 # 468EXPLAIN 469SELECT * FROM t1 470WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; 471 472--sorted_result 473SELECT * FROM t1 474WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; 475 476DROP TABLE t1; 477 478SET SESSION optimizer_switch='index_merge_sort_intersection=on'; 479SET SESSION optimizer_switch='rowid_filter=default'; 480