If the join_condition is specified via USING (column_list), the query plan depends on the sequence of tables in the query

CREATE TABLE t1 (id  int(11) NOT NULL auto_increment, f1 INT NOT NULL, PRIMARY KEY  (id));

INSERT INTO t1 (id, f1) VALUES (1,9),(2,0), (3,7);

CREATE TABLE t2 (id  int(11), f2 INT NOT NULL);

INSERT INTO t2 (id, f2) VALUES

(4,5),(3,3),(1,0),(1,3),(6,1),(2,0),(4,1),(2,7),(2,1),(1,0),(3,0),(5,8),(5,4),(3,9),(2,0),(7,2),(2,0),(1,8),(6,5),(4,1);

ANALYZE TABLE t1, t2;

EXPLAIN EXTENDED

  SELECT  count(*) FROM t2 JOIN t1 USING (id);

EXPLAIN EXTENDED

  SELECT  count(*) FROM t1 JOIN t2 USING (id);

EXPLAIN EXTENDED

  SELECT  count(*) FROM t2 JOIN t1 ON t1.id=t2.id;

EXPLAIN EXTENDED

  SELECT  count(*) FROM t1 JOIN t2 ON t1.id=t2.id;

drop tables t1, t2;

EXPLAIN EXTENDED

SELECT  count(*) FROM t2 JOIN t1 USING (id);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	100.00	

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select count(0) AS `count(*)` from `test`.`t2` join `test`.`t1` where `test`.`t2`.`id` = `test`.`t1`.`id`

EXPLAIN EXTENDED

SELECT  count(*) FROM t1 JOIN t2 USING (id);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`id` = `test`.`t1`.`id`

EXPLAIN EXTENDED

SELECT  count(*) FROM t2 JOIN t1 ON t1.id=t2.id;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select count(0) AS `count(*)` from `test`.`t2` join `test`.`t1` where `test`.`t2`.`id` = `test`.`t1`.`id`

EXPLAIN EXTENDED

SELECT  count(*) FROM t1 JOIN t2 ON t1.id=t2.id;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where; Using join buffer (flat, BNL join)

Warnings:

Note	1003	select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`id` = `test`.`t1`.`id`