~drizzle-trunk/drizzle/development

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