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.
Show quoted text
-----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 queryHi 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=id1000and 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?
Import Notes
Resolved by subject fallback
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
Show quoted text
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=id1000and 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?
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
Show quoted text
-----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 queryIs 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 queryHi 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=id1000and 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?---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
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