|
1
by brian
clean slate |
1 |
drop table if exists t1; |
2 |
create table t1 (id int not null, str char(10), unique(str)); |
|
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 |
|
16 |
1 SIMPLE t1 ref str str 11 const 1 Using index condition |
|
17 |
explain select * from t1 where str="foo"; |
|
18 |
id select_type table type possible_keys key key_len ref rows Extra |
|
19 |
1 SIMPLE t1 const str str 11 const 1 |
|
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 |
|
25 |
1 SIMPLE t1 const str str 11 const 1 |
|
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 |
|
34 |
create table t1 (a int not null); |
|
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; |
|
47 |
set names koi8r; |
|
48 |
create table ÔÁÂ (ËÏÌ0 int, ËÏÌ1 int, key ÉÎÄ0 (ËÏÌ0), key ÉÎÄ01 (ËÏÌ0,ËÏÌ1)); |
|
49 |
insert into ÔÁÂ (ËÏÌ0) values (1); |
|
50 |
insert into ÔÁÂ (ËÏÌ0) values (2); |
|
51 |
explain select ËÏÌ0 from ÔÁÂ where ËÏÌ0=1; |
|
52 |
id select_type table type possible_keys key key_len ref rows Extra |
|
53 |
1 SIMPLE ÔÁÂ ref ÉÎÄ0,ÉÎÄ01 ÉÎÄ0 5 const 1 Using index |
|
54 |
drop table ÔÁÂ; |
|
55 |
set names latin1; |
|
56 |
select 3 into @v1; |
|
57 |
explain select 3 into @v1; |
|
58 |
id select_type table type possible_keys key key_len ref rows Extra |
|
59 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used |
|
60 |
CREATE TABLE t1(c INT); |
|
61 |
INSERT INTO t1 VALUES (),(); |
|
62 |
CREATE TABLE t2 (b INT, |
|
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 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), |
|
70 |
KEY(b),KEY(b),KEY(b),KEY(b),KEY(b)); |
|
71 |
INSERT INTO t2 VALUES (),(),(); |
|
72 |
EXPLAIN SELECT 1 FROM |
|
73 |
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; |
|
74 |
id select_type table type possible_keys key key_len ref rows Extra |
|
75 |
X X X X X X X X X const row not found |
|
76 |
X X X X X X X X X |
|
77 |
X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF) |
|
78 |
DROP TABLE t2; |
|
79 |
DROP TABLE t1; |
|
80 |
End of 5.0 tests. |
|
81 |
explain select 1; |
|
82 |
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr |
|
83 |
def id 8 3 1 N 32929 0 63 |
|
84 |
def select_type 253 19 6 N 1 31 8 |
|
85 |
def table 253 64 0 Y 0 31 8 |
|
86 |
def type 253 10 0 Y 0 31 8 |
|
87 |
def possible_keys 253 4096 0 Y 0 31 8 |
|
88 |
def key 253 64 0 Y 0 31 8 |
|
89 |
def key_len 253 320 0 Y 0 31 8 |
|
90 |
def ref 253 1024 0 Y 0 31 8 |
|
91 |
def rows 8 10 0 Y 32928 0 63 |
|
92 |
def Extra 253 255 14 N 1 31 8 |
|
93 |
id select_type table type possible_keys key key_len ref rows Extra |
|
94 |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used |
|
95 |
End of 5.2 tests. |