質問編集履歴
5
テーブルweb_list, tel_listのサンプルを追記しました。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -28,4 +28,27 @@
|
|
|
28
28
|
|
|
29
29
|
質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文＋INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。
|
|
30
30
|
|
|
31
|
-
master, web_list, tel_listのidを主キーにして速度が上がりました。しかしながら、まだかなり遅い印象で、まだ速くなる余地はないでしょうか。（tell_list, web_list共に2万件で1分ほど掛かります）
|
|
31
|
+
master, web_list, tel_listのidを主キーにして速度が上がりました。しかしながら、まだかなり遅い印象で、まだ速くなる余地はないでしょうか。（tell_list, web_list共に2万件で1分ほど掛かります）
|
|
32
|
+
|
|
33
|
+
**web_list**
|
|
34
|
+
```web_list
|
|
35
|
+
id|name|inserted
|
|
36
|
+
11|bob |2016/09/01 12:00
|
|
37
|
+
12|mary|2016/09/01 13:00
|
|
38
|
+
15|mary|2016/09/05 10:00
|
|
39
|
+
16|bob |2016/09/06 15:00
|
|
40
|
+
```
|
|
41
|
+
|
|
42
|
+
**tel_list**
|
|
43
|
+
```tel_list
|
|
44
|
+
id|name|inserted
|
|
45
|
+
13|bob |2016/09/02 17:00
|
|
46
|
+
14|bob |2016/09/03 15:00
|
|
47
|
+
17|bob |2016/09/07 15:00
|
|
48
|
+
18|bob |2016/09/07 18:00
|
|
49
|
+
19|mary|2016/09/10 10:00
|
|
50
|
+
```
|
|
51
|
+
上記のようなイメージです。
|
|
52
|
+
この場合、INのサブクエリーで得られるID一覧は
|
|
53
|
+
11, 16
|
|
54
|
+
となります。
|
4
インデックスにより少し高速化されたことの追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -26,4 +26,6 @@
|
|
|
26
26
|
|
|
27
27
|
nameが同一で、3日以内のレコードが1件以上存在する（0<）ということを条件にしてweb_listからidを列挙してきているのですが……、もっと高速にSQLを書き換えられないものでしょうか。
|
|
28
28
|
|
|
29
|
-
質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文＋INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。
|
|
29
|
+
質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文＋INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。
|
|
30
|
+
|
|
31
|
+
master, web_list, tel_listのidを主キーにして速度が上がりました。しかしながら、まだかなり遅い印象で、まだ速くなる余地はないでしょうか。（tell_list, web_list共に2万件で1分ほど掛かります）
|
3
masterにもインデックスはなかったため修正。update対象が0件でも異様に時間が掛かります
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -2,7 +2,7 @@
|
|
|
2
2
|
web_list, tell_listと名前を付けています。
|
|
3
3
|
どちらも同じ構造ですが意味合いが異なっています。
|
|
4
4
|
|
|
5
|
-
master
|
|
5
|
+
master, web_list, tel_listにはインデックスはありません。
|
|
6
6
|
idは自動採番、insertedは秒単位で全くバラバラです。nameは全体の2割ほどが複数回重複して含まれています。
|
|
7
7
|
|
|
8
8
|
ここで、web_listを上から順番に見ていって、あるレコード（例えばid=n）について【条件1】insertedが後ろ3日（259200秒）以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、masterに付き合わせてflagをアプデートしようとしています。
|
2
質問のためselect \* masterとしましたが、update master〜でないと遅延の問題が起きないことが分かり修正しました。
title
CHANGED
|
@@ -1,1 +1,1 @@
|
|
|
1
|
-
「各レコードから見て後ろ3日以内で条件を満たすレコードが存在するもの」の一覧
|
|
1
|
+
「各レコードから見て後ろ3日以内で条件を満たすレコードが存在するもの」の結果一覧から高速にマスターをアップデートするSQL
|
body
CHANGED
|
@@ -5,10 +5,10 @@
|
|
|
5
5
|
masterにはインデックスがありますが、web_list, tel_listにはインデックスはありません。
|
|
6
6
|
idは自動採番、insertedは秒単位で全くバラバラです。nameは全体の2割ほどが複数回重複して含まれています。
|
|
7
7
|
|
|
8
|
-
ここで、web_listを上から順番に見ていって、あるレコード（例えばid=n）について【条件1】insertedが後ろ3日（259200秒）以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、master
|
|
8
|
+
ここで、web_listを上から順番に見ていって、あるレコード（例えばid=n）について【条件1】insertedが後ろ3日（259200秒）以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、masterに付き合わせてflagをアプデートしようとしています。
|
|
9
9
|
|
|
10
10
|
```SQL
|
|
11
|
-
|
|
11
|
+
update master set flag=1 where id in (
|
|
12
12
|
select id from web_list as wlst
|
|
13
13
|
where
|
|
14
14
|
0 < (select count(id) from tell_list as tlst
|
|
@@ -24,4 +24,6 @@
|
|
|
24
24
|
と、書いて結果は得られるのですが非常に遅く、ネットなどで検索するとMySQLのin＋サブクエリーは遅いのでjoinに書き換えた方が良いといった意見なども見受けられました。
|
|
25
25
|
AWSのRDS（large）などでテストしても各テーブルが数千件を超えるとまともに結果が返却されない（数十分かかってタイムアウト）といった状態です。
|
|
26
26
|
|
|
27
|
-
nameが同一で、3日以内のレコードが1件以上存在する（0<）ということを条件にしてweb_listからidを列挙してきているのですが……、もっと高速にSQLを書き換えられないものでしょうか。
|
|
27
|
+
nameが同一で、3日以内のレコードが1件以上存在する（0<）ということを条件にしてweb_listからidを列挙してきているのですが……、もっと高速にSQLを書き換えられないものでしょうか。
|
|
28
|
+
|
|
29
|
+
質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文＋INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。
|
1
分かりやすくしようと前後3日かつbetweenを使う形にしましたが、自分を含んでしまうため実際に試したSQLと条件に則した条件とSQLに戻しました
title
CHANGED
|
@@ -1,1 +1,1 @@
|
|
|
1
|
-
「各レコードから見て
|
|
1
|
+
「各レコードから見て後ろ3日以内で条件を満たすレコードが存在するもの」の一覧を高速に列挙するSQL
|
body
CHANGED
|
@@ -5,7 +5,7 @@
|
|
|
5
5
|
masterにはインデックスがありますが、web_list, tel_listにはインデックスはありません。
|
|
6
6
|
idは自動採番、insertedは秒単位で全くバラバラです。nameは全体の2割ほどが複数回重複して含まれています。
|
|
7
7
|
|
|
8
|
-
ここで、web_listを上から順番に見ていって、あるレコード（例えばid=n）について【条件1】insertedが
|
|
8
|
+
ここで、web_listを上から順番に見ていって、あるレコード（例えばid=n）について【条件1】insertedが後ろ3日（259200秒）以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、masterから情報を**in**を使って列挙しようとしています。
|
|
9
9
|
|
|
10
10
|
```SQL
|
|
11
11
|
select * from master where id in (
|
|
@@ -14,9 +14,10 @@
|
|
|
14
14
|
0 < (select count(id) from tell_list as tlst
|
|
15
15
|
where
|
|
16
16
|
wlst.name = tlst.name
|
|
17
|
-
|
|
17
|
+
and
|
|
18
|
+
wlst.inserted < tlst.inserted
|
|
19
|
+
and
|
|
18
|
-
|
|
20
|
+
tlst.inserted < wlst.inserted + 259200)
|
|
19
|
-
and wlst.inserted + 259200)
|
|
20
21
|
)
|
|
21
22
|
);
|
|
22
23
|
```
|