SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid 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

  2. #2
    SitePoint Addict
    Join Date
    Apr 2011
    Posts
    265
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    My opinion is that the variant with INNER JOIN has better performance.
    The second variant has 2 independent subquery.
    Free: Web Programming Courses HTML, CSS, Flash
    Web Programming: AJAX Course and PHP-MySQL Course video Lessons
    Good JavaScript and jQuery course for beginners

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,262
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,060
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    First option.

    I may be reading this incorrectly, but does p.property_master_project have an index? If not, it should.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •