質問編集履歴
2
ご指摘のあった通り、CREATE TABLE文と実行計画を記載しました。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,6 +1,21 @@
|
|
|
1
1
|
例えば下記のような２つのテーブルがあるとします。
|
|
2
2
|
|
|
3
3
|
Stock（在庫テーブル）
|
|
4
|
+
|
|
5
|
+
```SQL
|
|
6
|
+
CREATE TABLE `Stock` (
|
|
7
|
+
`stock_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
8
|
+
`location_id` INT(11) NULL DEFAULT NULL,
|
|
9
|
+
`product_id` INT(11) NULL DEFAULT NULL,
|
|
10
|
+
`stock_num` INT(11) NULL DEFAULT NULL,
|
|
11
|
+
PRIMARY KEY (`stock_id`),
|
|
12
|
+
INDEX `location_id` (`location_id`, `product_id`)
|
|
13
|
+
)
|
|
14
|
+
COLLATE='utf8_general_ci'
|
|
15
|
+
ENGINE=InnoDB
|
|
16
|
+
;
|
|
17
|
+
```
|
|
18
|
+
|
|
4
19
|
|stock_id|location_id|product_id|stock_num|
|
|
5
20
|
|:--|:--:|--:|
|
|
6
21
|
|1|100|1000|5|
|
|
@@ -13,6 +28,17 @@
|
|
|
13
28
|
|8|101|2000|4|
|
|
14
29
|
|
|
15
30
|
BoughtStock（購入在庫）
|
|
31
|
+
```SQL
|
|
32
|
+
CREATE TABLE `BoughtStock` (
|
|
33
|
+
`stock_id` INT(11) NULL DEFAULT NULL,
|
|
34
|
+
`bought_num` INT(11) NULL DEFAULT NULL,
|
|
35
|
+
INDEX `stock_id` (`stock_id`)
|
|
36
|
+
)
|
|
37
|
+
COLLATE='utf8_general_ci'
|
|
38
|
+
ENGINE=InnoDB
|
|
39
|
+
ROW_FORMAT=COMPACT
|
|
40
|
+
;
|
|
41
|
+
```
|
|
16
42
|
※購入されるたびにレコードが挿入される（stock_idは重複する）
|
|
17
43
|
|stock_id|bought_num|
|
|
18
44
|
|:--|--:|
|
|
@@ -52,6 +78,14 @@
|
|
|
52
78
|
ORDER BY stk.location_id, stk.product_id
|
|
53
79
|
```
|
|
54
80
|
|
|
81
|
+
実行計画
|
|
82
|
+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
|
|
83
|
+
|:--|:--:|--:|
|
|
84
|
+
|1|PRIMARY|stk|index||location_id|10||8||
|
|
85
|
+
|3|DEPENDENT SUBQUERY|stk2|ref|location_id|location_id|5|func|1|Using where|
|
|
86
|
+
|2|DEPENDENT SUBQUERY|bst|ref|stock_id|stock_id|5|func|1|Using where|
|
|
87
|
+
|
|
88
|
+
|
|
55
89
|
上記のSQLで**結果自体は問題ありません**し、EXPLAINで実行計画を取得しても**インデックスも効いている**のですが
|
|
56
90
|
実際何千行の大量データで抽出すると**著しく速度が低下**します。
|
|
57
91
|
|
1
ご指摘のありました点修正させていただきました。まだ不足していればお手数ですが再度ご指摘くださいませ。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -13,6 +13,7 @@
|
|
|
13
13
|
|8|101|2000|4|
|
|
14
14
|
|
|
15
15
|
BoughtStock（購入在庫）
|
|
16
|
+
※購入されるたびにレコードが挿入される（stock_idは重複する）
|
|
16
17
|
|stock_id|bought_num|
|
|
17
18
|
|:--|--:|
|
|
18
19
|
|1|1|
|
|
@@ -24,6 +25,9 @@
|
|
|
24
25
|
|3|1|
|
|
25
26
|
|
|
26
27
|
このデータから↓のような結果を抽出しようとしています。
|
|
28
|
+
・ロケ、商品ごとの在庫総数
|
|
29
|
+
・引当総数（購入点数）：そのロケのその商品が何点購入されているのかを抽出
|
|
30
|
+
・ロケ内総在庫数（商品問わず）：ロケ内の在庫を全商品ですべて合算する
|
|
27
31
|
|
|
28
32
|
|ロケーションID|商品ID|在庫数|引当総数|ロケ内総在庫数|
|
|
29
33
|
|:--|:--:|--:|
|