Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
Thread: subselect speed
10-04-2011, 04:37 PM #1
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.
SELECT ID,GGInternalID FROM `tblsites` WHERE ID IN(SELECT SiteID FROM `tblkeyclientstosites` WHERE KeyClientID=".$sessionArr.") AND DatePrepared BETWEEN '$startdate' AND '$enddate'
10-04-2011, 06:01 PM #2
- Join Date
- Dec 2005
- Thanked 76 Times in 76 Posts
something like that
SELECT A.ID,A.GGInternalID FROM tblsites A join tblkeyclientstosites B on A.ID = B.SiteID WHERE B.KeyClientID=".$sessionArr.") AND B.DatePrepared BETWEEN '$startdate' AND '$enddate'
10-05-2011, 02:03 PM #3
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?
10-05-2011, 07:13 PM #4
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.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.