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.
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
