Received: from smtp-relay1.noc.dsvr.net (virus-1.in.noc.dsvr.net [212.69.192.254]) by postgresql.org (Postfix) with ESMTP id 1DD58475864 for ; Tue, 26 Mar 2002 18:55:52 -0500 (EST) Received: from [212.69.206.2] (helo=fullerruss.dsvr.co.uk) by smtp-relay1.noc.dsvr.net with esmtp (Exim 3.34 #1) id 16q0n9-0002eL-00 for pgsql-sql@postgresql.org; Tue, 26 Mar 2002 23:55:51 +0000 Received: from there (fuller-230.adsl.newnet.co.uk [213.131.184.230]) by fullerruss.dsvr.co.uk (8.11.3/8.11.1) with SMTP id g2QNtpH17023 for ; Tue, 26 Mar 2002 23:55:51 GMT Message-Id: <200203262355.g2QNtpH17023@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: pgsql-sql@postgresql.org Subject: Re: quickest query to check a table for existance of a value in a field Date: Tue, 26 Mar 2002 23:55:50 +0000 X-Mailer: KMail [version 1.3.2] References: <20020325214112.27370.qmail@web12705.mail.yahoo.com> In-Reply-To: <20020325214112.27370.qmail@web12705.mail.yahoo.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/350 X-Sequence-Number: 7224 Hi Thanks, just tried this on my dev box (650,000 rows, ~50,000 per ID), the original way (COUNT()) takes ~1/2 second (guestimating), while your way is as instant as I can tel.. Beauty :) -- Shane On Monday 25 Mar 2002 9:41 pm, chester c young wrote: > > I want a quick way to find out if there are any rows matching a > > specified value for this indexed field. I dont care how many there > > are or what their properties are, just if there are any or not. > > > > SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234; > > > > Thanks > > Shane > > Try: select 1 as yes from mytable where c2=1234 limit 1; > or: select 1 as yes where exists( select 1 from mytable where c2=1234 > ); > > would be interesting to see differences in performance on your data > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Movies - coverage of the 74th Academy Awards® > http://movies.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org