Thread: Large OR query
Hi all If I have say 1000 values for an ID field, what is the best way to select from a table all the corresponding records? I have tried select * from blah where id in (id1,id2,id3...id1000) and select * from blah where id=id1 or id=id2 ... or id=id1000 and both are pretty slow. Is there a better way to do this please? Thanks Ciao -- Zak McGregor http://www.carfolio.com - Over 7000 car specs online Web mercenary - currently for hire. Perl/html/.js/sql/cgi/GNUlinux/php + --------------------------------------------------------------------- "Trying to make bits uncopyable is like trying to make water not wet. The sooner people accept this, and build business models that take this into account, the sooner people will start making money again." -- Bruce Schneier
I would load the 1000 values into a temporary table, and join on the ID field. I usually find that approach gives much faster results. > -----Original Message----- > From: Zak McGregor [SMTP:zak@mighty.co.za] > Sent: Tuesday, June 12, 2001 7:42 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Large OR query > > Hi all > > If I have say 1000 values for an ID field, what is the best way to > select from a table all the corresponding records? > I have tried > select * from blah where id in (id1,id2,id3...id1000) > and > select * from blah where id=id1 or id=id2 ... or id=id1000 > > and both are pretty slow. > Is there a better way to do this please? > > Thanks > > Ciao > -- > Zak McGregor http://www.carfolio.com - Over 7000 car specs online > Web mercenary - currently for hire. Perl/html/.js/sql/cgi/GNUlinux/php + > --------------------------------------------------------------------- > "Trying to make bits uncopyable is like trying to make water not wet. > The sooner people accept this, and build business models that take > this into account, the sooner people will start making money again." > -- Bruce Schneier > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
Is populating another table with the 1000 values and doing a SELECT * FROM <whatever> WHERE <something> IN (SELECT <something> FROM <whatever>) ? I'm not entirely sure that it will yield a better result but it's worth a try. -Mitch ----- Original Message ----- From: "Zak McGregor" <zak@mighty.co.za> To: <pgsql-general@postgresql.org> Sent: Tuesday, June 12, 2001 8:42 AM Subject: [GENERAL] Large OR query > Hi all > > If I have say 1000 values for an ID field, what is the best way to > select from a table all the corresponding records? > I have tried > select * from blah where id in (id1,id2,id3...id1000) > and > select * from blah where id=id1 or id=id2 ... or id=id1000 > > and both are pretty slow. > Is there a better way to do this please? > > Thanks > > Ciao > -- > Zak McGregor http://www.carfolio.com - Over 7000 car specs online > Web mercenary - currently for hire. Perl/html/.js/sql/cgi/GNUlinux/php + > --------------------------------------------------------------------- > "Trying to make bits uncopyable is like trying to make water not wet. > The sooner people accept this, and build business models that take > this into account, the sooner people will start making money again." > -- Bruce Schneier > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
isn't that sort of IN statement not very efficient in postgresql? normally instead of that sort of IN statement i do: SELECT * FROM maintable WHERE exists (SELECT id FROM temptable WHERE temptable.id = maintable.id); but anyway, its not actually needed is it? can't you just do: SELECT maintable.* FROM maintable, temptable WHERE maintable.id = temptable.id; tamsin > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mitch Vincent > Sent: 12 June 2001 15:25 > To: Zak McGregor; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Large OR query > > > Is populating another table with the 1000 values and doing a > SELECT * FROM <whatever> WHERE <something> IN (SELECT <something> FROM > <whatever>) ? > > I'm not entirely sure that it will yield a better result but it's worth a > try. > > -Mitch > > ----- Original Message ----- > From: "Zak McGregor" <zak@mighty.co.za> > To: <pgsql-general@postgresql.org> > Sent: Tuesday, June 12, 2001 8:42 AM > Subject: [GENERAL] Large OR query > > > > Hi all > > > > If I have say 1000 values for an ID field, what is the best way to > > select from a table all the corresponding records? > > I have tried > > select * from blah where id in (id1,id2,id3...id1000) > > and > > select * from blah where id=id1 or id=id2 ... or id=id1000 > > > > and both are pretty slow. > > Is there a better way to do this please? > > > > Thanks > > > > Ciao > > -- > > Zak McGregor http://www.carfolio.com - Over 7000 car specs online > > Web mercenary - currently for hire. Perl/html/.js/sql/cgi/GNUlinux/php + > > --------------------------------------------------------------------- > > "Trying to make bits uncopyable is like trying to make water not wet. > > The sooner people accept this, and build business models that take > > this into account, the sooner people will start making money again." > > -- Bruce Schneier > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
On Tue, 12 Jun 2001, Mitch Vincent wrote: > Is populating another table with the 1000 values and doing a > SELECT * FROM <whatever> WHERE <something> IN (SELECT <something> FROM > <whatever>) ? > > I'm not entirely sure that it will yield a better result but it's worth a > try. > SELECT * FROM <whatever>,<temptable> WHERE whatever.id=temp.id; is probably what you are after. - Einar Karttunen