SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Threaded View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sub query or inner join

    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:
    Code:
    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:
    Code:
    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?
    Last edited by ScallioXTX; Dec 21, 2012 at 05:23. Reason: Wrapped explain output in [code][/code] for readability


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •