1drop table if exists t1,t2,t3; 2SET SQL_WARNINGS=1; 3CREATE TABLE t1 ( 4ID CHAR(32) NOT NULL, 5name CHAR(32) NOT NULL, 6value CHAR(255), 7INDEX indexIDname (ID(8),name(8)) 8) ; 9INSERT INTO t1 VALUES 10('keyword','indexdir','/export/home/local/www/database/indexes/keyword'); 11INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text'); 12INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /'); 13INSERT INTO t1 VALUES ('keyword','attr','personal employee company'); 14INSERT INTO t1 VALUES 15('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids'); 16INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text'); 17INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /'); 18INSERT INTO t1 VALUES ('emailgids','attr','personal employee company'); 19SELECT value FROM t1 WHERE ID='emailgids' AND name='attr'; 20value 21personal employee company 22drop table t1; 23CREATE TABLE t1 ( 24price int(5) DEFAULT '0' NOT NULL, 25area varchar(40) DEFAULT '' NOT NULL, 26type varchar(40) DEFAULT '' NOT NULL, 27transityes enum('Y','N') DEFAULT 'Y' NOT NULL, 28shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 29schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 30petsyes enum('Y','N') DEFAULT 'Y' NOT NULL, 31KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes) 32); 33INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); 34INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); 35INSERT IGNORE INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','',''); 36Warnings: 37Warning 1265 Data truncated for column 'transityes' at row 1 38Warning 1265 Data truncated for column 'shopsyes' at row 1 39Warning 1265 Data truncated for column 'schoolsyes' at row 1 40Warning 1265 Data truncated for column 'petsyes' at row 1 41INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 42INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 43INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 44INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 45INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); 46SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400; 47price area type transityes shopsyes schoolsyes petsyes 48drop table t1; 49CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program)); 50ALTER TABLE t1 modify program enum('signup','unique','sliding'); 51drop table t1; 52CREATE TABLE t1 ( 53name varchar(50) DEFAULT '' NOT NULL, 54author varchar(50) DEFAULT '' NOT NULL, 55category decimal(10,0) DEFAULT '0' NOT NULL, 56email varchar(50), 57password varchar(50), 58proxy varchar(50), 59bitmap varchar(20), 60msg varchar(255), 61urlscol varchar(127), 62urlhttp varchar(127), 63timeout decimal(10,0), 64nbcnx decimal(10,0), 65creation decimal(10,0), 66livinguntil decimal(10,0), 67lang decimal(10,0), 68type decimal(10,0), 69subcat decimal(10,0), 70subtype decimal(10,0), 71reg char(1), 72scs varchar(255), 73capacity decimal(10,0), 74userISP varchar(50), 75CCident varchar(50) DEFAULT '' NOT NULL, 76PRIMARY KEY (name,author,category) 77); 78INSERT INTO t1 VALUES 79('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); 80INSERT INTO t1 VALUES 81('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1'); 82select * from t1 where name='patnom' and author='patauteur' and category=0; 83name author category email password proxy bitmap msg urlscol urlhttp timeout nbcnx creation livinguntil lang type subcat subtype reg scs capacity userISP CCident 84patnom patauteur 0 p.favre@cryo-networks.fr NULL NULL #p2sndnq6ae5g1u6t essai salut scol://195.242.78.119:patauteur.patnom NULL NULL NULL 950036174 -882087474 NULL 3 0 3 1 Pub/patnom/futur_divers.scs NULL pat CC1 85drop table t1; 86create table t1 87( 88name_id int not null auto_increment, 89name blob, 90INDEX name_idx (name(5)), 91primary key (name_id) 92); 93INSERT t1 VALUES(NULL,'/'); 94INSERT t1 VALUES(NULL,'[T,U]_axpby'); 95SELECT * FROM t1 WHERE name='[T,U]_axpy'; 96name_id name 97SELECT * FROM t1 WHERE name='[T,U]_axpby'; 98name_id name 992 [T,U]_axpby 100create table t2 101( 102name_id int not null auto_increment, 103name char(255) binary, 104INDEX name_idx (name(5)), 105primary key (name_id) 106); 107INSERT t2 select * from t1; 108SELECT * FROM t2 WHERE name='[T,U]_axpy'; 109name_id name 110SELECT * FROM t2 WHERE name='[T,U]_axpby'; 111name_id name 1122 [T,U]_axpby 113CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby'; 114SELECT * FROM t2 WHERE name='[T,U]_axpby'; 115name_id name 1162 [T,U]_axpby 117drop table t1,t2,t3; 118create table t1 119( 120SEQNO numeric(12 ) not null, 121MOTYPEID numeric(12 ) not null, 122MOINSTANCEID numeric(12 ) not null, 123ATTRID numeric(12 ) not null, 124VALUE varchar(120) not null, 125primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE ) 126); 127INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); 128INSERT INTO t1 VALUES (1, 1, 1, 1, 'b'); 129INSERT INTO t1 VALUES (1, 1, 1, 1, 'a'); 130ERROR 23000: Duplicate entry '1-1-1-1-a' for key 'PRIMARY' 131drop table t1; 132CREATE TABLE t1 ( 133a tinytext NOT NULL, 134b tinyint(3) unsigned NOT NULL default '0', 135PRIMARY KEY (a(32),b) 136) ENGINE=MyISAM; 137INSERT INTO t1 VALUES ('a',1),('a',2); 138SELECT * FROM t1 WHERE a='a' AND b=2; 139a b 140a 2 141SELECT * FROM t1 WHERE a='a' AND b in (2); 142a b 143a 2 144SELECT * FROM t1 WHERE a='a' AND b in (1,2); 145a b 146a 1 147a 2 148drop table t1; 149create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); 150show keys from t1; 151Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 152t1 0 PRIMARY 1 d A 0 NULL NULL BTREE 153t1 0 a 1 a A 0 NULL NULL BTREE 154t1 0 e 1 e A 0 NULL NULL BTREE 155t1 0 b 1 b A NULL NULL NULL YES BTREE 156t1 1 c 1 c A NULL NULL NULL YES BTREE 157drop table t1; 158CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, 159UNIQUE (c,i)); 160INSERT IGNORE INTO t1 (c) VALUES (NULL),(NULL); 161Warnings: 162Warning 1048 Column 'c' cannot be null 163Warning 1048 Column 'c' cannot be null 164SELECT * FROM t1; 165c i 166 1 167 2 168INSERT INTO t1 (c) VALUES ('a'),('a'); 169SELECT * FROM t1; 170c i 171 1 172 2 173a 1 174a 2 175DROP TABLE IF EXISTS t1; 176CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, 177UNIQUE (c,i)); 178INSERT INTO t1 (c) VALUES (NULL),(NULL); 179SELECT * FROM t1; 180c i 181NULL 1 182NULL 2 183INSERT INTO t1 (c) VALUES ('a'),('a'); 184SELECT * FROM t1; 185c i 186NULL 1 187NULL 2 188a 1 189a 2 190drop table t1; 191create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8; 192insert ignore t1 values (1, repeat('a',210), repeat('b', 310)); 193Warnings: 194Warning 1265 Data truncated for column 'a' at row 1 195insert ignore t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310)); 196Warnings: 197Warning 1265 Data truncated for column 'a' at row 1 198select i, length(a), length(b), char_length(a), char_length(b) from t1; 199i length(a) length(b) char_length(a) char_length(b) 2001 200 310 200 310 2012 400 620 200 310 202select i from t1 where a=repeat(_utf8 'a',200); 203i 2041 205select i from t1 where a=repeat(_utf8 0xD0B1,200); 206i 2072 208select i from t1 where b=repeat(_utf8 'b',310); 209i 2101 211drop table t1; 212CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam; 213insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); 214explain select 1 from t1 where id =2; 215id select_type table type possible_keys key key_len ref rows Extra 2161 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 217explain select 1 from t1 where id =2 or id=3; 218id select_type table type possible_keys key key_len ref rows Extra 2191 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index 220explain select name from t1 where id =2; 221id select_type table type possible_keys key key_len ref rows Extra 2221 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 223ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id); 224explain select 1 from t1 where id =2; 225id select_type table type possible_keys key key_len ref rows Extra 2261 SIMPLE t1 ref id id 4 const 1 Using index 227drop table t1; 228CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); 229INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); 230SELECT numeropost FROM t1 WHERE numreponse='1'; 231numeropost 2321 233EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; 234id select_type table type possible_keys key key_len ref rows Extra 2351 SIMPLE t1 const numreponse numreponse 4 const 1 Using index 236FLUSH TABLES; 237SELECT numeropost FROM t1 WHERE numreponse='1'; 238numeropost 2391 240drop table t1; 241create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; 242show create table t1; 243Table Create Table 244t1 CREATE TABLE `t1` ( 245 `c` varchar(30) CHARACTER SET utf8 DEFAULT NULL, 246 `t` text CHARACTER SET utf8 DEFAULT NULL, 247 UNIQUE KEY `c` (`c`(2)), 248 UNIQUE KEY `t` (`t`(3)) 249) ENGINE=MyISAM DEFAULT CHARSET=latin1 250insert t1 values ('cccc', 'tttt'), 251(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), 252(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); 253insert t1 (c) values ('cc22'); 254ERROR 23000: Duplicate entry 'cc' for key 'c' 255insert t1 (t) values ('ttt22'); 256ERROR 23000: Duplicate entry 'ttt' for key 't' 257insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); 258ERROR 23000: Duplicate entry '\0431!' for key 'c' 259insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); 260ERROR 23000: Duplicate entry '\0431\0431!' for key 't' 261select c from t1 where c='cccc'; 262c 263cccc 264select t from t1 where t='tttt'; 265t 266tttt 267select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; 268c 269?!"#????? 270select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; 271t 272??!"#???? 273drop table t1; 274DROP TABLE IF EXISTS t1; 275Warnings: 276Note 1051 Unknown table 'test.t1' 277CREATE TABLE t1 ( 278c1 int, 279c2 varbinary(240), 280UNIQUE KEY (c1), 281KEY (c2) 282) ENGINE=MyISAM; 283INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z'); 284INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z'); 285INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z'); 286select c1 from t1 where c2='\Z\Z\Z\Z'; 287c1 2881 2893 290DELETE FROM t1 WHERE (c1 = 1); 291check table t1; 292Table Op Msg_type Msg_text 293test.t1 check status OK 294select c1 from t1 where c2='\Z\Z\Z\Z'; 295c1 2963 297DELETE FROM t1 WHERE (c1 = 3); 298check table t1; 299Table Op Msg_type Msg_text 300test.t1 check status OK 301select c1 from t1 where c2='\Z\Z\Z\Z'; 302c1 303truncate table t1; 304insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc"); 305delete from t1 where c1=3; 306delete from t1 where c1=1; 307delete from t1 where c1=4; 308check table t1; 309Table Op Msg_type Msg_text 310test.t1 check status OK 311drop table t1; 312create table t1 (c char(10), index (c(0))); 313ERROR HY000: Key part 'c' length cannot be 0 314create table t1 (c char(10), index (c,c)); 315ERROR 42S21: Duplicate column name 'c' 316create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)); 317ERROR 42S21: Duplicate column name 'c1' 318create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)); 319ERROR 42S21: Duplicate column name 'c1' 320create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)); 321ERROR 42S21: Duplicate column name 'c1' 322create table t1 (c1 char(10), c2 char(10)); 323alter table t1 add key (c1,c1); 324ERROR 42S21: Duplicate column name 'c1' 325alter table t1 add key (c2,c1,c1); 326ERROR 42S21: Duplicate column name 'c1' 327alter table t1 add key (c1,c2,c1); 328ERROR 42S21: Duplicate column name 'c1' 329alter table t1 add key (c1,c1,c2); 330ERROR 42S21: Duplicate column name 'c1' 331drop table t1; 332create table t1 ( 333i1 INT NOT NULL, 334i2 INT NOT NULL, 335UNIQUE i1idx (i1), 336UNIQUE i2idx (i2)); 337desc t1; 338Field Type Null Key Default Extra 339i1 int(11) NO PRI NULL 340i2 int(11) NO UNI NULL 341show create table t1; 342Table Create Table 343t1 CREATE TABLE `t1` ( 344 `i1` int(11) NOT NULL, 345 `i2` int(11) NOT NULL, 346 UNIQUE KEY `i1idx` (`i1`), 347 UNIQUE KEY `i2idx` (`i2`) 348) ENGINE=MyISAM DEFAULT CHARSET=latin1 349drop table t1; 350create table t1 ( 351c1 int, 352c2 varchar(20) not null, 353primary key (c1), 354key (c2(10)) 355) engine=myisam; 356insert into t1 values (1,''); 357insert into t1 values (2,' \t\tTest String'); 358insert into t1 values (3,' \n\tTest String'); 359update t1 set c2 = 'New Test String' where c1 = 1; 360select * from t1; 361c1 c2 3621 New Test String 3632 Test String 3643 365 Test String 366drop table t1; 367create table t1 (a varchar(10), b varchar(10), key(a(10),b(10))); 368show create table t1; 369Table Create Table 370t1 CREATE TABLE `t1` ( 371 `a` varchar(10) DEFAULT NULL, 372 `b` varchar(10) DEFAULT NULL, 373 KEY `a` (`a`,`b`) 374) ENGINE=MyISAM DEFAULT CHARSET=latin1 375alter table t1 modify b varchar(20); 376show create table t1; 377Table Create Table 378t1 CREATE TABLE `t1` ( 379 `a` varchar(10) DEFAULT NULL, 380 `b` varchar(20) DEFAULT NULL, 381 KEY `a` (`a`,`b`) 382) ENGINE=MyISAM DEFAULT CHARSET=latin1 383alter table t1 modify a varchar(20); 384show create table t1; 385Table Create Table 386t1 CREATE TABLE `t1` ( 387 `a` varchar(20) DEFAULT NULL, 388 `b` varchar(20) DEFAULT NULL, 389 KEY `a` (`a`,`b`) 390) ENGINE=MyISAM DEFAULT CHARSET=latin1 391drop table t1; 392create table t1 (a int not null primary key, b varchar(20) not null unique); 393desc t1; 394Field Type Null Key Default Extra 395a int(11) NO PRI NULL 396b varchar(20) NO UNI NULL 397drop table t1; 398create table t1 (a int not null primary key, b int not null unique); 399desc t1; 400Field Type Null Key Default Extra 401a int(11) NO PRI NULL 402b int(11) NO UNI NULL 403drop table t1; 404create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10))); 405desc t1; 406Field Type Null Key Default Extra 407a int(11) NO PRI NULL 408b varchar(20) NO UNI NULL 409drop table t1; 410create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10))); 411desc t1; 412Field Type Null Key Default Extra 413a int(11) NO PRI NULL 414b varchar(20) NO MUL NULL 415c varchar(20) NO NULL 416drop table t1; 417create table t1 ( 418c1 int, 419c2 char(12), 420c3 varchar(123), 421c4 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 422index (c1), 423index i1 (c1), 424index i2 (c2), 425index i3 (c3), 426unique i4 (c4), 427index i5 (c1, c2, c3, c4), 428primary key (c2, c3), 429index (c2, c4)); 430Warnings: 431Note 1831 Duplicate index `i1`. This is deprecated and will be disallowed in a future release 432show create table t1; 433Table Create Table 434t1 CREATE TABLE `t1` ( 435 `c1` int(11) DEFAULT NULL, 436 `c2` char(12) NOT NULL, 437 `c3` varchar(123) NOT NULL, 438 `c4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 439 PRIMARY KEY (`c2`,`c3`), 440 UNIQUE KEY `i4` (`c4`), 441 KEY `c1` (`c1`), 442 KEY `i1` (`c1`), 443 KEY `i2` (`c2`), 444 KEY `i3` (`c3`), 445 KEY `i5` (`c1`,`c2`,`c3`,`c4`), 446 KEY `c2` (`c2`,`c4`) 447) ENGINE=MyISAM DEFAULT CHARSET=latin1 448alter table t1 drop index c1; 449alter table t1 add index (c1); 450Warnings: 451Note 1831 Duplicate index `c1`. This is deprecated and will be disallowed in a future release 452alter table t1 add index (c1); 453Warnings: 454Note 1831 Duplicate index `c1_2`. This is deprecated and will be disallowed in a future release 455alter table t1 drop index i3; 456alter table t1 add index i3 (c3); 457alter table t1 drop index i2, drop index i4; 458alter table t1 add index i2 (c2), add index i4 (c4); 459alter table t1 drop index i2, drop index i4, add index i6 (c2, c4); 460Warnings: 461Note 1831 Duplicate index `i6`. This is deprecated and will be disallowed in a future release 462alter table t1 add index i2 (c2), add index i4 (c4), drop index i6; 463alter table t1 drop index i2, drop index i4, add unique i4 (c4); 464alter table t1 add index i2 (c2), drop index i4, add index i4 (c4); 465alter table t1 drop index c2, add index (c2(4),c3(7)); 466alter table t1 drop index c2, add index (c2(4),c3(7)); 467alter table t1 add primary key (c1, c2), drop primary key; 468alter table t1 drop primary key; 469alter table t1 add primary key (c1, c2), drop primary key; 470ERROR 42000: Can't DROP INDEX `PRIMARY`; check that it exists 471show create table t1; 472Table Create Table 473t1 CREATE TABLE `t1` ( 474 `c1` int(11) NOT NULL, 475 `c2` char(12) NOT NULL, 476 `c3` varchar(123) NOT NULL, 477 `c4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 478 KEY `i1` (`c1`), 479 KEY `i5` (`c1`,`c2`,`c3`,`c4`), 480 KEY `c1` (`c1`), 481 KEY `c1_2` (`c1`), 482 KEY `i3` (`c3`), 483 KEY `i2` (`c2`), 484 KEY `i4` (`c4`), 485 KEY `c2` (`c2`(4),`c3`(7)) 486) ENGINE=MyISAM DEFAULT CHARSET=latin1 487insert into t1 values(1, 'a', 'a', NULL); 488insert into t1 values(1, 'b', 'b', NULL); 489alter table t1 drop index i3, drop index i2, drop index i1; 490alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1); 491ERROR 23000: Duplicate entry '1' for key 'i1' 492drop table t1; 493CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM; 494INSERT INTO t1 VALUES( 1 ); 495ALTER TABLE t1 DISABLE KEYS; 496EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a); 497id select_type table type possible_keys key key_len ref rows Extra 4981 SIMPLE t1 system NULL NULL NULL NULL 1 499drop table t1; 500CREATE TABLE t1 ( 501a INTEGER auto_increment PRIMARY KEY, 502b INTEGER NOT NULL, 503c INTEGER NOT NULL, 504d CHAR(64) 505); 506CREATE TABLE t2 ( 507a INTEGER auto_increment PRIMARY KEY, 508b INTEGER NOT NULL, 509c SMALLINT NOT NULL, 510d DATETIME NOT NULL, 511e SMALLINT NOT NULL, 512f INTEGER NOT NULL, 513g INTEGER NOT NULL, 514h SMALLINT NOT NULL, 515i INTEGER NOT NULL, 516j INTEGER NOT NULL, 517UNIQUE INDEX (b), 518INDEX (b, d, e, f, g, h, i, j, c), 519INDEX (c) 520); 521INSERT INTO t2 VALUES 522(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0), 523(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0), 524(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0), 525(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0), 526(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0), 527(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0), 528(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0), 529(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0); 530INSERT INTO t1 (b, c, d) VALUES 531(3388000, -553000, NULL), 532(3388000, -553000, NULL); 533SELECT * 534FROM t2 c JOIN t1 pa ON c.b = pa.a 535WHERE c.c = 1 536ORDER BY c.b, c.d 537; 538a b c d e f g h i j a b c d 5392 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL 540DROP TABLE t1, t2; 541create table t1(a int not null, key aa(a), 542b char(10) not null, unique key bb(b(1)), 543c char(4) not null, unique key cc(c)); 544desc t1; 545Field Type Null Key Default Extra 546a int(11) NO MUL NULL 547b char(10) NO UNI NULL 548c char(4) NO PRI NULL 549show create table t1; 550Table Create Table 551t1 CREATE TABLE `t1` ( 552 `a` int(11) NOT NULL, 553 `b` char(10) NOT NULL, 554 `c` char(4) NOT NULL, 555 UNIQUE KEY `cc` (`c`), 556 UNIQUE KEY `bb` (`b`(1)), 557 KEY `aa` (`a`) 558) ENGINE=MyISAM DEFAULT CHARSET=latin1 559drop table t1; 560create table t1(a int not null, key aa(a), 561b char(10) not null, unique key bb(b(1)), 562c char(4) not null); 563desc t1; 564Field Type Null Key Default Extra 565a int(11) NO MUL NULL 566b char(10) NO UNI NULL 567c char(4) NO NULL 568alter table t1 add unique key cc(c); 569desc t1; 570Field Type Null Key Default Extra 571a int(11) NO MUL NULL 572b char(10) NO UNI NULL 573c char(4) NO PRI NULL 574show create table t1; 575Table Create Table 576t1 CREATE TABLE `t1` ( 577 `a` int(11) NOT NULL, 578 `b` char(10) NOT NULL, 579 `c` char(4) NOT NULL, 580 UNIQUE KEY `cc` (`c`), 581 UNIQUE KEY `bb` (`b`(1)), 582 KEY `aa` (`a`) 583) ENGINE=MyISAM DEFAULT CHARSET=latin1 584drop table t1; 585End of 5.0 tests 586DROP TABLE IF EXISTS t1; 587CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT); 588INSERT INTO t1 VALUES (), (), (); 589SELECT 1 AS c1 590FROM t1 591ORDER BY ( 592SELECT 1 AS c2 593FROM t1 594GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC 595LIMIT 1); 596c1 5971 5981 5991 600DROP TABLE t1; 601CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); 602INSERT INTO t1 (a, b) 603VALUES 604(1,1), (1,2), (1,3), (1,4), (1,5), 605(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); 606ANALYZE table t1; 607Table Op Msg_type Msg_text 608test.t1 analyze status Engine-independent statistics collected 609test.t1 analyze status OK 610EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 611(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 612id select_type table type possible_keys key key_len ref rows Extra 6131 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 6142 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index for group-by 615SELECT 1 as RES FROM t1 AS t1_outer WHERE 616(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; 617RES 618DROP TABLE t1; 619# 620# Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. 621# 622# We are interested in showing that the cost for the last plan is higher 623# than for the preceding two plans. 624# 625CREATE TABLE t1( a INT, b INT, KEY( a ) ); 626INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); 627EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; 628id select_type table type possible_keys key key_len ref rows Extra 6291 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 630SHOW STATUS LIKE 'Last_query_cost'; 631Variable_name Value 632Last_query_cost 9.212184 633EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; 634id select_type table type possible_keys key key_len ref rows Extra 6351 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 636SHOW STATUS LIKE 'Last_query_cost'; 637Variable_name Value 638Last_query_cost 9.212184 639EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; 640id select_type table type possible_keys key key_len ref rows Extra 6411 SIMPLE t1 index NULL a 5 NULL 6 642SHOW STATUS LIKE 'Last_query_cost'; 643Variable_name Value 644Last_query_cost 14.199000 645DROP TABLE t1; 646# 647# MDEV-21480: Unique key using ref access though eq_ref access can be used 648# 649create table t1(a int, b int,c int, primary key(a), unique key(b,c)); 650insert into t1 select seq, seq, seq from seq_1_to_10; 651create table t2(a int, b int,c int); 652insert into t2 select seq, seq, seq+1 from seq_1_to_100; 653EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 654id select_type table type possible_keys key key_len ref rows Extra 6551 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where 6561 SIMPLE t1 eq_ref b b 10 test.t2.a,test.t2.b 1 Using index 657SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 658c c 6591 2 6602 3 6613 4 6624 5 6635 6 6646 7 6657 8 6668 9 6679 10 66810 11 669alter table t1 drop PRIMARY KEY; 670alter table t1 add PRIMARY KEY(b,c); 671EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 672id select_type table type possible_keys key key_len ref rows Extra 6731 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where 6741 SIMPLE t1 eq_ref PRIMARY,b PRIMARY 8 test.t2.a,test.t2.b 1 Using index 675SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; 676c c 6771 2 6782 3 6793 4 6804 5 6815 6 6826 7 6837 8 6848 9 6859 10 68610 11 687drop table t1,t2; 688