How to improve this query

[needs_upgrade]

Hello guys. I wanted to summarize all the items that are contained in a delivery. A delivery contains one or many sale receipts. My code works but is too slow. And I want it improved.  Any help is highly appreciated. thanks!

 

here's my SQL code:

SELECT p.product_id, p.product_name, SUM(sd.quantity)
FROM products p 
     INNER JOIN sale_details sd ON sd.product_id = p.product_id
     INNER JOIN sales s ON sd.sale_id = s.sale_id
WHERE s.sale_id IN (SELECT sale_id FROM delivery_details WHERE del_id = '$del_id')
GROUP BY sd.product_id
ORDER BY SUM(sd.quantity) DESC

 

 

and here are my tables:

CREATE TABLE `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',
  `acq_price` decimal(9,2) NOT NULL default '0.00',
  `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  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2236 ;

CREATE TABLE `sale_details` (
  `sd_id` int(15) unsigned NOT NULL auto_increment,
  `sale_id` int(10) unsigned NOT NULL,
  `product_id` int(5) unsigned NOT NULL,
  `acq_price` decimal(10,3) NOT NULL,
  `quantity` decimal(7,2) unsigned NOT NULL,
  `unit_price` decimal(10,3) unsigned NOT NULL default '0.000',
  `unit_discount` decimal(8,3) unsigned NOT NULL default '0.000',
  PRIMARY KEY  (`sd_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=139300 ;


CREATE TABLE `sales` (
  `sale_id` int(10) unsigned NOT NULL auto_increment,
  `account_id` int(2) NOT NULL,
  `or_num` varchar(10) character set latin1 collate latin1_general_ci NOT NULL,
  `customer_id` int(4) unsigned NOT NULL default '0',
  `user_id` int(2) unsigned NOT NULL default '0',
  `delivery_date` date default '0000-00-00',
  `due_date` date NOT NULL,
  `notes` varchar(255) character set latin1 collate latin1_general_ci default NULL,
  `line_total` decimal(13,2) unsigned NOT NULL default '0.00',
  `balance` decimal(13,2) unsigned NOT NULL default '0.00',
  `revenue` decimal(10,2) NOT NULL,
  `pmode` int(1) unsigned NOT NULL COMMENT '1 if cash; 2 if check',
  PRIMARY KEY  (`sale_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29485 ;

CREATE TABLE `delivery_details` (
  `dd_id` int(10) NOT NULL auto_increment,
  `del_id` int(10) NOT NULL,
  `sale_id` int(10) NOT NULL,
  PRIMARY KEY  (`dd_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=284 ;

 

[fenway]

Well, replacing IN() with a JOIN is likely going to show improvement.

 

Beyond that, I'll need to see EXPLAIN output.