|
1
by brian
clean slate |
1 |
create table t4 (
|
2 |
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
|
|
3 |
) engine=innodb;
|
|
4 |
insert into t4 (a1, a2, b, c, d) values
|
|
5 |
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
|
|
6 |
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
|
|
7 |
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
|
|
8 |
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
|
|
9 |
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
|
|
10 |
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
|
|
11 |
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
|
|
12 |
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
|
|
13 |
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
|
|
14 |
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
|
|
15 |
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
|
|
16 |
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
|
|
17 |
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
|
|
18 |
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
|
|
19 |
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
|
|
20 |
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
|
|
21 |
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
|
|
22 |
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
|
|
23 |
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
|
|
24 |
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
|
|
25 |
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
|
|
26 |
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
|
|
27 |
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
|
|
28 |
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
|
|
29 |
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
|
|
30 |
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
|
|
31 |
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
|
|
32 |
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
|
|
33 |
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
|
|
34 |
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
|
|
35 |
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
|
|
36 |
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
|
|
37 |
create index idx12672_0 on t4 (a1);
|
|
38 |
create index idx12672_1 on t4 (a1,a2,b,c);
|
|
39 |
create index idx12672_2 on t4 (a1,a2,b);
|
|
40 |
analyze table t4;
|
|
41 |
Table Op Msg_type Msg_text
|
|
42 |
test.t4 analyze status OK
|
|
43 |
select distinct a1 from t4 where pk_col not in (1,2,3,4);
|
|
44 |
a1
|
|
45 |
a
|
|
46 |
b
|
|
47 |
c
|
|
48 |
d
|
|
49 |
drop table t4;
|
|
50 |
create table t1 (
|
|
51 |
a varchar(30), b varchar(30), primary key(a), key(b)
|
|
52 |
) engine=innodb;
|
|
53 |
select distinct a from t1;
|
|
54 |
a
|
|
55 |
drop table t1;
|
|
56 |
create table t1(a int, key(a)) engine=innodb;
|
|
57 |
insert into t1 values(1);
|
|
58 |
select a, count(a) from t1 group by a with rollup;
|
|
59 |
a count(a)
|
|
60 |
1 1
|
|
61 |
NULL 1
|
|
62 |
drop table t1;
|
|
63 |
create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb;
|
|
64 |
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
|
|
65 |
alter table t1 drop primary key, add primary key (f2, f1);
|
|
66 |
explain select distinct f1 a, f1 b from t1;
|
|
67 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
|
383.1.16
by Brian Aker
Force client communication into UTF8 |
68 |
1 SIMPLE t1 index NULL PRIMARY 10 NULL 4 Using index; Using temporary
|
|
1
by brian
clean slate |
69 |
explain select distinct f1, f2 from t1;
|
70 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
|
383.1.16
by Brian Aker
Force client communication into UTF8 |
71 |
1 SIMPLE t1 range NULL PRIMARY 10 NULL 3 Using index for group-by; Using temporary
|
|
1
by brian
clean slate |
72 |
drop table t1;
|