例えば下記のような2つのテーブルがあるとします。
Stock(在庫テーブル)
SQL
1CREATE TABLE `Stock` ( 2 `stock_id` INT(11) NOT NULL AUTO_INCREMENT, 3 `location_id` INT(11) NULL DEFAULT NULL, 4 `product_id` INT(11) NULL DEFAULT NULL, 5 `stock_num` INT(11) NULL DEFAULT NULL, 6 PRIMARY KEY (`stock_id`), 7 INDEX `location_id` (`location_id`, `product_id`) 8) 9COLLATE='utf8_general_ci' 10ENGINE=InnoDB 11;
|stock_id|location_id|product_id|stock_num|
|:--|:--:|--:|
|1|100|1000|5|
|2|101|1001|3|
|3|102|2000|1|
|4|103|1000|1|
|5|104|3000|4|
|6|103|1001|3|
|7|100|1001|2|
|8|101|2000|4|
BoughtStock(購入在庫)
SQL
1CREATE TABLE `BoughtStock` ( 2 `stock_id` INT(11) NULL DEFAULT NULL, 3 `bought_num` INT(11) NULL DEFAULT NULL, 4 INDEX `stock_id` (`stock_id`) 5) 6COLLATE='utf8_general_ci' 7ENGINE=InnoDB 8ROW_FORMAT=COMPACT 9;
※購入されるたびにレコードが挿入される(stock_idは重複する)
stock_id | bought_num |
---|---|
1 | 1 |
1 | 1 |
4 | 1 |
1 | 1 |
2 | 1 |
2 | 1 |
3 | 1 |
このデータから↓のような結果を抽出しようとしています。
・ロケ、商品ごとの在庫総数
・引当総数(購入点数):そのロケのその商品が何点購入されているのかを抽出
・ロケ内総在庫数(商品問わず):ロケ内の在庫を全商品ですべて合算する
|ロケーションID|商品ID|在庫数|引当総数|ロケ内総在庫数|
|:--|:--:|--:|
|100|1000|5|3|7|
|100|1001|2|0|7|
|101|1001|3|2|7|
|101|2000|4|0|7|
|102|2000|1|1|1|
|103|1000|1|1|4|
|103|1001|3|0|4|
|104|3000|4|0|4|
SQL
1SELECT 2 stk.location_id AS 'ロケーションID' 3 , stk.product_id AS '商品ID' 4 , SUM(stk.stock_num) AS '在庫数' 5 , IFNULL((SELECT SUM(bst.bought_num) FROM BoughtStock bst WHERE bst.stock_id = stk.stock_id), 0) AS '引当総数' 6 , (SELECT SUM(stk2.stock_num) FROM Stock stk2 WHERE stk2.location_id = stk.location_id) AS 'ロケ内総在庫数' 7FROM Stock stk 8GROUP BY stk.location_id, stk.product_id 9ORDER BY stk.location_id, stk.product_id
実行計画
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
|:--|:--:|--:|
|1|PRIMARY|stk|index||location_id|10||8||
|3|DEPENDENT SUBQUERY|stk2|ref|location_id|location_id|5|func|1|Using where|
|2|DEPENDENT SUBQUERY|bst|ref|stock_id|stock_id|5|func|1|Using where|
上記のSQLで結果自体は問題ありませんし、EXPLAINで実行計画を取得してもインデックスも効いているのですが
実際何千行の大量データで抽出すると著しく速度が低下します。
SELECT句のサブクエリの項目を外すと速度は低下しません。
もしかしてSELECT句でサブクエリを使用する場合、上記SQLのような条件の書き方ではインデックスが効かない仕様なのでしょうか。
また、速度低下せずに抽出できるSQLを考案可能な方がいらっしゃいましたらご教示いただきたいです。
※BoughtStockはstock_idの重複があるため、単純なLEFT JOINでは「在庫数」など他の集計項目が倍々になります。
何卒宜しくお願い致します。
回答2件
あなたの回答
tips
プレビュー