Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: subselect speed

  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    subselect speed

    At the moment I select from one table where an ID column is part of another tables contents.

    I was doing it as 2 separate queries ie: doing the sub select first then implode() into a comma separated string then doing the 2nd select with a loop.

    Then as part of my query tidying up I changed to as below.

    Code:
    SELECT ID,GGInternalID FROM `tblsites` WHERE ID IN(SELECT SiteID FROM `tblkeyclientstosites` WHERE KeyClientID=".$sessionArr[3].") AND DatePrepared BETWEEN '$startdate' AND '$enddate'
    My question is is there a faster / better way of doing things than a sub select like this.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    something like that
    Code:
    SELECT A.ID,A.GGInternalID 
    FROM tblsites A join  tblkeyclientstosites B on A.ID =  B.SiteID  
    WHERE B.KeyClientID=".$sessionArr[3].") AND B.DatePrepared BETWEEN '$startdate' AND '$enddate'
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    that would only work if it was on a 1:1 relationship would it not same as other joins?
    where as this is a many to one select within limitations of a many input.

    what about speed issues? would joins be faster / lower overhead than sub selects?
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    JOINs are typically faster than sub-SELECTs, though it depends on how good the database query optimizer is. SQL Server will often convert one to the other if it can determine which would be faster. In general, MySQL isn't that sophisticated.

    And a JOIN is used for 1 to 1 *or* many to 1 *or* many to many. I don't see why you think Bubikol's is only good for 1 to 1. So far as I can see, his query is a direct translation of yours from sub-select to join.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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