List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 28 2002 6:27pm
Subject:Re: How to emulate subselect on larger table? ###
View as plain text  
At 12:12 -0600 1/28/02, BD wrote:
>I have 2 tables, detail and product. The detail table could have 
>millions of rows. The product table could have a few hundred. I need 
>to know which products are in the detail table. (It would also be 
>nice to know which products aren't in the detail table). The problem 
>is it has to be fast, very fast.  Since it is on a web server it 
>can't tie up the CPU for several seconds while it needlessly returns 
>thousands of records.  I only need to know which fields from one 
>table *exist* in the other table. Sounds simple right?
>
>Ideally it would look like:
>     select product_id from products where prod_id in (select prod_id 
>from detail where <somedetailwhere>);
>
>The <somedetailwhere> is an optional where clause that could be 
>applied to the detail table. It will use indexes so it will be quite 
>fast.
>
>Now if I try a simple join like:
>     select prod_id from products, detail where <somedetailwhere> and 
>product.prod_id = detail.prod_id;
>it will of course return duplicate prod_id's because the product 
>could appear in tens of thousands of  detail items. I don't need to 
>return thousands of rows. I only need to return 1 row of each 
>prod_id if that prod_id appears in the detail table.
>
>I can't use:
>     select distinct prod_id from products, detail where 
><somedetailwhere> and product.prod_id = detail.prod_id;
>because it takes too long. It will still returns hundreds of 
>thousands of rows unnecessarily.

That's impossible.  You said your product table contains only a few 
hundred rows.
This query can't return more rows than are in the product table.


>
>So is there a solution to this "simple" problem?
>
>TIA
>
>Brent

Thread
How to emulate subselect on larger table? ###BD28 Jan
  • Re: How to emulate subselect on larger table? ###Paul DuBois28 Jan
  • Re: How to emulate subselect on larger table? ###Anvar Hussain K.M.29 Jan
RE: How to emulate subselect on larger table? ###Rick Emery28 Jan
RE: How to emulate subselect on larger table? ###dougforrest@earthlink.net28 Jan