Mysql query help...

[ajlisowski]

Hey all, I am having issues with a rather simple query. Basically I want to select all vendors that have a product.

 

I have a vendor table, a product table and a vendor to product table (since products can be part of multiple vendors depending on region, and obviously vendors can have multiple products)

 

It takes about 6 seconds, which is way too long. I have a few hundred vendors and like 25 thousannd vendor-to-product entries.

 

This is the query

 

SELECT `vendor_number` AS `value`,CONCAT_WS(' - ', `manufacturer`, `division_dist`) AS `label` FROM `lunapr_c1vendor` WHERE `vendor_number` IN (SELECT `vendor_number` FROM `lunapr_vendor_to_product`) ORDER BY `manufacturer`

 

This is the result from explain.

 

 

1 PRIMARY lunapr_c1vendor ALL  NULL  NULL  NULL  NULL 1258 Using where; Using filesort

2 DEPENDENT SUBQUERY lunapr_vendor_to_product index vendor_to_product,vendor_number,vendor_to_product_... vendor_number 7 NULL 23360 Using where; Using index

 

 

Any idea what might be going wrong here?

[ajlisowski]

I decided to grab the subquery results first, put then in a string of (1,2,3,4,5) and just plug that variable in for the subquery so mysql didnt have to run that query on every vendor. Made things go a lot quicker. That being said, is there a way to do it all at once and not have it run like poop?

[fenway]

Why not just a simple JOIN?

[ajlisowski]

I didnt think to use a join simply because I wasnt actually getting any data from vendor_to_product, I just wanted to make sure it existed.

 

SELECT DISTINCT vtp.`vendor_number`,v.`vendor_number` AS `value`,CONCAT_WS(' - ', v.`manufacturer`, v.`division_dist`) AS `label` FROM `lunapr_c1vendor` AS `v`, `lunapr_vendor_to_product` AS `vtp`  WHERE vtp.`vendor_number`=v.`vendor_number` ORDER BY v.`manufacturer`

 

That query does the job well. Thanks for the advice!