|
1
by brian
clean slate |
1 |
drop table if exists t1; |
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
2 |
create TEMPORARY table t1 (id int not null, str char(10), unique(str)) ENGINE=MYISAM; |
|
1
by brian
clean slate |
3 |
explain select * from t1; |
4 |
id select_type table type possible_keys key key_len ref rows Extra |
|
5 |
1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found |
|
6 |
insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar"); |
|
7 |
select * from t1 where str is null; |
|
8 |
id str |
|
9 |
1 NULL |
|
10 |
2 NULL |
|
11 |
select * from t1 where str="foo"; |
|
12 |
id str |
|
13 |
3 foo |
|
14 |
explain select * from t1 where str is null; |
|
15 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
712
by Brian Aker
Added back explain test. |
16 |
1 SIMPLE t1 ref str str 43 const 1 Using where |
|
1
by brian
clean slate |
17 |
explain select * from t1 where str="foo"; |
18 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
712
by Brian Aker
Added back explain test. |
19 |
1 SIMPLE t1 const str str 43 const 1 |
|
1
by brian
clean slate |
20 |
explain select * from t1 ignore key (str) where str="foo"; |
21 |
id select_type table type possible_keys key key_len ref rows Extra |
|
22 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where |
|
23 |
explain select * from t1 use key (str,str) where str="foo"; |
|
24 |
id select_type table type possible_keys key key_len ref rows Extra |
|
|
712
by Brian Aker
Added back explain test. |
25 |
1 SIMPLE t1 const str str 43 const 1 |
|
1
by brian
clean slate |
26 |
explain select * from t1 use key (str,str,foo) where str="foo"; |
27 |
ERROR 42000: Key 'foo' doesn't exist in table 't1' |
|
28 |
explain select * from t1 ignore key (str,str,foo) where str="foo"; |
|
29 |
ERROR 42000: Key 'foo' doesn't exist in table 't1' |
|
30 |
drop table t1; |
|
31 |
explain select 1; |
|
32 |
id select_type table type possible_keys key key_len ref rows Extra |
|
33 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used |
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
34 |
create TEMPORARY table t1 (a int not null) ENGINE=myisam; |
|
1
by brian
clean slate |
35 |
explain select count(*) from t1; |
36 |
id select_type table type possible_keys key key_len ref rows Extra |
|
37 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away |
|
38 |
insert into t1 values(1); |
|
39 |
explain select count(*) from t1; |
|
40 |
id select_type table type possible_keys key key_len ref rows Extra |
|
41 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away |
|
42 |
insert into t1 values(1); |
|
43 |
explain select count(*) from t1; |
|
44 |
id select_type table type possible_keys key key_len ref rows Extra |
|
45 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away |
|
46 |
drop table t1; |
|
|
1063.9.3
by Brian Aker
Partial fix for tests for tmp |
47 |
create TEMPORARY table ☃ (☢ int, ☣ int, key ☢ (☢), key ☣ (☢,☣)) ENGINE=MYISAM; |
|
712
by Brian Aker
Added back explain test. |
48 |
insert into ☃ (☢) values (1); |
49 |
insert into ☃ (☢) values (2); |
|
50 |
explain select ☢ from ☃ where ☢=1; |
|
|
1
by brian
clean slate |
51 |
id select_type table type possible_keys key key_len ref rows Extra |
|
1685.7.6
by Patrick Crews
Updated test results with changes due to optimizer bug fix. EXPLAIN output now includes 'Using where' for several queries that didn't previously have this output |
52 |
1 SIMPLE ☃ ref ☢,☣ ☢ 5 const 1 Using where; Using index |
|
712
by Brian Aker
Added back explain test. |
53 |
drop table ☃; |
|
1
by brian
clean slate |
54 |
select 3 into @v1; |
55 |
explain select 3 into @v1; |
|
56 |
id select_type table type possible_keys key key_len ref rows Extra |
|
57 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used |
|
58 |
CREATE TABLE t1(c INT); |
|
59 |
INSERT INTO t1 VALUES (),(); |
|
60 |
CREATE TABLE t2 (b INT, |
|
61 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
62 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
63 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
64 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
65 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
66 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
67 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
68 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b)); |
|
69 |
INSERT INTO t2 VALUES (),(),(); |
|
70 |
EXPLAIN SELECT 1 FROM |
|
71 |
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; |
|
72 |
id select_type table type possible_keys key key_len ref rows Extra |
|
73 |
X X X X X X X X X const row not found |
|
74 |
X X X X X X X X X |
|
75 |
X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF) |
|
76 |
DROP TABLE t2; |
|
77 |
DROP TABLE t1; |
|
78 |
End of 5.0 tests. |
|
79 |
explain select 1; |
|
80 |
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr |
|
|
971.7.6
by Eric Day
Merged Stewart's tinyint removal, and fixed a few things to not break protocol. |
81 |
def id 5 3 1 N 32897 0 63 |
82 |
def select_type 8 76 6 N 1 31 45 |
|
83 |
def table 8 256 0 Y 0 31 45 |
|
84 |
def type 8 40 0 Y 0 31 45 |
|
85 |
def possible_keys 8 16384 0 Y 0 31 45 |
|
86 |
def key 8 256 0 Y 0 31 45 |
|
87 |
def key_len 8 1280 0 Y 0 31 45 |
|
88 |
def ref 8 4096 0 Y 0 31 45 |
|
89 |
def rows 5 10 0 Y 32896 0 63 |
|
90 |
def Extra 8 1020 14 N 1 31 45 |
|
|
1
by brian
clean slate |
91 |
id select_type table type possible_keys key key_len ref rows Extra |
92 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used |
|
93 |
End of 5.2 tests. |