Форум пользователей MySQL

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int unsigned NOT NULL,
  # есть еще колонки, но они отношения к делу не имеют
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=MyISAM

 CREATE TABLE `order_items` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int unsigned NOT NULL DEFAULT '0',
  `prod_id` int unsigned NOT NULL DEFAULT '0',
  `quantity` int NOT NULL DEFAULT '0',
  `1C_sale_cost` decimal(8,0) DEFAULT NULL,
  `1C_sale_code` char(11) DEFAULT NULL,
  `1C_sale_return_code` char(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `order_id_2` (`order_id`,`prod_id`)
) ENGINE=MyISAM

SELECT SUM(oi.1C_sale_cost * oi.quantity)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.client_id = ...
  AND oi.1C_sale_code IS NOT NULL
  AND oi.1C_sale_return_code IS NULL

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: oi
   partitions: NULL
         type: ALL
possible_keys: order_id_2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 392367
     filtered: 9.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,client_id
          key: PRIMARY
      key_len: 4
          ref: some_db.oi.order_id
         rows: 1
     filtered: 5.00
        Extra: Using where

ALTER TABLE order_items ADD KEY (order_id)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: ref
possible_keys: PRIMARY,client_id
          key: client_id
      key_len: 4
          ref: const
         rows: 164
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: oi
   partitions: NULL
         type: ref
possible_keys: order_id_2,order_id
          key: order_id
      key_len: 4
          ref: some_db.o.id
         rows: 1
     filtered: 9.00
        Extra: Using where