1
drop table if exists t1,t2,t3;
2
CREATE TABLE t1 (id int,facility char(20));
3
CREATE TABLE t2 (facility char(20));
4
INSERT INTO t1 VALUES (NULL,NULL);
5
INSERT INTO t1 VALUES (-1,'');
6
INSERT INTO t1 VALUES (0,'');
7
INSERT INTO t1 VALUES (1,'/L');
8
INSERT INTO t1 VALUES (2,'A01');
9
INSERT INTO t1 VALUES (3,'ANC');
10
INSERT INTO t1 VALUES (4,'F01');
11
INSERT INTO t1 VALUES (5,'FBX');
12
INSERT INTO t1 VALUES (6,'MT');
13
INSERT INTO t1 VALUES (7,'P');
14
INSERT INTO t1 VALUES (8,'RV');
15
INSERT INTO t1 VALUES (9,'SRV');
16
INSERT INTO t1 VALUES (10,'VMT');
17
INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;
18
select id from t1 group by id;
33
select * from t1 order by id;
48
select id-5,facility from t1 order by "id-5";
63
select id,concat(facility) from t1 group by id ;
78
select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
93
select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
98
SELECT DISTINCT FACILITY FROM t1;
112
SELECT FACILITY FROM t2;
126
SELECT count(*) from t1,t2 where t1.facility=t2.facility;
129
select count(facility) from t1;
132
select count(*) from t1;
135
select count(*) from t1 where facility IS NULL;
138
select count(*) from t1 where facility = NULL;
141
select count(*) from t1 where facility IS NOT NULL;
144
select count(*) from t1 where id IS NULL;
147
select count(*) from t1 where id IS NOT NULL;
151
CREATE TABLE t1 (UserId int DEFAULT '0' NOT NULL);
152
INSERT INTO t1 VALUES (20);
153
INSERT INTO t1 VALUES (27);
154
SELECT UserId FROM t1 WHERE Userid=22;
156
SELECT UserId FROM t1 WHERE UserId=22 group by Userid;
158
SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;
160
SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
163
CREATE TABLE t1 (a int not null primary key,b int);
164
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
165
CREATE TABLE t2 (a int not null, key (A));
166
INSERT INTO t2 VALUES (1),(2);
167
CREATE TABLE t3 (a int, key(A), b text);
168
INSERT INTO t3 VALUES (1,'1'),(2,'2');
169
SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
172
INSERT INTO t2 values (1),(2),(3);
173
INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
174
explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
175
id select_type table type possible_keys key key_len ref rows Extra
176
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary
177
1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index
178
1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer
179
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
182
create temporary table t4 select * from t3;
183
insert into t3 select * from t4;
184
insert into t4 select * from t3;
185
insert into t3 select * from t4;
186
insert into t4 select * from t3;
187
insert into t3 select * from t4;
188
insert into t4 select * from t3;
189
insert into t3 select * from t4;
190
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
191
id select_type table type possible_keys key key_len ref rows Extra
192
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
193
1 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct
194
select distinct t1.a from t1,t3 where t1.a=t3.a;
198
select distinct 1 from t1,t3 where t1.a=t3.a;
201
explain SELECT distinct t1.a from t1;
202
id select_type table type possible_keys key key_len ref rows Extra
203
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
204
explain SELECT distinct t1.a from t1 order by a desc;
205
id select_type table type possible_keys key key_len ref rows Extra
206
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
207
explain SELECT t1.a from t1 group by a order by a desc;
208
id select_type table type possible_keys key key_len ref rows Extra
209
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
210
explain SELECT distinct t1.a from t1 order by a desc limit 1;
211
id select_type table type possible_keys key key_len ref rows Extra
212
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index
213
explain SELECT distinct a from t3 order by a desc limit 2;
214
id select_type table type possible_keys key key_len ref rows Extra
215
1 SIMPLE t3 index NULL a 5 NULL 40 Using index
216
explain SELECT distinct a,b from t3 order by a+1;
217
id select_type table type possible_keys key key_len ref rows Extra
218
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
219
explain SELECT distinct a,b from t3 order by a limit 2;
220
id select_type table type possible_keys key key_len ref rows Extra
221
1 SIMPLE t3 index NULL a 5 NULL 2 Using temporary
222
explain SELECT a,b from t3 group by a,b order by a+1;
223
id select_type table type possible_keys key key_len ref rows Extra
224
1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort
225
drop table t1,t2,t3,t4;
226
CREATE TABLE t1 (name varchar(255));
227
INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
228
SELECT DISTINCT * FROM t1 LIMIT 2;
232
SELECT DISTINCT name FROM t1 LIMIT 2;
236
SELECT DISTINCT 1 FROM t1 LIMIT 2;
241
ID int NOT NULL auto_increment,
242
NAME varchar(75) DEFAULT '' NOT NULL,
243
LINK_ID int DEFAULT '0' NOT NULL,
246
KEY LINK_ID (LINK_ID)
248
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
250
ID int NOT NULL auto_increment,
251
NAME varchar(150) DEFAULT '' NOT NULL,
256
t2.id AS key_link_id,
259
LEFT JOIN t2 ON t1.link_id=t2.id
267
name tinytext not null,
280
insert into t1 values (1,'yes'), (2,'no');
281
insert into t2 values (1,1);
282
insert into t3 values (1,1);
293
t1 as j_lj_t2 left join t2 as t2_lj
294
on j_lj_t2.id=t2_lj.id
296
t1 as j_lj_t3 left join t3 as t3_lj
297
on j_lj_t3.id=t3_lj.id
299
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
300
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
301
id select_type table type possible_keys key key_len ref rows Extra
302
1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary
303
1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join buffer
304
1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join buffer
305
1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer
306
1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct
307
1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer
308
1 SIMPLE t3_lj ref id id 4 test.j_lj_t3.id 1 Using where; Using index; Distinct
318
t1 as j_lj_t2 left join t2 as t2_lj
319
on j_lj_t2.id=t2_lj.id
321
t1 as j_lj_t3 left join t3 as t3_lj
322
on j_lj_t3.id=t3_lj.id
324
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
325
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
329
create table t1 (a int not null, b int not null, t time);
330
insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15");
331
select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
332
a sec_to_time(sum(time_to_sec(t)))
336
select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
337
a sec_to_time(sum(time_to_sec(t)))
340
create table t2 (a int not null primary key, b int);
341
insert into t2 values (1,1),(2,2),(3,3);
342
select t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
343
a sec_to_time(sum(time_to_sec(t)))
347
select distinct t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
348
a sec_to_time(sum(time_to_sec(t)))
352
create table t1 (a int not null,b char(5), c text);
353
insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
354
select distinct a from t1 group by b,a having a > 2 order by a desc;
358
select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
363
create table t1 (a char(1), key(a)) engine=myisam;
364
insert into t1 values('1'),('1');
365
select * from t1 where a >= '1';
369
select distinct a from t1 order by a desc;
372
select distinct a from t1 where a >= '1' order by a desc;
376
CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME);
377
CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10));
378
INSERT INTO t1 (email, infoID, dateentered) VALUES
379
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
380
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
381
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
382
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
383
('test3@testdomain.com', 1, '2002-05-19 22:17:32');
384
INSERT INTO t2(infoID, shipcode) VALUES
387
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID;
389
test1@testdomain.com Z001
390
test2@testdomain.com Z001
391
test2@testdomain.com R002
392
test3@testdomain.com Z001
393
SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC;
398
SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC;
400
test1@testdomain.com Z001
401
test2@testdomain.com Z001
402
test2@testdomain.com R002
403
test3@testdomain.com Z001
405
CREATE TABLE t1 (privatemessageid int NOT NULL auto_increment, folderid int NOT NULL default '0', userid int NOT NULL default '0', touserid int NOT NULL default '0', fromuserid int NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int NOT NULL default '0', showsignature int NOT NULL default '0', iconid int NOT NULL default '0', messageread int NOT NULL default '0', readtime int NOT NULL default '0', receipt int NOT NULL default '0', deleteprompt int NOT NULL default '0', multiplerecipients int NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) ENGINE=MyISAM;
406
INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0);
407
CREATE TABLE t2 (userid int NOT NULL auto_increment, usergroupid int NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid int NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser int NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail int NOT NULL default '0', showemail int NOT NULL default '0', invisible int NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle int NOT NULL default '0', joindate int NOT NULL default '0', cookieuser int NOT NULL default '0', daysprune int NOT NULL default '0', lastvisit int NOT NULL default '0', lastactivity int NOT NULL default '0', lastpost int NOT NULL default '0', posts int NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification int NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm int NOT NULL default '0', emailonpm int NOT NULL default '0', pmpopup int NOT NULL default '0', avatarid int NOT NULL default '0', avatarrevision int NOT NULL default '0', options int NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts int NOT NULL default '-1', startofweek int NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int NOT NULL default '0', nosessionhash int NOT NULL default '0', autorefresh int NOT NULL default '-1', messagepopup int NOT NULL default '0', inforum int NOT NULL default '0', ratenum int NOT NULL default '0', ratetotal int NOT NULL default '0', allowrate int NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) ENGINE=MyISAM;
408
INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1);
409
SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid);
410
privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate
411
128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 33 6 Kevin 0 kevin@stileproject.com 1 0 http://www.stileproject.com 1 1 0 Administrator 0 996120694 1 -1 1030996168 1031027028 1030599436 36 -6 0 1 0 1 0 0 15 0000-00-00 -1 1 64.0.0.0 0 1 -1 0 0 4 19 1
413
CREATE TABLE t1 (a int primary key, b int, c int);
414
INSERT t1 VALUES (1,2,3);
415
CREATE TABLE t2 (a int primary key, b int, c int);
416
INSERT t2 VALUES (3,4,5);
417
SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c;
421
CREATE table t1 ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;
422
INSERT INTO t1 VALUES (1, 'aaaaa');
423
INSERT INTO t1 VALUES (3, 'aaaaa');
424
INSERT INTO t1 VALUES (2, 'eeeeeee');
425
select distinct left(name,1) as name from t1;
431
ID int NOT NULL auto_increment,
432
NAME varchar(75) DEFAULT '' NOT NULL,
433
LINK_ID int DEFAULT '0' NOT NULL,
436
KEY LINK_ID (LINK_ID)
438
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0);
439
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (2,'Jack',0);
440
INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (3,'Bill',0);
442
ID int NOT NULL auto_increment,
443
NAME varchar(150) DEFAULT '' NOT NULL,
448
t2.id AS key_link_id,
451
LEFT JOIN t2 ON t1.link_id=t2.id
458
html varchar(5) default NULL,
462
INSERT INTO t1 VALUES ('1',1,0);
463
SELECT DISTINCT html,SUM(rout)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin;
467
CREATE TABLE t1 (a int);
468
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
469
SELECT DISTINCT a, 1 FROM t1;
476
SELECT DISTINCT 1, a FROM t1;
483
CREATE TABLE t2 (a int, b int);
484
INSERT INTO t2 VALUES (1,1),(2,2),(2,3),(2,4),(3,5);
485
SELECT DISTINCT a, b, 2 FROM t2;
492
SELECT DISTINCT 2, a, b FROM t2;
499
SELECT DISTINCT a, 2, b FROM t2;
507
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
508
INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
509
EXPLAIN SELECT DISTINCT a FROM t1;
510
id select_type table type possible_keys key key_len ref rows Extra
511
1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index
512
EXPLAIN SELECT DISTINCT a,b FROM t1;
513
id select_type table type possible_keys key key_len ref rows Extra
514
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
515
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
516
id select_type table type possible_keys key key_len ref rows Extra
517
1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary
518
1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct; Using join buffer
519
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
520
WHERE t1_1.a = t1_2.a;
521
id select_type table type possible_keys key key_len ref rows Extra
522
1 SIMPLE t1_1 ALL PRIMARY NULL NULL NULL 3 Using temporary
523
1 SIMPLE t1_2 eq_ref PRIMARY PRIMARY 4 test.t1_1.a 1 Using index; Distinct
524
EXPLAIN SELECT a FROM t1 GROUP BY a;
525
id select_type table type possible_keys key key_len ref rows Extra
526
1 SIMPLE t1 index NULL PRIMARY 4 NULL 3 Using index
527
EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
528
id select_type table type possible_keys key key_len ref rows Extra
529
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
530
EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
531
id select_type table type possible_keys key key_len ref rows Extra
532
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
533
CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT,
535
INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
536
EXPLAIN SELECT DISTINCT a FROM t2;
537
id select_type table type possible_keys key key_len ref rows Extra
538
1 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index
539
EXPLAIN SELECT DISTINCT a,a FROM t2;
540
id select_type table type possible_keys key key_len ref rows Extra
541
1 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index
542
EXPLAIN SELECT DISTINCT b,a FROM t2;
543
id select_type table type possible_keys key key_len ref rows Extra
544
1 SIMPLE t2 index NULL PRIMARY 8 NULL 3 Using index
545
EXPLAIN SELECT DISTINCT a,c FROM t2;
546
id select_type table type possible_keys key key_len ref rows Extra
547
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary
548
EXPLAIN SELECT DISTINCT c,a,b FROM t2;
549
id select_type table type possible_keys key key_len ref rows Extra
550
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
551
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
552
id select_type table type possible_keys key key_len ref rows Extra
553
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
554
CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
555
EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
556
id select_type table type possible_keys key key_len ref rows Extra
557
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using filesort
559
create table t1 (id int, dsc varchar(50));
560
insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");
561
select distinct id, IFNULL(dsc, '-') from t1;
567
CREATE TABLE t1 (a int primary key, b int);
568
INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2);
569
explain SELECT DISTINCT a, b FROM t1 ORDER BY b;
570
id select_type table type possible_keys key key_len ref rows Extra
571
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
572
SELECT DISTINCT a, b FROM t1 ORDER BY b;
579
ID int NOT NULL auto_increment,
580
x varchar(20) default NULL,
581
y decimal(10,0) default NULL,
585
INSERT INTO t1 VALUES
592
select count(distinct x,y) from t1;
595
select count(distinct concat(x,y)) from t1;
596
count(distinct concat(x,y))
599
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
600
INSERT INTO t1 VALUES (1, 101);
601
INSERT INTO t1 SELECT a + 1, a + 101 FROM t1;
602
INSERT INTO t1 SELECT a + 2, a + 102 FROM t1;
603
INSERT INTO t1 SELECT a + 4, a + 104 FROM t1;
604
INSERT INTO t1 SELECT a + 8, a + 108 FROM t1;
605
EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
606
id select_type table type possible_keys key key_len ref rows Extra
607
1 SIMPLE t1 index NULL PRIMARY 8 NULL 16 Using where; Using index
608
SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
611
CREATE TABLE t1 (a INT, UNIQUE (a));
612
INSERT INTO t1 VALUES (4),(null),(2),(1),(null),(3);
613
EXPLAIN SELECT DISTINCT a FROM t1;
614
id select_type table type possible_keys key key_len ref rows Extra
615
1 SIMPLE t1 index NULL a 5 NULL 6 Using index
616
SELECT DISTINCT a FROM t1;
623
EXPLAIN SELECT a FROM t1 GROUP BY a;
624
id select_type table type possible_keys key key_len ref rows Extra
625
1 SIMPLE t1 index NULL a 5 NULL 6 Using index
626
SELECT a FROM t1 GROUP BY a;
634
CREATE TABLE t1 (a INT, b INT);
635
INSERT INTO t1 VALUES(1,1),(1,2),(1,3);
636
SELECT DISTINCT a, b FROM t1;
641
SELECT DISTINCT a, a, b FROM t1;
648
CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
649
PRIMARY KEY(a,b,c,d,e),
652
INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
658
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
659
id select_type table type possible_keys key key_len ref rows Extra
660
1 SIMPLE t1 index NULL a 16 NULL 6 Using index
661
SELECT DISTINCT a, b, d, c FROM t1;
added
removed
tests/r/pbxt/distinct.result
