Optimizing query for Link two tables on one row

Question

What should be the faster way to join two tables on a single row, the subselect or the join?

For example:

(1) Subselect

select * from main_territorypricing where item_id = 
 (select id from main_iteminstance where platform_id = '8d6kgwzl6dcm')

# explain
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY main_territorypricing   ref item_id item_id 5   const   1   Using where
2   SUBQUERY    main_iteminstance   ref platform_id_index   platform_id_index   182 const   1   Using where; Using index

(2) Join

select p.* from main_territorypricing p 
inner join main_iteminstance i on p.item_id=i.id 
where i.platform_id='8d6kgwzl6dcm'

# explain
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  i   ref PRIMARY,platform_id_index   platform_id_index   182     const   1   Using where; Using index
1   SIMPLE  p   ref item_id item_id 5   avails.i.id 1   NULL

Why would one be preferable over the other? Note all joined fields are indexed.

Possible duplicate of [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query) — drneel, Feb 12 '16 at 20:13
@drneel I think that's a great reference for the general question, I think the above question might be useful for the super-specific case of a single-row joined lookup. — David542, Feb 12 '16 at 20:16

score 1 · Accepted Answer · answered Feb 12 '16 at 20:17

The two are different.

The first returns exactly one row in main_territorypricing when there is a match. The second opens the possibility of there being multiple matching rows.

Similarly, the first will fail if main_iteminstance has more than one row (because of the =).

And, the second has more opportunities for optimization. The first is basically scanning the first table and looking up the values in the second.

None of these differences are "better" or "worse". They are simply different, depending on your needs.

Optimizing query for Link two tables on one row

1 Answers1