1SET SESSION STORAGE_ENGINE='InnoDB'; 2CREATE DATABASE dbt3_s001; 3use dbt3_s001; 4explain 5select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; 6id select_type table type possible_keys key key_len ref rows Extra 71 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index 8flush status; 9select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; 10count(*) 111 12show status like 'handler_read%'; 13Variable_name Value 14Handler_read_first 0 15Handler_read_key 1 16Handler_read_last 0 17Handler_read_next 1 18Handler_read_prev 0 19Handler_read_retry 0 20Handler_read_rnd 0 21Handler_read_rnd_deleted 0 22Handler_read_rnd_next 0 23explain 24select count(*) from lineitem 25where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; 26id select_type table type possible_keys key key_len ref rows Extra 271 SIMPLE lineitem const PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 const,const 1 28flush status; 29select count(*) from lineitem 30where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01'; 31count(*) 321 33show status like 'handler_read%'; 34Variable_name Value 35Handler_read_first 0 36Handler_read_key 1 37Handler_read_last 0 38Handler_read_next 0 39Handler_read_prev 0 40Handler_read_retry 0 41Handler_read_rnd 0 42Handler_read_rnd_deleted 0 43Handler_read_rnd_next 0 44explain 45select count(*) from lineitem 46where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; 47id select_type table type possible_keys key key_len ref rows Extra 481 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 1 Using where; Using index 49flush status; 50select count(*) from lineitem 51where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; 52count(*) 531 54show status like 'handler_read%'; 55Variable_name Value 56Handler_read_first 0 57Handler_read_key 1 58Handler_read_last 0 59Handler_read_next 1 60Handler_read_prev 0 61Handler_read_retry 0 62Handler_read_rnd 0 63Handler_read_rnd_deleted 0 64Handler_read_rnd_next 0 65explain 66select l_orderkey, l_linenumber from lineitem 67where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 3 Using where; Using index 70flush status; 71select l_orderkey, l_linenumber from lineitem 72where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 73l_orderkey l_linenumber 741088 3 751217 1 761221 3 77show status like 'handler_read%'; 78Variable_name Value 79Handler_read_first 0 80Handler_read_key 1 81Handler_read_last 0 82Handler_read_next 3 83Handler_read_prev 0 84Handler_read_retry 0 85Handler_read_rnd 0 86Handler_read_rnd_deleted 0 87Handler_read_rnd_next 0 88explain 89select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; 90id select_type table type possible_keys key key_len ref rows Extra 911 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 92flush status; 93select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; 94min(l_orderkey) 95130 96show status like 'handler_read%'; 97Variable_name Value 98Handler_read_first 0 99Handler_read_key 1 100Handler_read_last 0 101Handler_read_next 0 102Handler_read_prev 0 103Handler_read_retry 0 104Handler_read_rnd 0 105Handler_read_rnd_deleted 0 106Handler_read_rnd_next 0 107explain 108select min(l_orderkey) from lineitem 109where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 110id select_type table type possible_keys key key_len ref rows Extra 1111 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 112flush status; 113select min(l_orderkey) from lineitem 114where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 115min(l_orderkey) 1161088 117show status like 'handler_read%'; 118Variable_name Value 119Handler_read_first 0 120Handler_read_key 1 121Handler_read_last 0 122Handler_read_next 0 123Handler_read_prev 0 124Handler_read_retry 0 125Handler_read_rnd 0 126Handler_read_rnd_deleted 0 127Handler_read_rnd_next 0 128explain 129select max(l_linenumber) from lineitem 130where l_shipdate='1992-07-01' and l_orderkey=130; 131id select_type table type possible_keys key key_len ref rows Extra 1321 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 133flush status; 134select max(l_linenumber) from lineitem 135where l_shipdate='1992-07-01' and l_orderkey=130; 136max(l_linenumber) 1372 138show status like 'handler_read%'; 139Variable_name Value 140Handler_read_first 0 141Handler_read_key 1 142Handler_read_last 0 143Handler_read_next 0 144Handler_read_prev 0 145Handler_read_retry 0 146Handler_read_rnd 0 147Handler_read_rnd_deleted 0 148Handler_read_rnd_next 0 149explain 150select l_orderkey, l_linenumber 151from lineitem use index (i_l_shipdate, i_l_receiptdate) 152where l_shipdate='1992-07-01' and l_orderkey=130 153or l_receiptdate='1992-07-01' and l_orderkey=5603; 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 2 Using union(i_l_shipdate,i_l_receiptdate); Using where 156flush status; 157select l_orderkey, l_linenumber 158from lineitem use index (i_l_shipdate, i_l_receiptdate) 159where l_shipdate='1992-07-01' and l_orderkey=130 160or l_receiptdate='1992-07-01' and l_orderkey=5603; 161l_orderkey l_linenumber 162130 2 1635603 2 164show status like 'handler_read%'; 165Variable_name Value 166Handler_read_first 0 167Handler_read_key 2 168Handler_read_last 0 169Handler_read_next 2 170Handler_read_prev 0 171Handler_read_retry 0 172Handler_read_rnd 2 173Handler_read_rnd_deleted 0 174Handler_read_rnd_next 0 175explain 176select l_orderkey, l_linenumber 177from lineitem use index (i_l_shipdate, i_l_receiptdate) 178where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 179or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 180id select_type table type possible_keys key key_len ref rows Extra 1811 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where 182flush status; 183select l_orderkey, l_linenumber 184from lineitem use index (i_l_shipdate, i_l_receiptdate) 185where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 186or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 187l_orderkey l_linenumber 188130 2 1895603 2 1905959 3 191show status like 'handler_read%'; 192Variable_name Value 193Handler_read_first 0 194Handler_read_key 2 195Handler_read_last 0 196Handler_read_next 3 197Handler_read_prev 0 198Handler_read_retry 0 199Handler_read_rnd 3 200Handler_read_rnd_deleted 0 201Handler_read_rnd_next 0 202explain 203select l_orderkey, l_linenumber from lineitem 204where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 205or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 206id select_type table type possible_keys key key_len ref rows Extra 2071 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate 8,8 NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where 208flush status; 209select l_orderkey, l_linenumber from lineitem 210where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 211or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 212l_orderkey l_linenumber 213130 2 2145603 2 2155959 3 216show status like 'handler_read%'; 217Variable_name Value 218Handler_read_first 0 219Handler_read_key 2 220Handler_read_last 0 221Handler_read_next 3 222Handler_read_prev 0 223Handler_read_retry 0 224Handler_read_rnd 3 225Handler_read_rnd_deleted 0 226Handler_read_rnd_next 0 227explain 228select max(l_orderkey) from lineitem 229where l_partkey between 1 and 10 group by l_partkey; 230id select_type table type possible_keys key key_len ref rows Extra 2311 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index for group-by 232flush status; 233select max(l_orderkey) from lineitem 234where l_partkey between 1 and 10 group by l_partkey; 235max(l_orderkey) 2365984 2375957 2385892 2395856 2405959 2415957 2425794 2435894 2445859 2455632 246show status like 'handler_read%'; 247Variable_name Value 248Handler_read_first 0 249Handler_read_key 21 250Handler_read_last 1 251Handler_read_next 0 252Handler_read_prev 0 253Handler_read_retry 0 254Handler_read_rnd 0 255Handler_read_rnd_deleted 0 256Handler_read_rnd_next 0 257explain 258select max(l_orderkey) from lineitem 259where l_suppkey in (1,4) group by l_suppkey; 260id select_type table type possible_keys key key_len ref rows Extra 2611 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index for group-by 262flush status; 263select max(l_orderkey) from lineitem 264where l_suppkey in (1,4) group by l_suppkey; 265max(l_orderkey) 2665988 2675984 268show status like 'handler_read%'; 269Variable_name Value 270Handler_read_first 0 271Handler_read_key 6 272Handler_read_last 1 273Handler_read_next 0 274Handler_read_prev 0 275Handler_read_retry 0 276Handler_read_rnd 0 277Handler_read_rnd_deleted 0 278Handler_read_rnd_next 0 279create index i_p_retailprice on part(p_retailprice); 280explain 281select o_orderkey, p_partkey 282from part use index (i_p_retailprice), 283lineitem use index (i_l_partkey), orders 284where p_retailprice > 1100 and o_orderdate='1997-01-01' 285and o_orderkey=l_orderkey and p_partkey=l_partkey; 286id select_type table type possible_keys key key_len ref rows Extra 2871 SIMPLE part range i_p_retailprice i_p_retailprice 9 NULL # Using where; Using index 2881 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const # Using index 2891 SIMPLE lineitem ref i_l_partkey i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey # Using index 290flush status; 291select o_orderkey, p_partkey 292from part use index (i_p_retailprice), 293lineitem use index (i_l_partkey), orders 294where p_retailprice > 1100 and o_orderdate='1997-01-01' 295and o_orderkey=l_orderkey and p_partkey=l_partkey; 296o_orderkey p_partkey 2975895 200 298show status like 'handler_read%'; 299Variable_name Value 300Handler_read_first 0 301Handler_read_key 3 302Handler_read_last 0 303Handler_read_next 3 304Handler_read_prev 0 305Handler_read_retry 0 306Handler_read_rnd 0 307Handler_read_rnd_deleted 0 308Handler_read_rnd_next 0 309# 310# Bug mdev-3851: ref access used instead of expected eq_ref access 311# when extended_keys=on 312# 313create table t0 (a int); 314insert into t0 values (1), (2), (3), (4), (5); 315create index i_p_size on part(p_size); 316explain 317select * from t0, part ignore index (primary) 318where p_partkey=t0.a and p_size=1; 319id select_type table type possible_keys key key_len ref rows Extra 3201 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where 3211 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1 322select * from t0, part ignore index (primary) 323where p_partkey=t0.a and p_size=1; 324a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment 3252 2 blush rosy metallic lemon navajo Manufacturer#1 Brand#13 LARGE BRUSHED BRASS 1 LG CASE 902 final platelets hang f 326drop table t0; 327drop index i_p_size on part; 328DROP DATABASE dbt3_s001; 329use test; 330# 331# LP Bug #914560: query containing IN subquery 332# + extended_keys = on 333# 334set @save_optimizer_switch=@@optimizer_switch; 335SET optimizer_switch='materialization=on,semijoin=on'; 336CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; 337INSERT INTO t1 VALUES (1,1), (2,2); 338SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); 339a b 3401 1 3412 2 342EXPLAIN 343SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); 344id select_type table type possible_keys key key_len ref rows Extra 3451 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 3461 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 3472 MATERIALIZED s1 ALL NULL NULL NULL NULL 2 3482 MATERIALIZED s2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 349DROP TABLE t1; 350set optimizer_switch=@save_optimizer_switch; 351# 352# LP Bug #915291: query using a materialized view 353# + extended_keys = on 354# (valgrinf complains fixed by the patch for bug #914560) 355# 356SET optimizer_switch = 'derived_with_keys=on'; 357CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM; 358INSERT INTO t1 VALUES ('j'), ('v'); 359CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM; 360INSERT INTO t2 VALUES ('j'), ('v'); 361CREATE TABLE t3 (c varchar(1)); 362INSERT INTO t2 VALUES ('m'), ('n'); 363CREATE VIEW v 364AS SELECT DISTINCT * FROM t2 STRAIGHT_JOIN t3; 365SELECT * FROM t1, v WHERE a = b; 366a b c 367DROP VIEW v; 368DROP TABLE t1,t2,t3; 369set optimizer_switch=@save_optimizer_switch; 370# 371# LP Bug #921167: query containing IN subquery 372# + extended_keys = on 373# 374CREATE TABLE t1 ( 375a int NOT NULL, b varchar(1) NOT NULL, KEY(a), KEY(b,a) 376) ENGINE=MyISAM; 377INSERT INTO t1 VALUES 378(0,'j'), (8,'v'), (1,'c'), (8,'m'), (9,'d'), 379(24,'d'), (6,'y'), (1,'t'), (6,'d'), (2,'s'); 380CREATE TABLE t2 ( 381c int NOT NULL PRIMARY KEY 382) ENGINE=MyISAM; 383INSERT INTO t2 VALUES 384(10), (11), (12), (13), (14), 385(15), (16), (17), (18), (19), (24); 386EXPLAIN 387SELECT a FROM t1 AS t, t2 388WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); 389id select_type table type possible_keys key key_len ref rows Extra 3901 PRIMARY t index a,b b 7 NULL 10 Using index 3911 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary 3921 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 3931 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index 394SELECT a FROM t1 AS t, t2 395WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); 396a 39724 398DROP TABLE t1,t2; 399# 400# LP Bug #923236: hash join + extended_keys = on 401# 402CREATE TABLE t1 (a int) ENGINE=MyISAM; 403CREATE TABLE t2 (b int) ENGINE=MyISAM; 404INSERT INTO t1 (a) VALUES (4), (6); 405INSERT INTO t2 (b) VALUES (0), (8); 406set @save_join_cache_level=@@join_cache_level; 407SET join_cache_level=3; 408SET optimizer_switch='join_cache_hashed=on'; 409SET optimizer_switch='join_cache_bka=on'; 410EXPLAIN 411SELECT * FROM t1, t2 WHERE b=a; 412id select_type table type possible_keys key key_len ref rows Extra 4131 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 4141 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) 415SELECT * FROM t1, t2 WHERE b=a; 416a b 417set join_cache_level=@save_join_cache_level; 418set optimizer_switch=@save_optimizer_switch; 419DROP TABLE t1,t2; 420# 421# Bug mdev-3888: INSERT with UPDATE on duplicate keys 422# with extended_keys=on 423# 424CREATE TABLE t1 ( 425c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, 426c2 bigint(20) unsigned NOT NULL, 427c3 bigint(20) unsigned NOT NULL, 428c4 varchar(128) DEFAULT NULL, 429PRIMARY KEY (c1), 430UNIQUE KEY uq (c2,c3), 431KEY c3 (c3), 432KEY c4 (c4) 433) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 434INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') 435ON DUPLICATE KEY UPDATE c4 = VALUES(c4); 436INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar') 437ON DUPLICATE KEY UPDATE c4 = VALUES(c4); 438DROP TABLE t1; 439# 440# Bug mdev-4220: using ref instead of eq_ref 441# with extended_keys=on 442# (performance regression introduced in the patch for mdev-3851) 443# 444create table t1 (a int not null) engine=innodb; 445insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 446create table t2 ( 447pk int primary key, a int not null, b int, unique(a) 448)engine=innodb; 449insert into t2 450select 451A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 452from t1 A, t1 B; 453explain 454select * from t1, t2 where t2.a=t1.a and t2.b < 2; 455id select_type table type possible_keys key key_len ref rows Extra 4561 SIMPLE t1 ALL NULL NULL NULL NULL 10 4571 SIMPLE t2 eq_ref a a 4 test.t1.a 1 Using where 458flush status; 459select * from t1, t2 where t2.a=t1.a and t2.b < 2; 460a pk a b 4610 0 0 0 4621 1 1 1 463show status like 'handler_read%'; 464Variable_name Value 465Handler_read_first 0 466Handler_read_key 10 467Handler_read_last 0 468Handler_read_next 0 469Handler_read_prev 0 470Handler_read_retry 0 471Handler_read_rnd 0 472Handler_read_rnd_deleted 0 473Handler_read_rnd_next 11 474drop table t1,t2; 475create table t1(a int) engine=myisam; 476insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 477create table t2(a int) engine=myisam; 478insert into t2 select A.a + 10*B.a + 100*C.a from t1 A, t1 B, t1 C; 479create table t3 ( 480pk1 int not null, pk2 int not null, col1 int not null, col2 int not null) 481engine=innodb; 482insert into t3 select a,a,a,a from t2; 483alter table t3 add primary key (pk1, pk2); 484alter table t3 add key (col1, col2); 485analyze table t1,t3; 486Table Op Msg_type Msg_text 487test.t1 analyze status OK 488test.t3 analyze status OK 489explain 490select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a; 491id select_type table type possible_keys key key_len ref rows Extra 4921 SIMPLE t1 ALL NULL NULL NULL NULL # Using where 4931 SIMPLE t3 ref col1 col1 8 test.t1.a,test.t1.a # Using index 494explain 495select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; 496id select_type table type possible_keys key key_len ref rows Extra 4971 SIMPLE t1 ALL NULL NULL NULL NULL # Using where 4981 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index 499drop table t1,t2,t3; 500# 501# Bug mdev-4340: performance regression with extended_keys=on 502# 503CREATE TABLE t1 ( 504page_id int(8) unsigned NOT NULL AUTO_INCREMENT, 505page_namespace int(11) NOT NULL DEFAULT '0', 506page_title varbinary(255) NOT NULL DEFAULT '', 507page_restrictions tinyblob NOT NULL, 508page_counter bigint(20) unsigned NOT NULL DEFAULT '0', 509page_is_redirect tinyint(1) unsigned NOT NULL DEFAULT '0', 510page_is_new tinyint(1) unsigned NOT NULL DEFAULT '0', 511page_random double unsigned NOT NULL DEFAULT '0', 512page_touched varbinary(14) NOT NULL DEFAULT '', 513page_latest int(8) unsigned NOT NULL DEFAULT '0', 514page_len int(8) unsigned NOT NULL DEFAULT '0', 515PRIMARY KEY (page_id), 516UNIQUE KEY name_title (page_namespace,page_title), 517KEY page_random (page_random), 518KEY page_len (page_len), 519KEY page_redirect_namespace_len (page_is_redirect,page_namespace,page_len) 520) ENGINE=InnoDB AUTO_INCREMENT=38929100 DEFAULT CHARSET=binary; 521INSERT INTO t1 VALUES 522(38928077,0,'Sandbox','',0,0,0,0,'',0,0),(38928078,1,'Sandbox','',0,0,0,1,'',0,0), 523(38928079,2,'Sandbox','',0,0,0,2,'',0,0),(38928080,3,'Sandbox','',0,0,0,3,'',0,0), 524(38928081,4,'Sandbox','',0,0,0,4,'',0,0),(38928082,5,'Sandbox','',0,0,0,5,'',0,0); 525CREATE TABLE t2 ( 526rev_id int(8) unsigned NOT NULL AUTO_INCREMENT, 527rev_page int(8) unsigned NOT NULL DEFAULT '0', 528rev_text_id int(8) unsigned NOT NULL DEFAULT '0', 529rev_comment varbinary(255) DEFAULT NULL, 530rev_user int(5) unsigned NOT NULL DEFAULT '0', 531rev_user_text varbinary(255) NOT NULL DEFAULT '', 532rev_timestamp varbinary(14) NOT NULL DEFAULT '', 533rev_minor_edit tinyint(1) unsigned NOT NULL DEFAULT '0', 534rev_deleted tinyint(1) unsigned NOT NULL DEFAULT '0', 535rev_len int(8) unsigned DEFAULT NULL, 536rev_parent_id int(8) unsigned DEFAULT NULL, 537rev_sha1 varbinary(32) NOT NULL DEFAULT '', 538PRIMARY KEY (rev_page,rev_id), 539UNIQUE KEY rev_id (rev_id), 540KEY rev_timestamp (rev_timestamp), 541KEY page_timestamp (rev_page,rev_timestamp), 542KEY user_timestamp (rev_user,rev_timestamp), 543KEY usertext_timestamp (rev_user_text,rev_timestamp,rev_user,rev_deleted,rev_minor_edit,rev_text_id,rev_comment) 544) ENGINE=InnoDB DEFAULT CHARSET=binary; 545INSERT INTO t2 VALUES 546(547116222,20,0,NULL,3,'','',0,0,NULL,NULL,''),(547117245,20,0,NULL,4,'','',0,0,NULL,NULL,''), 547(547118268,20,0,NULL,5,'','',0,0,NULL,NULL,''),(547114177,21,0,NULL,1,'','',0,0,NULL,NULL,''), 548(547115200,21,0,NULL,2,'','',0,0,NULL,NULL,''),(547116223,21,0,NULL,3,'','',0,0,NULL,NULL,''), 549(547117246,21,0,NULL,4,'','',0,0,NULL,NULL,''),(547118269,21,0,NULL,5,'','',0,0,NULL,NULL,''), 550(547114178,22,0,NULL,1,'','',0,0,NULL,NULL,''),(547115201,22,0,NULL,2,'','',0,0,NULL,NULL,''), 551(547116224,22,0,NULL,3,'','',0,0,NULL,NULL,''),(547117247,22,0,NULL,4,'','',0,0,NULL,NULL,''), 552(547116226,24,0,NULL,3,'','',0,0,NULL,NULL,''),(547117249,24,0,NULL,4,'','',0,0,NULL,NULL,''), 553(547118272,24,0,NULL,5,'','',0,0,NULL,NULL,''),(547114181,25,0,NULL,1,'','',0,0,NULL,NULL,''), 554(547115204,25,0,NULL,2,'','',0,0,NULL,NULL,''),(547116227,25,0,NULL,3,'','',0,0,NULL,NULL,''), 555(547116157,978,0,NULL,2,'','',0,0,NULL,NULL,''),(547117180,978,0,NULL,3,'','',0,0,NULL,NULL,''), 556(547118203,978,0,NULL,4,'','',0,0,NULL,NULL,''),(547119226,978,0,NULL,5,'','',0,0,NULL,NULL,''), 557(547115135,979,0,NULL,1,'','',0,0,NULL,NULL,''),(547116158,979,0,NULL,2,'','',0,0,NULL,NULL,''), 558(547116173,994,0,NULL,2,'','',0,0,NULL,NULL,''),(547117196,994,0,NULL,3,'','',0,0,NULL,NULL,''), 559(547118219,994,0,NULL,4,'','',0,0,NULL,NULL,''),(547119242,994,0,NULL,5,'','',0,0,NULL,NULL,''), 560(547115151,995,0,NULL,1,'','',0,0,NULL,NULL,''),(547116174,995,0,NULL,2,'','',0,0,NULL,NULL,''), 561(547117197,995,0,NULL,3,'','',0,0,NULL,NULL,''),(547118220,995,0,NULL,4,'','',0,0,NULL,NULL,''), 562(547118223,998,0,NULL,4,'','',0,0,NULL,NULL,''),(547119246,998,0,NULL,5,'','',0,0,NULL,NULL,''), 563(547115155,999,0,NULL,1,'','',0,0,NULL,NULL,''),(547116178,999,0,NULL,2,'','',0,0,NULL,NULL,''), 564(547117201,999,0,NULL,3,'','',0,0,NULL,NULL,''),(547118224,999,0,NULL,4,'','',0,0,NULL,NULL,''), 565(547119271,38928081,0,NULL,10,'','',0,0,NULL,NULL,''),(547119272,38928081,0,NULL,11,'','',0,0,NULL,NULL,''), 566(547119273,38928081,0,NULL,12,'','',0,0,NULL,NULL,''),(547119274,38928081,0,NULL,13,'','',0,0,NULL,NULL,''), 567(547119275,38928081,0,NULL,14,'','',0,0,NULL,NULL,''),(547119276,38928081,0,NULL,15,'','',0,0,NULL,NULL,''), 568(547119277,38928081,0,NULL,16,'','',0,0,NULL,NULL,''),(547119278,38928081,0,NULL,17,'','',0,0,NULL,NULL,''), 569(547119279,38928081,0,NULL,18,'','',0,0,NULL,NULL,''),(547119280,38928081,0,NULL,19,'','',0,0,NULL,NULL,''); 570CREATE TABLE t3 ( 571old_id int(10) unsigned NOT NULL AUTO_INCREMENT, 572old_text mediumblob NOT NULL, 573old_flags tinyblob NOT NULL, 574PRIMARY KEY (old_id) 575) ENGINE=InnoDB DEFAULT CHARSET=latin1; 576INSERT INTO t3 VALUES 577(1,'text-0',''),(2,'text-1000',''),(3,'text-2000',''),(4,'text-3000',''), 578(5,'text-4000',''),(6,'text-5000',''),(7,'text-6000',''),(8,'text-7000',''), 579(9,'text-8000',''),(10,'text-9000',''),(11,'text-1',''),(12,'text-1001',''), 580(13,'text-2001',''),(14,'text-3001',''),(15,'text-4001',''),(16,'text-5001',''), 581(17,'text-6001',''),(18,'text-7001',''),(19,'text-8001',''),(20,'text-9001',''), 582(21,'text-2',''),(22,'text-1002',''),(23,'text-2002',''),(24,'text-3002',''), 583(25,'text-4002',''),(26,'text-5002',''),(27,'text-6002',''),(28,'text-7002',''), 584(29,'text-8002',''),(30,'text-9002',''),(31,'text-3',''),(32,'text-1003',''), 585(33,'text-2003',''),(34,'text-3003',''),(35,'text-4003',''),(36,'text-5003',''), 586(37,'text-6003',''),(38,'text-7003',''),(39,'text-8003',''),(40,'text-9003',''), 587(41,'text-4',''),(42,'text-1004',''),(43,'text-2004',''),(44,'text-3004',''), 588(45,'text-4004',''),(46,'text-5004',''),(47,'text-6004',''),(48,'text-7004',''), 589(49,'text-8004',''),(50,'text-9004',''),(51,'text-5',''),(52,'text-1005',''), 590(53,'text-2005',''),(54,'text-3005',''),(55,'text-4005',''),(56,'text-5005',''), 591(57,'text-6005',''),(58,'text-7005',''),(59,'text-8005',''),(60,'text-9005',''), 592(61,'text-6',''),(62,'text-1006',''),(63,'text-2006',''),(64,'text-3006',''), 593(65,'text-4006',''),(66,'text-5006',''),(67,'text-6006',''),(68,'text-7006',''), 594(69,'text-8006',''),(70,'text-9006',''),(71,'text-7',''),(72,'text-1007',''), 595(73,'text-2007',''),(74,'text-3007',''),(75,'text-4007',''),(76,'text-5007',''), 596(77,'text-6007',''),(78,'text-7007',''),(79,'text-8007',''),(80,'text-9007',''), 597(81,'text-8',''),(82,'text-1008',''),(83,'text-2008',''),(84,'text-3008',''), 598(85,'text-4008',''),(86,'text-5008',''),(87,'text-6008',''),(88,'text-7008',''), 599(89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''), 600(93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''), 601(97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009',''); 602EXPLAIN 603SELECT * FROM t1, t2 IGNORE INDEX (PRIMARY), t3 604WHERE page_id=rev_page AND rev_text_id=old_id AND page_namespace=4 AND page_title='Sandbox' 605ORDER BY rev_timestamp ASC LIMIT 10; 606id select_type table type possible_keys key key_len ref rows Extra 6071 SIMPLE t1 const PRIMARY,name_title name_title 261 const,const 1 6081 SIMPLE t2 ref page_timestamp page_timestamp 4 const 10 Using where 6091 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.rev_text_id 1 610DROP TABLE t1,t2,t3; 611# 612# MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected 613# results (InnoDB/XtraDB) 614# 615create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8; 616create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8; 617insert into t1 (b) values (null), (null), (null); 618insert into t2 (b) values (null), (null), (null); 619explain select a from t1 where b is null order by a desc limit 2; 620id select_type table type possible_keys key key_len ref rows Extra 6211 SIMPLE t1 index b PRIMARY 8 NULL 3 Using where 622select a from t1 where b is null order by a desc limit 2; 623a 6243 6252 626explain select a from t2 where b is null order by a desc limit 2; 627id select_type table type possible_keys key key_len ref rows Extra 6281 SIMPLE t2 range b b 9 NULL 3 Using where; Using filesort 629select a from t2 where b is null order by a desc limit 2; 630a 6313 6322 633explain select a from t2 where b is null order by a desc; 634id select_type table type possible_keys key key_len ref rows Extra 6351 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where 636select a from t2 where b is null order by a desc; 637a 6383 6392 6401 641explain select a from t2 where b is null order by a desc,a,a; 642id select_type table type possible_keys key key_len ref rows Extra 6431 SIMPLE t2 index b PRIMARY 8 NULL 3 Using where 644select a from t2 where b is null order by a desc,a,a; 645a 6463 6472 6481 649drop table t1, t2; 650# 651# MDEV-10325: Queries examines all rows of a tables when it should not 652# 653create table t0 (a int); 654insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 655create table t1 ( 656pk int not null, 657col1 varchar(32), 658filler varchar(100), 659key idx1(col1(10)), 660primary key (pk) 661)engine=innodb; 662insert into t1 663select 664A.a + 10*B.a + 100*C.a, 665concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a), 666repeat('filler-data-', 4) 667from 668t0 A, t0 B, t0 C; 669drop table t0,t1; 670# 671# 672# MDEV-10360: Extended keys: index properties depend on index order 673# 674create table t0 (a int); 675insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 676create table t1 ( 677index_id bigint(20) unsigned NOT NULL, 678index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , 679index_object_id int(10) unsigned NOT NULL DEFAULT '0' , 680index_date_updated int(10) unsigned DEFAULT NULL , 681PRIMARY KEY (index_id), 682KEY object (index_class(181),index_object_id), 683KEY index_date_updated (index_date_updated) 684) engine=innodb; 685create table t2 ( 686index_id bigint(20) unsigned NOT NULL, 687index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , 688index_object_id int(10) unsigned NOT NULL DEFAULT '0' , 689index_date_updated int(10) unsigned DEFAULT NULL , 690PRIMARY KEY (index_id), 691KEY index_date_updated (index_date_updated), 692KEY object (index_class(181),index_object_id) 693) engine=innodb; 694insert into t1 select 695@a:=A.a + 10*B.a + 100*C.a, 696concat('val-', @a), 697123456, 698A.a + 10*B.a 699from 700t0 A, t0 B, t0 C; 701insert into t2 select * from t1; 702# This must have the same query plan as the query below it: 703# type=range, key=index_date_updated, key_len=13 704explain 705select * from t1 force index(index_date_updated) 706where index_date_updated= 10 and index_id < 800; 707id select_type table type possible_keys key key_len ref rows Extra 7081 SIMPLE t1 range index_date_updated index_date_updated 13 NULL # Using index condition 709# This used to work from the start: 710explain 711select * from t2 force index(index_date_updated) 712where index_date_updated= 10 and index_id < 800; 713id select_type table type possible_keys key key_len ref rows Extra 7141 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition 715drop table t0,t1,t2; 716# 717# MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' 718# was corrupted, server crashes in opt_sum_query 719SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@@optimizer_use_condition_selectivity=1; 720CREATE TABLE t1 ( 721pk INT, 722f1 VARCHAR(3), 723f2 VARCHAR(1024), 724PRIMARY KEY (pk), 725KEY(f2) 726) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; 727INSERT INTO t1 VALUES (1,'foo','abc'),(2,'bar','def'); 728SELECT MAX(t2.pk) FROM t1 t2 INNER JOIN t1 t3 ON t2.f1 = t3.f1 WHERE t2.pk <= 4; 729MAX(t2.pk) 7302 731drop table t1; 732CREATE TABLE t1 ( 733pk1 INT, 734pk2 INT, 735f1 VARCHAR(3), 736f2 VARCHAR(1021), 737PRIMARY KEY (pk1,pk2), 738KEY(f2) 739) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; 740INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); 741explain format= json 742select * from t1 force index(f2) where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3'; 743EXPLAIN 744{ 745 "query_block": { 746 "select_id": 1, 747 "table": { 748 "table_name": "t1", 749 "access_type": "range", 750 "possible_keys": ["f2"], 751 "key": "f2", 752 "key_length": "3070", 753 "used_key_parts": ["f2", "pk1"], 754 "rows": 1, 755 "filtered": 100, 756 "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", 757 "attached_condition": "t1.f1 <= '3'" 758 } 759 } 760} 761drop table t1; 762CREATE TABLE t1 ( 763f2 INT, 764pk2 INT, 765f1 VARCHAR(3), 766pk1 VARCHAR(1000), 767PRIMARY KEY (pk1,pk2), 768KEY k1(pk1,f2) 769) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; 770INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); 771explain format= json 772select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; 773EXPLAIN 774{ 775 "query_block": { 776 "select_id": 1, 777 "table": { 778 "table_name": "t1", 779 "access_type": "range", 780 "possible_keys": ["k1"], 781 "key": "k1", 782 "key_length": "3011", 783 "used_key_parts": ["pk1", "f2", "pk2"], 784 "rows": 1, 785 "filtered": 100, 786 "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", 787 "attached_condition": "t1.f1 <= '3'" 788 } 789 } 790} 791drop table t1; 792SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 793# 794# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index 795# 796CREATE TABLE t1(a INT); 797INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 798CREATE TABLE t2 ( 799pk VARCHAR(50), 800a VARCHAR(20), 801KEY k1(a), 802PRIMARY KEY(pk) 803)ENGINE=INNODB; 804INSERT INTO t2 SELECT a,a FROM t1; 805EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); 806id select_type table type possible_keys key key_len ref rows Extra 8071 SIMPLE t2 index NULL k1 23 NULL # Using index 808DROP TABLE t1,t2; 809