From: walter harms
Date: August 8 2008 4:43pm
Subject: Re: Picking the better query (join vs subselect)
List-Archive: http://lists.mysql.com/mysql/214016
Message-Id: <489C77A1.1040301@bfs.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit



Waynn Lue wrote:
> Out of curiosity, is it generally faster to do a sub query or do it in
> code for something like this.
> 
> Schema of Settings table, where the PK is (ApplicationId, SettingId):
> ApplicationId, SettingId, SettingValue
> 
> Select SettingValue from Settings where SettingId = 10 and
> ApplicationId IN (select ApplicationId from Settings where SettingId =
> 22 and SettingValue = "1");
> 
> The other solution is to do the two queries separately then do the
> filtering in code.
> 
> What's generally faster?
> 
> Waynn
> 

there is no easy answer.
subqueries with constants are fast in mysql without is is better to use a join.
complex queries involving several tables with perhaps a lot foreign keys tend to be
very slow. using "join" from coreutils can improve things dramaticly.

re,
 wh