|
223
by Brian Aker
Cleanup int() work. |
1 |
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, name varchar(20), |
|
1
by brian
clean slate |
2 |
INDEX (name)) ENGINE=InnoDB; |
|
223
by Brian Aker
Cleanup int() work. |
3 |
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, fkey int, |
|
1
by brian
clean slate |
4 |
FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; |
5 |
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); |
|
6 |
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); |
|
7 |
EXPLAIN
|
|
8 |
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id |
|
9 |
WHERE t1.name LIKE 'A%'; |
|
10 |
id select_type table type possible_keys key key_len ref rows Extra |
|
11 |
1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where |
|
12 |
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index |
|
13 |
EXPLAIN
|
|
14 |
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id |
|
15 |
WHERE t1.name LIKE 'A%' OR FALSE; |
|
16 |
id select_type table type possible_keys key key_len ref rows Extra |
|
17 |
1 SIMPLE t2 index NULL PRIMARY 4 NULL 5 |
|
18 |
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where |
|
19 |
DROP TABLE t1,t2; |