id, name(text), inserted(UNIX_TIME)で出来ているテーブルが2つあります。
web_list, tell_listと名前を付けています。
どちらも同じ構造ですが意味合いが異なっています。
master, web_list, tel_listにはインデックスはありません。
idは自動採番、insertedは秒単位で全くバラバラです。nameは全体の2割ほどが複数回重複して含まれています。
ここで、web_listを上から順番に見ていって、あるレコード(例えばid=n)について【条件1】insertedが後ろ3日(259200秒)以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、masterに付き合わせてflagをアプデートしようとしています。
SQL
1update master set flag=1 where id in ( 2 select id from web_list as wlst 3 where 4 0 < (select count(id) from tell_list as tlst 5 where 6 wlst.name = tlst.name 7 and 8 wlst.inserted < tlst.inserted 9 and 10 tlst.inserted < wlst.inserted + 259200) 11 ) 12);
と、書いて結果は得られるのですが非常に遅く、ネットなどで検索するとMySQLのin+サブクエリーは遅いのでjoinに書き換えた方が良いといった意見なども見受けられました。
AWSのRDS(large)などでテストしても各テーブルが数千件を超えるとまともに結果が返却されない(数十分かかってタイムアウト)といった状態です。
nameが同一で、3日以内のレコードが1件以上存在する(0<)ということを条件にしてweb_listからidを列挙してきているのですが……、もっと高速にSQLを書き換えられないものでしょうか。
質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文+INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。
master, web_list, tel_listのidを主キーにして速度が上がりました。しかしながら、まだかなり遅い印象で、まだ速くなる余地はないでしょうか。(tell_list, web_list共に2万件で1分ほど掛かります)
web_list
web_list
1id|name|inserted 211|bob |2016/09/01 12:00 312|mary|2016/09/01 13:00 415|mary|2016/09/05 10:00 516|bob |2016/09/06 15:00
tel_list
tel_list
1id|name|inserted 213|bob |2016/09/02 17:00 314|bob |2016/09/03 15:00 417|bob |2016/09/07 15:00 518|bob |2016/09/07 18:00 619|mary|2016/09/10 10:00
上記のようなイメージです。
この場合、INのサブクエリーで得られるID一覧は
11, 16
となります。
回答4件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/09/19 04:01
2016/09/19 04:48
2016/09/19 05:07 編集
2016/09/19 11:59
2016/09/20 13:49
2016/09/22 04:52