1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
show create table seq_1_to_15_step_2;
Table Create Table
seq_1_to_15_step_2 CREATE TABLE `seq_1_to_15_step_2` (
`seq` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`seq`)
) ENGINE=SEQUENCE DEFAULT CHARSET=latin1
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
count(seq) sum(seq) 1
8 64 1
#
# The engine should be able to optimize the following requests
#
select count(*) from seq_1_to_15_step_2;
count(*)
8
explain select count(*) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) from seq_1_to_15_step_2;
count(seq)
8
explain select count(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select sum(seq) from seq_1_to_15_step_2;
sum(seq)
64
explain select sum(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq),sum(seq) from seq_1_to_15_step_2;
count(seq) sum(seq)
8 64
explain select count(seq),sum(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) as c from seq_1_to_15_step_2 having c > 5;
c
8
explain select count(seq) as c from seq_1_to_15_step_2 having c > 5;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
c
explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select distinct count(*) from seq_1_to_15_step_2;
count(*)
8
explain select distinct count(*) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
seq count(*)
1 8
3 8
5 8
7 8
9 8
11 8
13 8
15 8
explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY seq_1_to_15_step_2 index NULL PRIMARY 8 NULL # Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
2 DERIVED NULL NULL NULL NULL NULL NULL # Storage engine handles GROUP BY
#
# The engine can't optimize the following queries
#
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
count(seq) sum(seq) 1
8 64 1
explain select count(seq),sum(seq),1 from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
1 SIMPLE t2 index NULL PRIMARY 8 NULL 8 Using index; Using join buffer (flat, BNL join)
explain select count(*) from seq_1_to_15_step_2 where seq > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index PRIMARY PRIMARY 8 NULL 8 Using where; Using index
explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index; Using temporary; Using filesort
drop table seq_1_to_15_step_2;
|
