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

編集履歴

質問編集履歴

4

追記

2019/11/20 15:08

投稿

wwwww
wwwww

スコア41

title CHANGED
File without changes
body CHANGED
@@ -1,43 +1,61 @@
1
1
  ■環境
2
2
  MySQLバージョン：5.6.36
3
3
 
4
- ■質問
4
+ ■質問(※2019/11/21 00:00 大きく修正しております。申し訳ありません。。)
5
5
  以下のような「サンプルデータ」、「期待する結果」、「(期待結果は取得できるが、時間がかかる)SQL」において、パフォーマンスが遅く困っています。
6
6
  改善方法をご存知の方がいらしたら、ご教示お願いできませんでしょうか。
7
7
 
8
8
  ```
9
9
  ●サンプルデータ
10
- X,Y,create_time
10
+ Name,X,Y,create_time
11
- (0,0,'2019-11-20 01:00'),
11
+ (A,0,0,'2019-11-20 01:00'),
12
- (0,1,'2019-11-20 01:10'),
12
+ (A,0,1,'2019-11-20 01:10'),
13
- (0,2,'2019-11-20 01:20'),
13
+ (B,0,1,'2019-11-20 01:20'),
14
- (0,1,'2019-11-20 01:30'),
14
+ (A,0,1,'2019-11-20 01:30'),
15
- (1,2,'2019-11-20 01:40'),
15
+ (A,1,1,'2019-11-20 01:40'),
16
- (1,1,'2019-11-20 01:50'),
16
+ (B,1,1,'2019-11-20 01:50'),
17
- (2,0,'2019-11-20 02:00'),
17
+ (A,0,1,'2019-11-20 02:00'),
18
- (0,1,'2019-11-20 03:50'),
18
+ (A,0,1,'2019-11-20 03:50'),
19
- (0,1,'2019-11-20 05:50')
19
+ (B,0,1,'2019-11-20 05:50')
20
20
  ```
21
21
  ```
22
- ●期待する結果…4カラム目の、それまでのパターン別発生回数が知りたいです
22
+ ●期待する結果…当該Nameの、それまでのパターン別発生回数が知りたいです
23
- |X|Y|count|create_time|count|
23
+ |Name|X|Y|count|create_time|0-0|0-1|1-0|1-1|
24
- |0|0|2019-11-20 01:00|0|
24
+ |A|0|0|2019-11-20 01:00|0|0|0|0|
25
- |0|1|2019-11-20 01:10|0|
25
+ |A|0|1|2019-11-20 01:10|1|0|0|0|
26
- |0|2|2019-11-20 01:20|0|
26
+ |B|0|1|2019-11-20 01:20|0|0|0|0|
27
- |0|1|2019-11-20 01:30|1|
27
+ |A|0|1|2019-11-20 01:30|1|1|0|0|
28
- |1|2|2019-11-20 01:40|0|
28
+ |A|1|1|2019-11-20 01:40|1|2|0|0|
29
- |1|1|2019-11-20 01:50|0|
29
+ |B|1|1|2019-11-20 01:50|0|1|0|0|
30
- |2|0|2019-11-20 02:00|0|
30
+ |A|0|1|2019-11-20 02:00|1|2|0|1|
31
- |0|1|2019-11-20 03:50|1|
31
+ |A|0|1|2019-11-20 03:50|1|3|0|1|
32
- |0|1|2019-11-20 05:50|2|
32
+ |B|0|1|2019-11-20 05:50|0|1|0|1|
33
33
  ```
34
34
  ```MySQL
35
- Select X,Y,create_time,(
35
+ Select Name,X,Y,create_time,(
36
- Select count(X=1 and Y=2)
36
+ Select count(X=0 and Y=0)
37
37
  From Table as B
38
+ Where A.Name=B.Name
38
- Where A.create_time > B.create_time
39
+ And A.create_time > B.create_time
40
+ ),
41
+ (
42
+ Select count(X=0 and Y=1)
43
+ From Table as B
44
+ Where A.Name=B.Name
45
+ And A.create_time > B.create_time
39
46
  )
47
+ (
40
- ...(同様のサブクエリを、X,Yのパターン分)
48
+ Select count(X=1 and Y=0)
49
+ From Table as B
50
+ Where A.Name=B.Name
51
+ And A.create_time > B.create_time
52
+ ),
53
+ (
54
+ Select count(X=1 and Y=1)
55
+ From Table as B
56
+ Where A.Name=B.Name
57
+ And A.create_time > B.create_time
58
+ )
41
59
  From Table as A
42
60
 
43
61
  (※実行・期待された結果は取得できるが、時間がかかってしまう)

3

追記

2019/11/20 15:08

投稿

wwwww
wwwww

スコア41

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

2

追記の修正

2019/11/20 05:00

投稿

wwwww
wwwww

スコア41

title CHANGED
File without changes
body CHANGED
@@ -25,7 +25,9 @@
25
25
 
26
26
  ■追記(2019/11/19 23:20)
27
27
  不躾ながら追加質問…
28
+ 上記MySQLは(複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っています。
29
+
28
- (バージョン8に)
30
+ (バージョン8に変更することを考えないといけないのかな、と考え、)
29
- (複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っているのですが、ざっと調べた感じ、バージョン8の物が無い?気がしています。
31
+ 同系統のサービスをざっと調べた感じ、バージョン8の物が無い?気がしています。
30
32
  ご存知ある方いらっしゃれば、別途教えていただきたいです。
31
33
  (MYSQLバージョン8の機能って、世間的にそんなに需要ないものなんでしょうか…？)

1

追記

2019/11/19 14:25

投稿

wwwww
wwwww

スコア41

title CHANGED
File without changes
body CHANGED
@@ -21,4 +21,11 @@
21
21
  サブクエリ部分をCaseで分岐させてテーブル1周で実施できないか試したりしたのですが、うまく行きませんでした。
22
22
 
23
23
  不足・不明点等あれば、ご指摘いただけますと幸いです。
24
- 以上、何卒宜しくお願いいたします。
24
+ 以上、何卒宜しくお願いいたします。
25
+
26
+ ■追記(2019/11/19 23:20)
27
+ 不躾ながら追加質問…
28
+ (バージョン8に)
29
+ (複数人でサーバを使う比較的安価の契約にて)レンタルサーバで使っているのですが、ざっと調べた感じ、バージョン8の物が無い?気がしています。
30
+ ご存知ある方いらっしゃれば、別途教えていただきたいです。
31
+ (MYSQLバージョン8の機能って、世間的にそんなに需要ないものなんでしょうか…？)