title: Slow query SELECT ... GROUP BY on two tables fields` (MySQL/InnoDB)
body: <p>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.</p>
  
  <p><strong>Currently it's very slow (10 seconds on my setup), I need to speed it up.</strong></p>
  
  <p><strong>Update: switching over to MyISAM is not an option, I need transactions</strong></p>
  
  <p>The query currently is like that:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>Explain :</p>
  
  <p><img src="https://i.stack.imgur.com/esNL8.png" alt="explain results"></p>
  
  <p>Schema (truncated, only required fields left):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Server is MySQL 5.0.77, tables are InnoDB. 
  <code>orders</code> is about 75k records, <code>order_items</code> 160k records, <code>product_variations</code> 140k records, <code>product</code> - 300 records.</p>
  
  <p>Any help is appreciated.</p>