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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
