teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問SQLでJoin句をつなげるとQueryに10分くらいかかってしまう。

編集履歴

質問編集履歴

6

クエリーパターンを追加

2020/06/25 05:30

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -68,7 +68,7 @@
68
68
 
69
69
 
70
70
 
71
- ### クエリー
71
+ # クエリー１（baseball.pitchに集約）
72
72
 
73
73
  ```MySQL
74
74
 
@@ -96,7 +96,7 @@
96
96
 
97
97
 
98
98
 
99
- ### Explainで実行計画結果。
99
+ ### Explainで実行計画結果（クエリー１）。
100
100
 
101
101
  #### Indexを貼らない場合
102
102
 
@@ -165,3 +165,77 @@
165
165
  1 row in set (0.00 sec)
166
166
 
167
167
  ```
168
+
169
+
170
+
171
+ # クエリー2（baseball.playerに集約）
172
+
173
+ ```MySQL
174
+
175
+ explain select * from baseball.player as 選手
176
+
177
+ left join baseball.pitch as 投手
178
+
179
+ on 選手.年度 = 投手.年度
180
+
181
+ and 選手.選手ID = 投手.投手ID
182
+
183
+ left join baseball.pitch as 打者
184
+
185
+ on 選手.年度 = 打者.年度
186
+
187
+ and 選手.選手ID = 打者.打者ID;
188
+
189
+ ```
190
+
191
+
192
+
193
+ ### Explainで実行計画結果（クエリー２）。
194
+
195
+ #### Indexを貼った場合
196
+
197
+ ```mySQL
198
+
199
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
200
+
201
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
202
+
203
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
204
+
205
+ | 1 | SIMPLE | 選手 | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
206
+
207
+ | 1 | SIMPLE | 投手 | NULL | ref | id_year_index,id_year_index_toshu | id_year_index_toshu | 10 | baseball.選手.年度,baseball.選手.選手ID | 1002 | 100.00 | NULL |
208
+
209
+ | 1 | SIMPLE | 打者 | NULL | ref | id_year_index,id_year_index_toshu | id_year_index | 10 | baseball.選手.年度,baseball.選手.選手ID | 734 | 100.00 | NULL |
210
+
211
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
212
+
213
+ 3 rows in set, 1 warning (0.00 sec)
214
+
215
+ ```
216
+
217
+
218
+
219
+ #### Indexを貼らない場合
220
+
221
+ ```mySQL
222
+
223
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
224
+
225
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
226
+
227
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
228
+
229
+ | 1 | SIMPLE | 選手 | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
230
+
231
+ | 1 | SIMPLE | 投手 | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
232
+
233
+ | 1 | SIMPLE | 打者 | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
234
+
235
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
236
+
237
+ 3 rows in set, 1 warning (0.00 sec)
238
+
239
+
240
+
241
+ ```

5

クエリの場所変更

2020/06/25 05:30

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -16,31 +16,7 @@
16
16
 
17
17
 
18
18
 
19
- ### クエリー
19
+ ### テーブル概要
20
-
21
- ```MySQL
22
-
23
- explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
24
-
25
- from baseball.pitch as p
26
-
27
- join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
28
-
29
- on p.年度 = 投手テーブル.年度
30
-
31
- and p.投手ID = 投手テーブル.選手ID
32
-
33
- join (select 年度, 選手ID, 身長, 年俸 from baseball.player) as 打者テーブル
34
-
35
- on p.年度 = 打者テーブル.年度
36
-
37
- and p.打者ID = 打者テーブル.選手ID
38
-
39
- limit 10000;
40
-
41
- ```
42
-
43
-
44
20
 
45
21
  ##### baseball.pitch (70万レコード)
46
22
 
@@ -87,6 +63,34 @@
87
63
  |2018|100|180|15000|A|1982/1/1|
88
64
 
89
65
  |、、、|、、、|、、、|、、、|、、、|、、、|
66
+
67
+
68
+
69
+
70
+
71
+ ### クエリー
72
+
73
+ ```MySQL
74
+
75
+ explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
76
+
77
+ from baseball.pitch as p
78
+
79
+ join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
80
+
81
+ on p.年度 = 投手テーブル.年度
82
+
83
+ and p.投手ID = 投手テーブル.選手ID
84
+
85
+ join (select 年度, 選手ID, 身長, 年俸 from baseball.player) as 打者テーブル
86
+
87
+ on p.年度 = 打者テーブル.年度
88
+
89
+ and p.打者ID = 打者テーブル.選手ID
90
+
91
+ limit 10000;
92
+
93
+ ```
90
94
 
91
95
 
92
96
 

4

EXLPAINの結果追記

2020/06/24 13:36

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -16,11 +16,11 @@
16
16
 
17
17
 
18
18
 
19
- ### スクリプト
19
+ ### クエリー
20
20
 
21
21
  ```MySQL
22
22
 
23
- select データ内連番, p.年度, 年俸 as 投手年俸, 身長 as 打者身長
23
+ explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
24
24
 
25
25
  from baseball.pitch as p
26
26
 
@@ -96,26 +96,68 @@
96
96
 
97
97
  #### Indexを貼らない場合
98
98
 
99
- | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
99
+ ```mySQL
100
100
 
101
- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
101
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
102
102
 
103
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
103
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
104
104
 
105
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10 | Using where; Using join buffer (Block Nested Loop) |
105
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
106
106
 
107
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
108
+
109
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
110
+
107
- | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.1 | Using where; Using join buffer (Block Nested Loop) |
111
+ | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.10 | Using where; Using join buffer (Block Nested Loop) |
112
+
113
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
114
+
115
+ 3 rows in set, 1 warning (0.00 sec)
116
+
117
+ ```
118
+
119
+ #### Indexを貼った場合
108
120
 
109
121
 
110
122
 
111
- #### Indexを貼った場合
123
+ ```mySQL
112
124
 
113
- | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
125
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
114
126
 
115
- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
127
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
116
128
 
117
- | 1 | SIMPLE | p | NULL | ALL | id\_year\_index | NULL | NULL | NULL | 771848 | 100 | Using where |
129
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
118
130
 
119
- | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100 | NULL |
131
+ | 1 | SIMPLE | p | NULL | ALL | id_year_index | NULL | NULL | NULL | 771848 | 100.00 | Using where |
120
132
 
121
- | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.打者ID | 1 | 100 | NULL |
133
+ | 1 | SIMPLE | player | NULL | ref | id_year_index | id_year_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100.00 | NULL |
134
+
135
+ | 1 | SIMPLE | player | NULL | ref | id_year_index | id_year_index | 10 | baseball.p.年度,baseball.p.打者ID | 1 | 100.00 | NULL |
136
+
137
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
138
+
139
+ 3 rows in set, 1 warning (0.00 sec)
140
+
141
+ ```
142
+
143
+
144
+
145
+
146
+
147
+ Warning内容
148
+
149
+ ```
150
+
151
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
152
+
153
+ | Level | Code | Message |
154
+
155
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
156
+
157
+ | Note | 1003 | /* select#1 */ select `baseball`.`p`.`年度` AS `年度`,`baseball`.`player`.`年俸` AS `投手年俸`,`baseball`.`player`.`身長` AS `打者身長` from `baseball`.`pitch` `p` join `baseball`.aseball`.`player` where ((`baseball`.`player`.`選手ID` = `baseball`.`p`.`投手ID`) and (`baseball`.`player`.`選手ID` = `baseball`.`p`.`打者ID`) and (`baseball`.`player`.`年度` = `baseball`.`p`.`年度eball`.`player`.`年度` = `baseball`.`p`.`年度`)) limit 10000 |
158
+
159
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
160
+
161
+ 1 row in set (0.00 sec)
162
+
163
+ ```

3

EXPLAINの実行結果の追記

2020/06/24 13:12

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -92,7 +92,9 @@
92
92
 
93
93
 
94
94
 
95
- また、実行計画を作成してみました。まだ内容を把握できていないので、これから調べます。
95
+ ### Explainで実行計画結果。
96
+
97
+ #### Indexを貼らない場合
96
98
 
97
99
  | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
98
100
 
@@ -100,6 +102,20 @@
100
102
 
101
103
  | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
102
104
 
103
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | Using join buffer (Block Nested Loop) |
105
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10 | Using where; Using join buffer (Block Nested Loop) |
104
106
 
105
- | 1 | SIMPLE | pitch | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100 | Using where; Using join buffer (Block Nested Loop) |
107
+ | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.1 | Using where; Using join buffer (Block Nested Loop) |
108
+
109
+
110
+
111
+ #### Indexを貼った場合
112
+
113
+ | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
114
+
115
+ | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
116
+
117
+ | 1 | SIMPLE | p | NULL | ALL | id\_year\_index | NULL | NULL | NULL | 771848 | 100 | Using where |
118
+
119
+ | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100 | NULL |
120
+
121
+ | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.打者ID | 1 | 100 | NULL |

2

EXPLAINによる実行計画の追記

2020/06/23 12:46

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -87,3 +87,19 @@
87
87
  |2018|100|180|15000|A|1982/1/1|
88
88
 
89
89
  |、、、|、、、|、、、|、、、|、、、|、、、|
90
+
91
+
92
+
93
+
94
+
95
+ また、実行計画を作成してみました。まだ内容を把握できていないので、これから調べます。
96
+
97
+ | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
98
+
99
+ | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
100
+
101
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
102
+
103
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | Using join buffer (Block Nested Loop) |
104
+
105
+ | 1 | SIMPLE | pitch | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100 | Using where; Using join buffer (Block Nested Loop) |

1

表を更新

2020/06/22 09:40

投稿

MagMag
MagMag

スコア80

test CHANGED
File without changes
test CHANGED
@@ -44,17 +44,27 @@
44
44
 
45
45
  ##### baseball.pitch (70万レコード)
46
46
 
47
- |年度|投手ID|打者ID|
47
+ |年度|投手ID|打者ID|投げた球の種類|時刻|アウトカウント|
48
48
 
49
- |:--:|:--:|:--:|
49
+ |:--:|:--:|:--:|:--:|:--:|:--:|
50
50
 
51
- |2017|100|102|
51
+ |2017|100|102|ストレート|19:00|0|
52
52
 
53
- |2017|101|100|
53
+ |2017|100|102|ストレート|19:01|0|
54
54
 
55
- |2018|100|102|
55
+ |2017|100|102|スライダー|19:01|0|
56
56
 
57
+ |2017|101|100|カーブ|19:03|1|
58
+
57
- |、、、|、、、|、、、|
59
+ |、、、|、、、|、、、|、、、|、、、|
60
+
61
+ |2018|100|102|カーブ|20:00|0|
62
+
63
+ |2018|100|102|カーブ|20:00|0|
64
+
65
+ |2018|100|102|カーブ|20:02|0|
66
+
67
+ |、、、|、、、|、、、|、、、|、、、|、、、|
58
68
 
59
69
 
60
70
 
@@ -64,16 +74,16 @@
64
74
 
65
75
  #### baseball.player (3000レコード)
66
76
 
67
- |年度|選手ID|身長|年俸|氏名|
77
+ |年度|選手ID|身長|年俸|氏名|生年月日|
68
78
 
69
- |:--|:--:|:--:|:--:|:--:|
79
+ |:--|:--:|:--:|:--:|:--:|:--:|
70
80
 
71
- |2017|100|180|20000|A|
81
+ |2017|100|180|20000|A|1990/1/1|
72
82
 
73
- |2017|101|175|10000|B|
83
+ |2017|101|175|10000|B|1989/3/1|
74
84
 
75
- |2017|102|170|8000|C|
85
+ |2017|102|170|8000|C|1991/5/1|
76
86
 
77
- |2018|100|180|15000|A|
87
+ |2018|100|180|15000|A|1982/1/1|
78
88
 
79
- |、、、|、、、|、、、|、、、|、、、|
89
+ |、、、|、、、|、、、|、、、|、、、|、、、|