At 12:12 PM 28/01/2002 -0600, you 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.
It will return a maximum of the number of rows in products table (surely the database engine will have to work with more than 100 rows). >So is there a solution to this "simple" problem? If you cannot even afford the overhead of determining the distinct prod_id rows the option is to create a new table with one colum for prod_id. When a row is added into detail a row will be inserted into this table if that id does not already exist and whenever a delete takes place you can delete the corresponding row of this table if no more rows exists in the detail table. Otherwise a column can be added to product table where you can keep a 0 for not present in detail or 1 for present in detail updating this column as and when necessary. >TIA > >Brent Regards, Anvar --------------------------------------------------------------------- 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
