How to improve this query

[needs_upgrade]

hello guys, i wanted to improve my query. it works but it's slow. how can i improve it? do i need to rewrite my query or change the table design? pls feel free to criticize. thanks a lot.

 

here's the mysql code:

$sql = "SELECT product_num FROM products WHERE product_id IN 
			(
			SELECT to_product_id FROM tconversions WHERE fr_product_id IN
				(
				SELECT product_id FROM products WHERE product_num = '$product_num'
				)
			)";

 

and here are the tables' descriptions:

 

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `product_name` varchar(75) COLLATE latin1_general_ci NOT NULL,
  `product_num` varchar(6) COLLATE latin1_general_ci NOT NULL,
  `category_id` int(3) NOT NULL,
  `manufacturer_id` int(2) NOT NULL DEFAULT '0',
  `sell_price` decimal(9,2) NOT NULL DEFAULT '0.00',
  `active` int(1) NOT NULL COMMENT '1 if active, 0 if inactive',
  `lastsale` date NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM

CREATE TABLE IF NOT EXISTS `tconversions` (
  `tconv_id` int(6) NOT NULL AUTO_INCREMENT,
  `fr_product_id` int(5) NOT NULL,
  `to_product_id` int(5) NOT NULL,
  `conv_units` decimal(8,2) NOT NULL,
  PRIMARY KEY (`tconv_id`)
) ENGINE=MyISAM;

 

[awjudd]

By using JOINS instead of IN

 

SELECT p.product_num 
FROM products p
JOIN tconversions t ON p.product_id = t.to_product_id to_product_id 
JOIN products p2 ON t.fr_product_id =  p2.product_id 
WHERE p2.product_num = '$product_num'

[needs_upgrade]

thanks juddster! cheers!

[awjudd]

Please mark the topic as complete.

 

~juddster