Thread: quickest query to check a table for existance of a value in a field
Hi I have a reasonable sized table (~2m rows), with an indexed field for which there are ~200 possible values. 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. Here's a quick example: CREATE TABLE mytable(c1 INT4 PRIMARY KEY, c2 INT2); CREATE INDEX myindex ON mytable(c2); SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234; The time it takes to execute the above query appears to be of the order O(n) - is there an O(1) query to do it - given that I only want to know if num>0 or if num=0? (the function COUNT() doesn't have to be used). Any help appreciated, Thanks -- Shane
> 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/
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