Sub query or inner join

Databases
1

I would like to get expert recommendation either inner join query is better or sub query like i posted below with explain results:

explain SELECT p.property_master_project, pd.project AS Myproject FROM property p INNER JOIN master_project pd ON ( p.property_master_project = pd.id )
GROUP BY p.property_master_project

Explain result:


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	p 	index 	NULL	cmbd 	944 	NULL	1323 	Using index; Using temporary; Using filesort
1 	SIMPLE 	pd 	eq_ref 	PRIMARY 	PRIMARY 	4 	espace_crem_software.p.property_master_project 	1

OR

explain SELECT property_master_project, (SELECT project FROM master_project WHERE id = property_master_project) AS master_project FROM property
GROUP BY property_master_project

Explain result:


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	property 	index 	NULL	cmbd 	944 	NULL	1323 	Using index; Using temporary; Using filesort
2 	DEPENDENT SUBQUERY 	master_project 	eq_ref 	PRIMARY 	PRIMARY 	4 	func 	1

Which one is having better performance to use?

2

Hi,
My opinion is that the variant with INNER JOIN has better performance.
The second variant has 2 independent subquery.

3

I can’t think of a time when option one wouldn’t be the most optimal solution - the first thing it does is filter out the row to only the matching. Option two does both the full selects then tries to match up, but it’s still dealing with both full datasets.

4

First option.

I may be reading this incorrectly, but does p.property_master_project have an index? If not, it should.