Are you sure you're ok publishing that detail? That user has delete/drop/alter privs etc, albeit limited to that one db.
Well it's up to you, but you're far braver than me! :)
Anyway, one reason that the query isn't running quickly is that vendor_id=1002 matches half the rows in the vendor_products table:
mysql> select count(*) from vendor_products where vendor_id=1002;
+----------+
| count(*) |
+----------+
| 419944 |
+----------+
1 row in set (0.86 sec)
So for each of these 420K rows MySQL needs to do a lookup against the products. 420K index lookups is gonna take time.
Adding an index on (vendor_id, mfg_no) reduces the need to hit the datafile(MYD) for vendor_products, but it only takes the query time down to 15 secs from about 17.
It's convoluted, but if you want you can:
CREATE TEMPORARY TABLE t (mfg_no char(25), UNIQUE(mfg_no)) TYPE=HEAP;
INSERT INTO t SELECT p.mfg_no FROM products p;
SELECT count(*) as rejected_products FROM `vendor_products` as v left outer join t as p on (v.mfg_no = p.mfg_no) where p.mfg_no is null and v.vendor_id=1002;
DROP TABLE t;
It'll take about 4-5 secs in total, 2 sec for the query itself. I think it's quicker because the join happens with a HEAP table which is both in memory and uses HASH indexes rather than BTREE. (AFAIK)
You'll need to make your max HEAP table size larger first though: eg:
SET max_heap_table_size = 32000000;
Do you need to do this query often? Does the data need to be very up to date? Or can you just cache the value each hour in your app somewhere? If so I'd just do that :)
Not super useful I know, maybe someone else will be able to suggest a neater approach.
HTH,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail