teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問MySQLのパフォーマンス改善(共通テーブル式が使えず、サブクエリが大量発生)

編集履歴

質問編集履歴

4

追記

2019/11/20 15:08

投稿

wwwww
wwwww

スコア41

test CHANGED
File without changes
test CHANGED
@@ -4,7 +4,7 @@
4
4
 
5
5
 
6
6
 
7
- ■質問
7
+ ■質問(※2019/11/21 00:00 大きく修正しております。申し訳ありません。。)
8
8
 
9
9
  以下のような「サンプルデータ」、「期待する結果」、「(期待結果は取得できるが、時間がかかる)SQL」において、パフォーマンスが遅く困っています。
10
10
 
@@ -16,67 +16,103 @@
16
16
 
17
17
  ●サンプルデータ
18
18
 
19
- X,Y,create_time
19
+ Name,X,Y,create_time
20
20
 
21
- (0,0,'2019-11-20 01:00'),
21
+ (A,0,0,'2019-11-20 01:00'),
22
22
 
23
- (0,1,'2019-11-20 01:10'),
23
+ (A,0,1,'2019-11-20 01:10'),
24
24
 
25
- (0,2,'2019-11-20 01:20'),
25
+ (B,0,1,'2019-11-20 01:20'),
26
26
 
27
- (0,1,'2019-11-20 01:30'),
27
+ (A,0,1,'2019-11-20 01:30'),
28
28
 
29
- (1,2,'2019-11-20 01:40'),
29
+ (A,1,1,'2019-11-20 01:40'),
30
30
 
31
- (1,1,'2019-11-20 01:50'),
31
+ (B,1,1,'2019-11-20 01:50'),
32
32
 
33
- (2,0,'2019-11-20 02:00'),
33
+ (A,0,1,'2019-11-20 02:00'),
34
34
 
35
- (0,1,'2019-11-20 03:50'),
35
+ (A,0,1,'2019-11-20 03:50'),
36
36
 
37
- (0,1,'2019-11-20 05:50')
37
+ (B,0,1,'2019-11-20 05:50')
38
38
 
39
39
  ```
40
40
 
41
41
  ```
42
42
 
43
- ●期待する結果…4カラム目の、それまでのパターン別発生回数が知りたいです
43
+ ●期待する結果…当該Nameの、それまでのパターン別発生回数が知りたいです
44
44
 
45
- |X|Y|count|create_time|count|
45
+ |Name|X|Y|count|create_time|0-0|0-1|1-0|1-1|
46
46
 
47
- |0|0|2019-11-20 01:00|0|
47
+ |A|0|0|2019-11-20 01:00|0|0|0|0|
48
48
 
49
- |0|1|2019-11-20 01:10|0|
49
+ |A|0|1|2019-11-20 01:10|1|0|0|0|
50
50
 
51
- |0|2|2019-11-20 01:20|0|
51
+ |B|0|1|2019-11-20 01:20|0|0|0|0|
52
52
 
53
- |0|1|2019-11-20 01:30|1|
53
+ |A|0|1|2019-11-20 01:30|1|1|0|0|
54
54
 
55
- |1|2|2019-11-20 01:40|0|
55
+ |A|1|1|2019-11-20 01:40|1|2|0|0|
56
56
 
57
- |1|1|2019-11-20 01:50|0|
57
+ |B|1|1|2019-11-20 01:50|0|1|0|0|
58
58
 
59
- |2|0|2019-11-20 02:00|0|
59
+ |A|0|1|2019-11-20 02:00|1|2|0|1|
60
60
 
61
- |0|1|2019-11-20 03:50|1|
61
+ |A|0|1|2019-11-20 03:50|1|3|0|1|
62
62
 
63
- |0|1|2019-11-20 05:50|2|
63
+ |B|0|1|2019-11-20 05:50|0|1|0|1|
64
64
 
65
65
  ```
66
66
 
67
67
  ```MySQL
68
68
 
69
- Select X,Y,create_time,(
69
+ Select Name,X,Y,create_time,(
70
70
 
71
- Select count(X=1 and Y=2)
71
+ Select count(X=0 and Y=0)
72
72
 
73
73
  From Table as B
74
74
 
75
+ Where A.Name=B.Name
76
+
75
- Where A.create_time > B.create_time
77
+ And A.create_time > B.create_time
78
+
79
+ ),
80
+
81
+ (
82
+
83
+ Select count(X=0 and Y=1)
84
+
85
+ From Table as B
86
+
87
+ Where A.Name=B.Name
88
+
89
+ And A.create_time > B.create_time
76
90
 
77
91
  )
78
92
 
93
+ (
94
+
79
- ...(同様のサブクエリを、X,Yのパターン分)
95
+ Select count(X=1 and Y=0)
96
+
97
+ From Table as B
98
+
99
+ Where A.Name=B.Name
100
+
101
+ And A.create_time > B.create_time
102
+
103
+ ),
104
+
105
+ (
106
+
107
+ Select count(X=1 and Y=1)
108
+
109
+ From Table as B
110
+
111
+ Where A.Name=B.Name
112
+
113
+ And A.create_time > B.create_time
114
+
115
+ )
80
116
 
81
117
  From Table as A
82
118
 

3

追記

2019/11/20 15:08

投稿

wwwww
wwwww

スコア41

test CHANGED
File without changes
test CHANGED
@@ -6,31 +6,87 @@
6
6
 
7
7
  ■質問
8
8
 
9
- 以下のような構造のSQLにおいて、パフォーマンスが遅く困っています。
9
+ 以下のような「サンプルデータ」、「期待する結果」、「(期待結果は取得できるが、時間がかかる)SQL」において、パフォーマンスが遅く困っています。
10
10
 
11
11
  改善方法をご存知の方がいらしたら、ご教示お願いできませんでしょうか。
12
12
 
13
13
 
14
14
 
15
+ ```
16
+
17
+ ●サンプルデータ
18
+
19
+ X,Y,create_time
20
+
21
+ (0,0,'2019-11-20 01:00'),
22
+
23
+ (0,1,'2019-11-20 01:10'),
24
+
25
+ (0,2,'2019-11-20 01:20'),
26
+
27
+ (0,1,'2019-11-20 01:30'),
28
+
29
+ (1,2,'2019-11-20 01:40'),
30
+
31
+ (1,1,'2019-11-20 01:50'),
32
+
33
+ (2,0,'2019-11-20 02:00'),
34
+
35
+ (0,1,'2019-11-20 03:50'),
36
+
37
+ (0,1,'2019-11-20 05:50')
38
+
39
+ ```
40
+
41
+ ```
42
+
43
+ ●期待する結果…4カラム目の、それまでのパターン別発生回数が知りたいです
44
+
45
+ |X|Y|count|create_time|count|
46
+
47
+ |0|0|2019-11-20 01:00|0|
48
+
49
+ |0|1|2019-11-20 01:10|0|
50
+
51
+ |0|2|2019-11-20 01:20|0|
52
+
53
+ |0|1|2019-11-20 01:30|1|
54
+
55
+ |1|2|2019-11-20 01:40|0|
56
+
57
+ |1|1|2019-11-20 01:50|0|
58
+
59
+ |2|0|2019-11-20 02:00|0|
60
+
61
+ |0|1|2019-11-20 03:50|1|
62
+
63
+ |0|1|2019-11-20 05:50|2|
64
+
65
+ ```
66
+
15
67
  ```MySQL
16
68
 
17
- Select create_time,W,X,Y,Z,(
69
+ Select X,Y,create_time,(
18
70
 
19
- Select count(X=1 and Y=2)
71
+ Select count(X=1 and Y=2)
20
72
 
21
- From Table as B
73
+ From Table as B
22
74
 
23
- Where A.create_time > B.create_time
75
+ Where A.create_time > B.create_time
24
76
 
25
- )
77
+ )
78
+
79
+ ...(同様のサブクエリを、X,Yのパターン分)
26
80
 
27
81
  From Table as A
28
82
 
83
+
84
+
85
+ (※実行・期待された結果は取得できるが、時間がかかってしまう)
86
+
29
87
  ```
30
88
 
31
-
32
-
33
- X,Y,Z,Wの組み合わせごとに、当該データより(create_timeが)古いデータの計数を行いたいです。
89
+ X,Yの組み合わせごとに、当該データより(create_timeが)古いデータの計数を行いたいです。
34
90
 
35
91
  組み合わせ数・行数が多いため、クエリが終わらない状況となってしまっています。
36
92
 
@@ -45,21 +101,3 @@
45
101
  不足・不明点等あれば、ご指摘いただけますと幸いです。
46
102
 
47
103
  以上、何卒宜しくお願いいたします。
48
-
49
-
50
-
51
- ■追記(2019/11/19 23:20)
52
-
53
- 不躾ながら追加質問…
54
-
55
- 上記MySQLは(複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っています。
56
-
57
-
58
-
59
- (バージョン8に変更することを考えないといけないのかな、と考え、)
60
-
61
- 同系統のサービスをざっと調べた感じ、バージョン8の物が無い?気がしています。
62
-
63
- ご存知ある方いらっしゃれば、別途教えていただきたいです。
64
-
65
- (MYSQLバージョン8の機能って、世間的にそんなに需要ないものなんでしょうか…？)

2

追記の修正

2019/11/20 05:00

投稿

wwwww
wwwww

スコア41

test CHANGED
File without changes
test CHANGED
@@ -52,9 +52,13 @@
52
52
 
53
53
  不躾ながら追加質問…
54
54
 
55
- (バージョン8に)
55
+ 上記MySQLは(複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っています。
56
56
 
57
+
58
+
59
+ (バージョン8に変更することを考えないといけないのかな、と考え、)
60
+
57
- (複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っているのですが、ざっと調べた感じ、バージョン8の物が無い?気がしています。
61
+ 同系統のサービスをざっと調べた感じ、バージョン8の物が無い?気がしています。
58
62
 
59
63
  ご存知ある方いらっしゃれば、別途教えていただきたいです。
60
64
 

1

追記

2019/11/19 14:25

投稿

wwwww
wwwww

スコア41

test CHANGED
File without changes
test CHANGED
@@ -45,3 +45,17 @@
45
45
  不足・不明点等あれば、ご指摘いただけますと幸いです。
46
46
 
47
47
  以上、何卒宜しくお願いいたします。
48
+
49
+
50
+
51
+ ■追記(2019/11/19 23:20)
52
+
53
+ 不躾ながら追加質問…
54
+
55
+ (バージョン8に)
56
+
57
+ (複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っているのですが、ざっと調べた感じ、バージョン8の物が無い?気がしています。
58
+
59
+ ご存知ある方いらっしゃれば、別途教えていただきたいです。
60
+
61
+ (MYSQLバージョン8の機能って、世間的にそんなに需要ないものなんでしょうか…？)