View Full Version : subselect speed

10-04-2011, 05:37 PM
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[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.

10-04-2011, 07:01 PM
something like that

FROM tblsites A join tblkeyclientstosites B on A.ID = B.SiteID
WHERE B.KeyClientID=".$sessionArr[3].") AND B.DatePrepared BETWEEN '$startdate' AND '$enddate'

10-05-2011, 03:03 PM
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?

Old Pedant
10-05-2011, 08:13 PM
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.