1 Vote Vote

Slow query SELECT ... GROUP BY on two tables fields` (MySQL/InnoDB)

Posted by topdog 310 days ago Questions| product order null All

I have a query to select orders, and for each order I need to get list of products it includes with summary quantity for each product.

Currently it's very slow (10 seconds on my setup), I need to speed it up.

Update: switching over to MyISAM is not an option, I need transactions

The query currently is like that:

SELECT `Order`.*, `Product`.`id`, `Product`.`msrp`, SUM(`OrderItem`.`quantity`) AS sum
FROM `orders` AS `Order` 
LEFT JOIN `order_items` AS `OrderItem` ON (`OrderItem`.`order_id` = `Order`.`id`)
LEFT JOIN `product_variations` AS `ProductVariation` ON (`OrderItem`.`product_variation_id` = `ProductVariation`.`id`)
LEFT JOIN `products` AS `Product` ON (`ProductVariation`.`product_id` = `Product`.`id`)
WHERE 1 = 1
GROUP BY `Order`.`id`, `Product`.`id`
ORDER BY `Order`.`created` DESC
LIMIT 20;

Explain :

explain results

Schema (truncated, only required fields left):

CREATE TABLE `orders` (
  `id` int(11) NOT NULL auto_increment,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `customer_comments` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `created` (`created`),
  KEY `id_created` (`id`,`created`),
) ENGINE=InnoDB 

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` int(11) NOT NULL,
  `product_variation_id` int(11) NOT NULL,
  `type` enum('First','Second') default NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `item_UNIQUE` (`order_id`,`product_variation_id`,`type`),
  KEY `fk_order_items_product_variations1` (`product_variation_id`),
  CONSTRAINT `fk_order_items_orders1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_items_product_variations1` FOREIGN KEY (`product_variation_id`) REFERENCES `product_variations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB

CREATE TABLE `product_variations` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  CONSTRAINT `fk_product_variations_products1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB 

CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `msrp` decimal(5,2) NOT NULL,
  PRIMARY KEY  (`id`),
) ENGINE=InnoDB

Server is MySQL 5.0.77, tables are InnoDB. orders is about 75k records, order_items 160k records, product_variations 140k records, product - 300 records.

Any help is appreciated.

Originally asked by: lxa on Stack Overflow

Discuss Bury


Who Voted for this Question