use mark drop table if exists test1; create table test1 ( i int, PRIMARY KEY (i)); insert into test1 values (10),(20),(30),(40),(50), (60); drop table if exists test2; create table test2 ( i int, PRIMARY KEY (i)); insert into test2 values (10),(20); select test1.i, test2.i from test1 join test2 on (test1.i = test2.i); explain select test1.i, test2.i from test1 join test2 on (test1.i = test2.i)\G explain format=tree select test1.i, test2.i from test1 join test2 on (test1.i = test2.i); explain analyze select test1.i, test2.i from test1 join test2 on (test1.i = test2.i);
mysql> explain select test1.i, test2.i from test1 join test2 on (test1.i = test2.i)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test2
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mark.test2.i
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
Even though the table test1 is specified first, the optimizer chooses test2 (probably because it has fewer rows).
For row in the first part, 2, is multiplied with every row in the 2nd part, 1. 2 * 1 = 2 rows. We can also see with indexes are
used, it uses eq_ref for the join.
mysql> explain format=tree select test1.i, test2.i from test1 join test2 on (test1.i = test2.i);
+-----------------------------------------------------------------
| EXPLAIN
+-----------------------------------------------------------------
| -> Nested loop inner join (cost=1.15 rows=2)
-> Index scan on test2 using PRIMARY (cost=0.45 rows=2)
-> Single-row covering index lookup on test1 using PRIMARY (i=test2.i) (cost=0.3 rows=1)
|
+-----------------------------------------------------------------
1 row in set (0.00 sec)
We see it is a nested loop. Same thing is described, test2 is looked first and then joined to tests. 2 rows of test2 is matched
with test1 of 1 row. 2 rows * 1 row is 2 rows total.
mysql> explain analyze select test1.i, test2.i from test1 join test2 on (test1.i = test2.i);
+-----------------------------------------------------------------
| EXPLAIN
+-----------------------------------------------------------------
| -> Nested loop inner join (cost=1.15 rows=2) (actual time=0.0247..0.0329 rows=2 loops=1)
-> Covering index scan on test2 using PRIMARY (cost=0.45 rows=2) (actual time=0.0147..0.0183 rows=2 loops=1)
-> Single-row covering index lookup on test1 using PRIMARY (i=test2.i) (cost=0.3 rows=1) (actual time=0.00577..0.00588 rows=1 loops=2)
|
+-----------------------------------------------------------------
Same as tree, but with the time costs at each step.
insert into test2 values (30), (40);
mysql> explain analyze select test1.i, test2.i from test1 join test2 on (test1.i = test2.i) order by test1.i desc;
+-----------------------------------------------------------------
| EXPLAIN
+-----------------------------------------------------------------
| -> Sort: test1.i DESC (actual time=0.0849..0.0854 rows=4 loops=1)
-> Stream results (cost=2.05 rows=4) (actual time=0.0496..0.0688 rows=4 loops=1)
-> Nested loop inner join (cost=2.05 rows=4) (actual time=0.0479..0.0656 rows=4 loops=1)
-> Covering index scan on test2 using PRIMARY (cost=0.65 rows=4) (actual time=0.0316..0.0382 rows=4 loops=1)
-> Single-row covering index lookup on test1 using PRIMARY (i=test2.i) (cost=0.275 rows=1) (actual time=0.00591..0.00599 rows=1 loops=4)
|
+-----------------------------------------------------------------
We see here in the nested loop there are 4 rows times 1 row. Then the sort the results, 4 rows.