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

編集履歴

質問編集履歴

6

クエリーパターンを追加

2020/06/25 05:30

投稿

MagMag
MagMag

スコア80

title CHANGED
File without changes
body CHANGED
@@ -33,7 +33,7 @@
33
33
  |、、、|、、、|、、、|、、、|、、、|、、、|
34
34
 
35
35
 
36
- ### クエリー
36
+ # クエリー１（baseball.pitchに集約）
37
37
  ```MySQL
38
38
  explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
39
39
  from baseball.pitch as p
@@ -47,7 +47,7 @@
47
47
  ```
48
48
 
49
49
 
50
- ### Explainで実行計画結果。
50
+ ### Explainで実行計画結果（クエリー１）。
51
51
  #### Indexを貼らない場合
52
52
  ```mySQL
53
53
  +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
@@ -81,4 +81,41 @@
81
81
  | 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 |
82
82
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
83
83
  1 row in set (0.00 sec)
84
+ ```
85
+
86
+ # クエリー2（baseball.playerに集約）
87
+ ```MySQL
88
+ explain select * from baseball.player as 選手
89
+ left join baseball.pitch as 投手
90
+ on 選手.年度 = 投手.年度
91
+ and 選手.選手ID = 投手.投手ID
92
+ left join baseball.pitch as 打者
93
+ on 選手.年度 = 打者.年度
94
+ and 選手.選手ID = 打者.打者ID;
95
+ ```
96
+
97
+ ### Explainで実行計画結果（クエリー２）。
98
+ #### Indexを貼った場合
99
+ ```mySQL
100
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
101
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
102
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
103
+ | 1 | SIMPLE | 選手 | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
104
+ | 1 | SIMPLE | 投手 | NULL | ref | id_year_index,id_year_index_toshu | id_year_index_toshu | 10 | baseball.選手.年度,baseball.選手.選手ID | 1002 | 100.00 | NULL |
105
+ | 1 | SIMPLE | 打者 | NULL | ref | id_year_index,id_year_index_toshu | id_year_index | 10 | baseball.選手.年度,baseball.選手.選手ID | 734 | 100.00 | NULL |
106
+ +----+-------------+--------+------------+------+-----------------------------------+---------------------+---------+-------------------------------------------------+------+----------+-------+
107
+ 3 rows in set, 1 warning (0.00 sec)
108
+ ```
109
+
110
+ #### Indexを貼らない場合
111
+ ```mySQL
112
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
113
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
114
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
115
+ | 1 | SIMPLE | 選手 | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
116
+ | 1 | SIMPLE | 投手 | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
117
+ | 1 | SIMPLE | 打者 | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
118
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
119
+ 3 rows in set, 1 warning (0.00 sec)
120
+
84
121
  ```

5

クエリの場所変更

2020/06/25 05:30

投稿

MagMag
MagMag

スコア80

title CHANGED
File without changes
body CHANGED
@@ -7,19 +7,7 @@
7
7
  本例のように、joinを複数実施する時に遅くしないようにどうしたらいいでしょうか？
8
8
  また、Primary Key設定はしていません(こういった場合に設定したら早くなるのかがわからないため)。
9
9
 
10
- ### クエリー
10
+ ### テーブル概要
11
- ```MySQL
12
- explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
13
- from baseball.pitch as p
14
- join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
15
- on p.年度 = 投手テーブル.年度
16
- and p.投手ID = 投手テーブル.選手ID
17
- join (select 年度, 選手ID, 身長, 年俸 from baseball.player) as 打者テーブル
18
- on p.年度 = 打者テーブル.年度
19
- and p.打者ID = 打者テーブル.選手ID
20
- limit 10000;
21
- ```
22
-
23
11
  ##### baseball.pitch (70万レコード)
24
12
  |年度|投手ID|打者ID|投げた球の種類|時刻|アウトカウント|
25
13
  |:--:|:--:|:--:|:--:|:--:|:--:|
@@ -45,6 +33,20 @@
45
33
  |、、、|、、、|、、、|、、、|、、、|、、、|
46
34
 
47
35
 
36
+ ### クエリー
37
+ ```MySQL
38
+ explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
39
+ from baseball.pitch as p
40
+ join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
41
+ on p.年度 = 投手テーブル.年度
42
+ and p.投手ID = 投手テーブル.選手ID
43
+ join (select 年度, 選手ID, 身長, 年俸 from baseball.player) as 打者テーブル
44
+ on p.年度 = 打者テーブル.年度
45
+ and p.打者ID = 打者テーブル.選手ID
46
+ limit 10000;
47
+ ```
48
+
49
+
48
50
  ### Explainで実行計画結果。
49
51
  #### Indexを貼らない場合
50
52
  ```mySQL

4

EXLPAINの結果追記

2020/06/24 13:36

投稿

MagMag
MagMag

スコア80

title CHANGED
File without changes
body CHANGED
@@ -7,9 +7,9 @@
7
7
  本例のように、joinを複数実施する時に遅くしないようにどうしたらいいでしょうか？
8
8
  また、Primary Key設定はしていません(こういった場合に設定したら早くなるのかがわからないため)。
9
9
 
10
- ### スクリプト
10
+ ### クエリー
11
11
  ```MySQL
12
- select データ内連番, p.年度, 年俸 as 投手年俸, 身長 as 打者身長
12
+ explain select p.年度, 投手テーブル.年俸 as 投手年俸, 身長 as 打者身長
13
13
  from baseball.pitch as p
14
14
  join (select 年度, 年俸, 選手ID from baseball.player) as 投手テーブル
15
15
  on p.年度 = 投手テーブル.年度
@@ -47,15 +47,36 @@
47
47
 
48
48
  ### Explainで実行計画結果。
49
49
  #### Indexを貼らない場合
50
+ ```mySQL
51
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
50
- | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
52
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
51
- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
53
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
52
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
54
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100.00 | NULL |
53
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10 | Using where; Using join buffer (Block Nested Loop) |
55
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
54
- | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.1 | Using where; Using join buffer (Block Nested Loop) |
56
+ | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.10 | Using where; Using join buffer (Block Nested Loop) |
57
+ +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
58
+ 3 rows in set, 1 warning (0.00 sec)
59
+ ```
60
+ #### Indexを貼った場合
55
61
 
56
- #### Indexを貼った場合
57
- | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
58
- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
59
- | 1 | SIMPLE | p | NULL | ALL | id\_year\_index | NULL | NULL | NULL | 771848 | 100 | Using where |
60
- | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100 | NULL |
61
- | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.打者ID | 1 | 100 | NULL |
62
+ ```mySQL
63
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
64
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
65
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
66
+ | 1 | SIMPLE | p | NULL | ALL | id_year_index | NULL | NULL | NULL | 771848 | 100.00 | Using where |
67
+ | 1 | SIMPLE | player | NULL | ref | id_year_index | id_year_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100.00 | NULL |
68
+ | 1 | SIMPLE | player | NULL | ref | id_year_index | id_year_index | 10 | baseball.p.年度,baseball.p.打者ID | 1 | 100.00 | NULL |
69
+ +----+-------------+--------+------------+------+---------------+---------------+---------+---------------------------------------+--------+----------+-------------+
70
+ 3 rows in set, 1 warning (0.00 sec)
71
+ ```
72
+
73
+
74
+ Warning内容
75
+ ```
76
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
77
+ | Level | Code | Message |
78
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
79
+ | 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 |
80
+ +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
81
+ 1 row in set (0.00 sec)
82
+ ```

3

EXPLAINの実行結果の追記

2020/06/24 13:12

投稿

MagMag
MagMag

スコア80

title CHANGED
File without changes
body CHANGED
@@ -45,9 +45,17 @@
45
45
  |、、、|、、、|、、、|、、、|、、、|、、、|
46
46
 
47
47
 
48
- また、実行計画を作成してみました。まだ内容を把握できていないので、これから調べます。
48
+ ### Explainで実行計画結果。
49
+ #### Indexを貼らない場合
49
50
  | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
50
51
  | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
51
52
  | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
52
- | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | Using join buffer (Block Nested Loop) |
53
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 10 | Using where; Using join buffer (Block Nested Loop) |
53
- | 1 | SIMPLE | pitch | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 100 | Using where; Using join buffer (Block Nested Loop) |
54
+ | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 771848 | 0.1 | Using where; Using join buffer (Block Nested Loop) |
55
+
56
+ #### Indexを貼った場合
57
+ | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
58
+ | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
59
+ | 1 | SIMPLE | p | NULL | ALL | id\_year\_index | NULL | NULL | NULL | 771848 | 100 | Using where |
60
+ | 1 | SIMPLE | player | NULL | eq\_ref | id\_year\_index | id\_year\_index | 10 | baseball.p.年度,baseball.p.投手ID | 1 | 100 | NULL |
61
+ | 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

title CHANGED
File without changes
body CHANGED
@@ -42,4 +42,12 @@
42
42
  |2017|101|175|10000|B|1989/3/1|
43
43
  |2017|102|170|8000|C|1991/5/1|
44
44
  |2018|100|180|15000|A|1982/1/1|
45
- |、、、|、、、|、、、|、、、|、、、|、、、|
45
+ |、、、|、、、|、、、|、、、|、、、|、、、|
46
+
47
+
48
+ また、実行計画を作成してみました。まだ内容を把握できていないので、これから調べます。
49
+ | id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
50
+ | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
51
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | NULL |
52
+ | 1 | SIMPLE | player | NULL | ALL | NULL | NULL | NULL | NULL | 2704 | 100 | Using join buffer (Block Nested Loop) |
53
+ | 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

title CHANGED
File without changes
body CHANGED
@@ -21,20 +21,25 @@
21
21
  ```
22
22
 
23
23
  ##### baseball.pitch (70万レコード)
24
- |年度|投手ID|打者ID|
24
+ |年度|投手ID|打者ID|投げた球の種類|時刻|アウトカウント|
25
- |:--:|:--:|:--:|
25
+ |:--:|:--:|:--:|:--:|:--:|:--:|
26
- |2017|100|102|
26
+ |2017|100|102|ストレート|19:00|0|
27
+ |2017|100|102|ストレート|19:01|0|
28
+ |2017|100|102|スライダー|19:01|0|
27
- |2017|101|100|
29
+ |2017|101|100|カーブ|19:03|1|
28
- |2018|100|102|
29
- |、、、|、、、|、、、|
30
+ |、、、|、、、|、、、|、、、|、、、|
31
+ |2018|100|102|カーブ|20:00|0|
32
+ |2018|100|102|カーブ|20:00|0|
33
+ |2018|100|102|カーブ|20:02|0|
34
+ |、、、|、、、|、、、|、、、|、、、|、、、|
30
35
 
31
36
 
32
37
 
33
38
  #### baseball.player (3000レコード)
34
- |年度|選手ID|身長|年俸|氏名|
39
+ |年度|選手ID|身長|年俸|氏名|生年月日|
35
- |:--|:--:|:--:|:--:|:--:|
40
+ |:--|:--:|:--:|:--:|:--:|:--:|
36
- |2017|100|180|20000|A|
41
+ |2017|100|180|20000|A|1990/1/1|
37
- |2017|101|175|10000|B|
42
+ |2017|101|175|10000|B|1989/3/1|
38
- |2017|102|170|8000|C|
43
+ |2017|102|170|8000|C|1991/5/1|
39
- |2018|100|180|15000|A|
44
+ |2018|100|180|15000|A|1982/1/1|
40
- |、、、|、、、|、、、|、、、|、、、|
45
+ |、、、|、、、|、、、|、、、|、、、|、、、|